Combining Query Results

[illini]

I have two queries: one groups records by Date Created; the other groups records by Date Completed. Each query has dates in common, as well as dates unique to each.

I would like to combine both queries to produce a report which will illustrate the date, number of records created (if any), and number of records completed (if any).

I'm not sure how to go about this. Any suggestions would be appreciated.

-illini

 

[Creepers]

Try a Union of the two queries

 

[illini]

Thanks, Creepers.

I'm not too familiar with union queries (and the sql syntax), but I was able to construct one which produced the following results:

Date Total Rec_Type
3/3/02 10 Created
3/3/02 5 Completed

Instead of these results, I would like to group by date and have separate columns for the total created and completed records. I imagine it would as follows:

Date Created Completed
3/3/02 10 5


What would be the best approach? By-the-way, here's the SQL:

select [Date_Create] as Rec_Date,[countofDate_create] as Total,[Qry_Daily_Rec_Created]![Expr1] as Type from [Qry_Daily_Rec_Created]'Expr1 simply places "Created" in the field

UNION select [Date_Effective],[countofDate_Effective],[Qry_Daily_Rec_Effective]![Expr1] from [Qry_Daily_Rec_Effective];
]'Expr1 simply places "Completed" in the field -illini

 

[illini]

I still haven't resolved the challenge listed above. Now I have another. Is there a way to group records by the week? In other words, I would like to group some number of records which were created between March 09-15 so that the results would look like:

Week # Created Completed
11 10 5

Thanks in advance.
-illini