Repost, Help with combining queries

[Guest]

I currently have a database that contains some information such as:
File number, date ordered, date closed, gross income. I have two queries
that I would like to combine, the first is as follows:

SELECT Year([OrderDate]) AS [Year], Month([OrderDate]) AS [Month],
Count(File_Tracking.FileNumber) AS CountofFileNumber
FROM File_Tracking
GROUP BY Year([OrderDate]), Month([OrderDate]);

This query will give me the total number of files ordered for each month.
The next query will give me the total number of files closed in each month:

SELECT Year([ClosingDate]) AS [Year], Month([ClosingDate]) AS [Month],
Count(File_Tracking.FileNumber) AS Files, Sum([Gross]) AS SumofGross
FROM File_Tracking
WHERE ([ClosingDate]) Is Not Null
GROUP BY Year([ClosingDate]), Month([ClosingDate]);


The reason I wanted to combine these queries is that I wanted to get a
percentage of how many files are ordered in each month vs. how many files
actually closed in each month. I am stumped on how to get this done in on
query so any help would be greatly appreciated.

 

[Guest]

First, you should not name fields as Year or Month in your queries. Name
them OrderYear, OrderMonth, CloseYear, CloseMonth

You need to join these two queries.

SELECT qry1.OrderYear, _
qry1.OrderMonth, _
qry1.CountOfFileNumber as FilesOpened, _
qry2.Files as FilesClosed, _
qry2.SumOfGross
FROM qry1 LEFT JOIN qry2
ON qry1.OrderYear = qry2.CloseYear
AND qry1.OrderMonth = qry2.CloseMonth
Order by qry1.OrderYear, qry1.OrderMonth

The LEFT JOIN above assumes that every month will have at least one file
opened. If that is not the case, You might need to write another query that
will give you every Year/Month combination durint a given timespan. Then,
join that to both qry1 and qry2.

HTH
Dale

 

[Guest]

Try these queries ---
Gregw_Close ---
SELECT Format([ClosingDate],"yyyymm") AS [Close]
FROM File_Tracking
GROUP BY Format([ClosingDate],"yyyymm");

Gregw_Order ---
SELECT Format([OrderDate],"yyyymm") AS [Order]
FROM File_Tracking
GROUP BY Format([OrderDate],"yyyymm");

Gregw_Dates ---
SELECT Gregw_Order.Order AS Gregw_Dates
FROM Gregw_Order
UNION SELECT Gregw_Close.Close AS Gregw_Dates
FROM Gregw_Close;

SELECT DateSerial(Left([Gregw_Dates],4),Right([Gregw_Dates],2),1) AS
[Transaction], Sum(IIf(Format([OrderDate],"yyyymm")=[Gregw_Dates],1,0)) AS
[Number of Orders],
Sum(IIf(Format([ClosingDate],"yyyymm")=[Gregw_Dates],1,0)) AS [Number of
Closing]
FROM Gregw_Dates, File_Tracking
GROUP BY DateSerial(Left([Gregw_Dates],4),Right([Gregw_Dates],2),1);