[AccessD] Switch function in a query

Are you going to put a where clause to filter the results for those
three values?

I was under the assumption that the numeric values were your PIDs
earlier, thats why I thought you had the values switched.


On Mon, Nov 24, 2008 at 1:32 PM, Kaup, Chester
<Chester_Kaup at kindermorgan.com> wrote:
> The way the DB is set up there has to be a match for each of the three switch conditions. I really don't want an other condition. Also the PID field in the table is sorted but the values are such that thing like WGRP7 comes after WGRP69.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
> Sent: Monday, November 24, 2008 3:23 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Switch function in a query
>
> Correction: should have said 'which does not have the value "WGRP115",
> "WGRP116" or "WGRP117".'
>
> So if these cryptic data do indeed simply get 'bigger' (next value "WGRP118"
> etc) then the switch statement would be something along the lines of
>
> Area:Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",
> 2,[ConfigMaster]![PID]="WGRP117",3,[PID]>Area:Switch([ConfigMaster]![PID]="W
> GRP115",1,[ConfigMaster]![PID]="WGRP116",2,[ConfigMaster]![PID]="WGRP117",3,
> [PID]>"WGRP117"),"Out of Range")
>
> Lambert
>
> -----Original Message-----
> From: Heenan, Lambert
> Sent: Monday, November 24, 2008 4:15 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Switch function in a query
>
> You have data in the PID field that does not have the value 1,2 or 3. The
> Switch statement does not say what to do in that circumstance, so nothing is
> displayed in the Area field for those records.
>
> There is no "Else" part to a switch statement, so you need some other
> catch-all condition for the other data items, e.g.:
>
> Area:Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",
> 2,[ConfigMaster]![PID]="WGRP117",3,[PID]>3"Out of Range")
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
> Sent: Monday, November 24, 2008 3:26 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Switch function in a query
>
> 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.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>