Multiple Where clause in query expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need 2 columns in my query, 1) to sum records where Location 1 RorM = "R"
and 2) to sum records where Location 1 RorM = "M". I'm not having any luck
adding both columns using the query below. Any help is much appreciated.
Thank you.

SELECT [Part Number Info].Model, Sum([UT Data].[Location 1]) AS L1R, Sum([UT
Data].[Location 1]) AS L1M
FROM [UT Data] RIGHT JOIN [Part Number Info] ON [UT Data].[Part Number] =
[Part Number Info].[Part Number]
WHERE ((([UT Data].[Location 1 RorM])="R") AND (([UT Data].[Location 1
RorM])="M"))
GROUP BY [Part Number Info].Model;
 
I need 2 columns in my query, 1) to sum records where Location 1 RorM = "R"
and 2) to sum records where Location 1 RorM = "M". I'm not having any luck
adding both columns using the query below. Any help is much appreciated.
Thank you.

SELECT [Part Number Info].Model, Sum([UT Data].[Location 1]) AS L1R, Sum([UT
Data].[Location 1]) AS L1M
FROM [UT Data] RIGHT JOIN [Part Number Info] ON [UT Data].[Part Number] =
[Part Number Info].[Part Number]
WHERE ((([UT Data].[Location 1 RorM])="R") AND (([UT Data].[Location 1
RorM])="M"))
GROUP BY [Part Number Info].Model;
Click to expand...

Well, you're not going to find any records for which [Location 1 RorM}
is simultneously equal to R and also equal to M, of course; so you
won't get any records at all.

Try this instead: sum the results of an IIF statement which returns 1
if the value is R, 0 if it's M. E.g.

SELECT [Part Number Info].Model,
Sum(IIF([UT Data].[Location 1] = "R", 1, 0)) AS L1R,
Sum(IIF([UT Data].[Location 1] = "M", 1, 0)) AS L1M
FROM [UT Data] RIGHT JOIN [Part Number Info]
ON [UT Data].[Part Number] = [Part Number Info].[Part Number]
WHERE [UT Data].[Location 1 RorM] IN("R", "M")
GROUP BY [Part Number Info].Model;


John W. Vinson[MVP]
 
Back
Top