[AccessD] Switch function in a query

Kaup, Chester Chester_Kaup at kindermorgan.com
Mon Nov 24 14:26:07 CST 2008


This is my first attempt to use this expression in a query. When I use the expression below I get a value of 1,2 or 3 for each record and also a duplicate record with the area field blank. What am I doing wrong. I have also listed below the entire SQL string. The field ChildPID may appear in the ConfigMaster table multiple times.

Area:Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",2,[ConfigMaster]![PID]="WGRP117",3)

SELECT [qry Status Date Closest to 1 Month Prior].PID, 
[qry Status Date Closest to 1 Month Prior].Well_Number, 
[qry Status Date Closest to 1 Month Prior].RecordDate, dbo_DSS_StatusChanges.Status, Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",2,_
[ConfigMaster]![PID]="WGRP117",3) AS Area
FROM ([qry Status Date Closest to 1 Month Prior] 
INNER JOIN dbo_DSS_StatusChanges 
ON ([qry Status Date Closest to 1 Month Prior].MaxOfStatusDate = dbo_DSS_StatusChanges.StatusDate) 
AND ([qry Status Date Closest to 1 Month Prior].PID = dbo_DSS_StatusChanges.PID)) 
INNER JOIN ConfigMaster 
ON [qry Status Date Closest to 1 Month Prior].Well_Number = ConfigMaster.ChildPID
GROUP BY [qry Status Date Closest to 1 Month Prior].PID, 
[qry Status Date Closest to 1 Month Prior].Well_Number, 
[qry Status Date Closest to 1 Month Prior].RecordDate, dbo_DSS_StatusChanges.Status, Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",2,
[ConfigMaster]![PID]="WGRP117",3)
ORDER BY [qry Status Date Closest to 1 Month Prior].Well_Number;

Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799

 
No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.





More information about the AccessD mailing list