One More Access SQL Question

[JeffK627]

The following query returns 26,318 rows, even though the table tProd has 27,018 rows:

SELECT T1.Product, T1.Business_Unit, T1.ProductDesc, T1.NDC11, T1.State, T1.State_Category, SUM(T1.iMANY_Units) AS iMANYUnits, SUM(T1.ffs_Units) AS FFSUnits, SUM(T1.mco_Units) AS MCOUnits
FROM (tURMSReport T1
RIGHT OUTER JOIN
tProd T2
ON T1.NDC11=T2.NDC11 AND T1.State=T2.State)
WHERE T1.Quarter='Q1_2008'
GROUP BY T1.Product, T1.Business_Unit, T1.ProductDesc, T1.NDC11, T1.State, T1.State_Category
ORDER BY T1.Product, T1.State, T1.NDC11;

I figure it's because of the GROUP BY clause. Is there a way to force the query to show all 27,018 rows from tProd even after the GROUP BY is executed?

 

[Joe4]

I am kind of confused. Why are you GROUPING at all if you don't want the results grouped?

How many records do you get if you run this query?

SELECT NDC11, State
FROM tProd
GROUP BY NDC11, State;

If this issue is you need to group the records from tURMSReport, then do that first, grouping just that table in its own query or a subquery before joining to tProd so that tProd is not part of the Aggregate Query.

 

[JeffK627]

Joe - yes, I needed to group the tURMSReport data separately. Thanks for the help!