Query combining 2 sets of data

[baronvont]

I have a table that has fields 'RefNumber', 'LogDate' and 'FinishDate' (amongest others of course). I can easily query the table to count the number of orders per month and also to count the number of completed per month, but I cannot combine these into a single query. Is it possible?

The individual queries are as follows..

SELECT DatePart("m",[logdate]) AS Month, Count([GCSR Register].RefNumber) AS Orders FROM [GCSR Register]
GROUP BY DatePart("m",[logdate])
HAVING (((DatePart("m",[logdate]))>0));

SELECT DatePart("m",[finishdate]) AS Month, Count(qryGCSR_Report_Query.RefNumber) AS Completed
FROM qryGCSR_Report_Query
GROUP BY DatePart("m",[finishdate])
HAVING (((DatePart("m",[finishdate]))>0));

Thanks
Georg

 

[LonnieJohnson]

If you are wanting to create a combined dataset of the output of the two queries, you can make a union query. You already have the basics for it. Create a new query and paste the SQL for your two existing queries into the SQL window and use the UNION reserve word like so...

SELECT DatePart("m",[logdate]) AS Month, Count([GCSR Register].RefNumber) AS Orders FROM [GCSR Register]
GROUP BY DatePart("m",[logdate])
HAVING (((DatePart("m",[logdate]))>0))

UNION SELECT DatePart("m",[finishdate]) AS Month, Count(qryGCSR_Report_Query.RefNumber) AS Completed
FROM qryGCSR_Report_Query;

Hope this is what you're lookin' for... B-)
ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[Guest_imported]

Thanks, it partly solves the problem, but it does not separate the 2 sets of data into columns.. I am looking for this..

Count of Count of
Month logdate FinishDate
------------------------------
1 xx yyy
2 zz etc

Thanks
Georg

 

[tlbroadbent]

You should save both of the queries and then create another query joining the two. Assume the queries are saved as qryMonthlyOrderCount and qryMonthlyOrderComp.

The Join query would look something like this...

Select a.month, a.Orders, b.completed
From qryMonthlyOrderCount As a Inner Join qryMonthlyOrderComp As b On a.month=b.month

If it is possible for there to be Orders but no Completions or vice versa, you'll need to construct an outer join instead of an inner join.

You may also want to add some logic for selecting and matching the year if the table contains multiple years of data. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[baronvont]

That works perfectly!! I was thinking along those lines, but just couldn't make the final connection..

Maby thanks

Georg