Query wih Multiple Counts 1

[stlrain95]

I am using the following query to get Count results from Multiple Table...but I need each to make their own column? I am not sure the UNION is the right answer?

SELECT Format([RECDATE],"Short Date&quot AS DayRecd, Count(MASTER2.ID) AS [Rec Qty]

FROM MASTER2

WHERE (((MASTER2.OrderNumber) Is Not Null) AND ((MASTER2.RECDATE) Between FromDate() And ToDate()))

GROUP BY Format([RECDATE],"Short Date&quot



UNION SELECT Format([Start],"Short Date&quot AS DayQCd, Count([MASTER2].[InspectorStart]) AS [QC Qty]

FROM MASTER2

WHERE ((([MASTER2].[OrderNumber]) Is Not Null) And (([MASTER2].[STOP]) Between FromDate() And ToDate()))

GROUP BY Format([Start],"Short Date&quot



UNION SELECT Format([PickDate],"Short Date&quot AS DayPicked, Count([Pick].[ID]) AS [Pick Qty]

FROM PICK

WHERE ((([PICK].[PICKDATE]) Is Not Null) And (([PICK].[PICKDATE]) Between FromDate() And ToDate()))

GROUP BY Format([PickDate],"Short Date&quot



UNION SELECT Format([VerDate],"Short Date&quot AS DayVerified, Count([VERIFICATION].[ID]) AS [Ver Qty]

FROM VERIFICATION

WHERE ((([VERIFICATION].[VERDATE]) Is Not Null) And (([VERIFICATION].[VERDATE]) Between FromDate() And ToDate()))

GROUP BY Format([VerDate],"Short Date"

 

[Philly44]

The UNION as you have it won't really work for what you want. From what I can tell you are getting a count of several different pieces of data and then you want them to display together. From my understanding of a UNION queries they work best when you want that type of result from one table. I have some similar queries that feed reports in a few systems I take care of and have found that creating seperate queries for each count and then bringing them together in one master query and using that master query works best. I know its not the most efficient way of doing it and am very sure there are many better ways to do, but I guarantee that it works.

 

[Golom]

If you want each query in the UNION to populate it's own field then you need to define all the fields in each select. Here's an example using your first two queries.

SELECT Format([RECDATE],"Short Date") AS DayRecd, 
       Count(MASTER2.ID) AS [Rec Qty],
       0 As [Qc_Qty],
       0 As [Pick Qty],
       0 As [Ver Qty]

FROM MASTER2

WHERE MASTER2.OrderNumber Is Not Null 
      AND MASTER2.RECDATE Between FromDate() And ToDate()

GROUP BY Format([RECDATE],"Short Date")

UNION 

SELECT Format([Start],"Short Date") AS DayQCd, 
       0 As [Rec Qty],
       Count([MASTER2].[InspectorStart]) AS [QC Qty],
       0 As [Pick Qty],
       0 As [Ver Qty]

FROM MASTER2

WHERE [MASTER2].[OrderNumber] Is Not Null 
      And [MASTER2].[STOP] Between FromDate() And ToDate()

GROUP BY Format([Start],"Short Date")

UNION ... etc.

 

[Quehay]

Because the subqueries won't have a meaningful join field, and you'll get multiple rows in output the UNION is probably best (no Cartesian cross products).

Just put in a column with a literal value to identify each subquery, add a meaningful literal prefix to the date and count columns, and it will be like spreadsheet output, e.g.,

select 'Verification','Day Verified: ' & Format([VerDate],"Short Date&quot,'Ver QTY: ' & Count([VERIFICATION].[ID]) AS [Ver Qty]

FROM VERIFICATION.

Order the results by subquery then date. The Access help for 'UNION': "Use aliases only in the first SELECT statement because they are ignored in any others. In the ORDER BY clause, refer to fields by what they are called in the first SELECT statement."

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[stlrain95]

GOLOM...
the 2nd choice worked...but it repeats the Date on the Left side. Can I get this query to just give me 1 date, and not the 4?

 

[Golom]

Not THIS query ... but you can save your UNION query (above) as (for example) qryMyUnion and then

Select DayRecd As [The Date], 
       Sum([Rec Qty])  As [Received],
       Sum([Qc_Qty])   As [Qc],
       Sum([Pick Qty]) As [Picked],
       Sum([Ver Qty])  As [Verified]

From  qryMyUnion

Group By DayRecd

That should return one record with one date ([The Date]) and four other "Qty" values. They should be the same as the ones generated in the individual records.

 

[stlrain95]

FANTASTIC!!

 

[Quehay]

So stlrain95, give the man a star already!! That was a lot of thought and very clean formatting to produce a tailored solution for you.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[stlrain95]

Hey now...he got his due!! I gave him a star!!