Using Swtich

[alvarjo9 via AccessMonster.com]

Hi,
Please help, I am trying to use the switch function. Below is query used.
But my problem is when I use the query to display in the report it return
blank spaces if there is no value in B, C and D column. column if no A
calibre and so on.

Please assist if anyone knows how to just make them in one record if no value
satisfy for each A, B C and D calibre. I am not sure if there is any other
command to use
thanks


SELECT Scores.Name, switch(Scores.Calibre="A",Scores.Date) AS DATEa, switch
(Scores.Calibre="B",Scores.Date) AS DATEb, switch(Scores.Calibre="C",Scores.
Date) AS DATEc, switch(Scores.Calibre="D",Scores.Date) AS DATEd
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName
AND (((Scores.Date) In (SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name =
Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

 

[Guest]

It appears what you are trying to do is set up four columns A, B, C, D and
put a score in each of the columns based on the value in the Calibre field.
If this is correct, then this is not an appropriate use for the Switch
function. The Switch function ususally has multiple conditions. Each
condition has a return value and the Switch function returns the return value
for the first True condition.

x = Switch(a = 3, "Frodo", a = 2, "Bilbo", b = "South", "Down")

If a contains 2, then the value "Bilbo" will be returned. If non of the
conditions equates to True, Null is returned.

So, if you had one column and wanted to put in one of four values, the
Switch would be a good way to do it. Since you want to put the value of one
field in one of four columns in the query, four IIf statements would be
better:

SELECT Scores.Name, IIf(Scores.Calibre="A",Scores.Date, NUll) AS DATEa,
IIf(Scores.Calibre="B",Scores.Date, Null) AS DATEb,
Iif(Scores.Calibre="C",Scores.Date, Null) AS DATEc,
IIf(Scores.Calibre="D",Scores.Date, Null) AS DATEd

 

[alvarjo9 via AccessMonster.com]

Hi,
thanks for your help. But I am still getting the same result when I ran the
query below.
SELECT Scores.Name, IIf(Scores.Calibre="A",Scores.Date,Null) AS DATEa, IIf
(Scores.Calibre="B",Scores.Date,Null) AS DATEb, IIf(Scores.Calibre="C",Scores.
Date,Null) AS DATEc, IIf(Scores.Calibre="D",Scores.Date,Null) AS DATEd
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName AND (((Scores.Date) In
(SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name = Scores.Name And X.
Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

If calibre A is satisfied then column B, C D will have blank values and so on.
If I ran the query in the report it will produce blank spaces.

I am using combo box to find the name and display the report. Or can I use 4
queries then call them in the report? Please kindly help.

thanks

It appears what you are trying to do is set up four columns A, B, C, D and
put a score in each of the columns based on the value in the Calibre field.
If this is correct, then this is not an appropriate use for the Switch
function. The Switch function ususally has multiple conditions. Each
condition has a return value and the Switch function returns the return value
for the first True condition.

x = Switch(a = 3, "Frodo", a = 2, "Bilbo", b = "South", "Down")

If a contains 2, then the value "Bilbo" will be returned. If non of the
conditions equates to True, Null is returned.

So, if you had one column and wanted to put in one of four values, the
Switch would be a good way to do it. Since you want to put the value of one
field in one of four columns in the query, four IIf statements would be
better:

SELECT Scores.Name, IIf(Scores.Calibre="A",Scores.Date, NUll) AS DATEa,
IIf(Scores.Calibre="B",Scores.Date, Null) AS DATEb,
Iif(Scores.Calibre="C",Scores.Date, Null) AS DATEc,
IIf(Scores.Calibre="D",Scores.Date, Null) AS DATEd

Hi,
Please help, I am trying to use the switch function. Below is query used.

[quoted text clipped - 15 lines]

Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

 

[John Spencer]

Evidently we don't understand what you are trying to accomplish.

Can I suggest that you post a record or two and then what you want the
results of the query to show?



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
thanks for your help. But I am still getting the same result when I ran the
query below.
SELECT Scores.Name, IIf(Scores.Calibre="A",Scores.Date,Null) AS DATEa, IIf
(Scores.Calibre="B",Scores.Date,Null) AS DATEb, IIf(Scores.Calibre="C",Scores.
Date,Null) AS DATEc, IIf(Scores.Calibre="D",Scores.Date,Null) AS DATEd
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName AND (((Scores.Date) In
(SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name = Scores.Name And X.
Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

If calibre A is satisfied then column B, C D will have blank values and so on.
If I ran the query in the report it will produce blank spaces.

I am using combo box to find the name and display the report. Or can I use 4
queries then call them in the report? Please kindly help.

thanks

It appears what you are trying to do is set up four columns A, B, C, D and
put a score in each of the columns based on the value in the Calibre field.
If this is correct, then this is not an appropriate use for the Switch
function. The Switch function ususally has multiple conditions. Each
condition has a return value and the Switch function returns the return value
for the first True condition.

x = Switch(a = 3, "Frodo", a = 2, "Bilbo", b = "South", "Down")

If a contains 2, then the value "Bilbo" will be returned. If non of the
conditions equates to True, Null is returned.

So, if you had one column and wanted to put in one of four values, the
Switch would be a good way to do it. Since you want to put the value of one
field in one of four columns in the query, four IIf statements would be
better:

SELECT Scores.Name, IIf(Scores.Calibre="A",Scores.Date, NUll) AS DATEa,
IIf(Scores.Calibre="B",Scores.Date, Null) AS DATEb,
Iif(Scores.Calibre="C",Scores.Date, Null) AS DATEc,
IIf(Scores.Calibre="D",Scores.Date, Null) AS DATEd

Hi,
Please help, I am trying to use the switch function. Below is query used.

[quoted text clipped - 15 lines]

Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;