[AccessD] Switch function in a query

That sounds good at first glance but will not work because the field ChildPID can be associated with many different PID's. For example ChildPID could be associated with PID's 

PID	     ChildPID
Cambrian	100-1
PAT 100-1	100-1
WGRP1		100-1
WGRP116	100-1
WGRP12	100-1
WGRP160	100-1
WGRP162	100-1
WGRP17	100-1
WGRP217	100-1
WGRP28	100-1
WGRP3		100-1
WGRP35	100-1
WGRP37	100-1
WGRP53	100-1

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Monday, November 24, 2008 3:18 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Switch function in a query

Not sure yet what your problem is, but how replacing the switch with

... Val(Right([ConfigMaster]![PID],1)) - 4 As Area...

-- 
Stuart

On 24 Nov 2008 at 14:26, Kaup, Chester wrote:

> 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]="WGRP
> 116",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; 
> 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com