Count date entries from seperate tables/queries

[krutoigoga]

Hi all,
I'm trying to create a report (chart) which would compare the # of entries from two separate tables or queries. My knowledge of access is limited, however I have tried a few things and was not successful.
In this database (attached) I have two tables, each tracking a number of entries per day. The chart would show # of entries per month in both tables.
I tried a union query - which did not work.
Also tried to create a query with both fields from separate tables and it did not work.


Any ideas?

[June7]

By chart do you mean a graph? Or tabular output with months as column headings? You want separate summary values for each table?

First, UNION query:
SELECT ID, drv_dates, "DRV" As Category FROM DRV_F
UNION SELECT ID, GC_dates, "GC" FROM gc;

Second, CROSSTAB query:
TRANSFORM Count([ID]) AS CountOfID
SELECT [Category]
FROM UnionTables
GROUP BY [Category]
PIVOT Format([drv_dates],"mmm");

All-in-one nested:
TRANSFORM Count([ID]) AS CountOfID
SELECT [Category]
FROM (SELECT ID, drv_dates, "DRV" As Category FROM DRV_F
UNION SELECT ID, GC_dates, "GC" FROM gc) As UnionQuery
GROUP BY [Category]
PIVOT Format([drv_dates],"mmm");

[krutoigoga]

By chart do you mean a graph? Or tabular output with months as column headings? You want separate summary values for each table?

First, UNION query:
SELECT ID, drv_dates, "DRV" As Category FROM DRV_F
UNION SELECT ID, GC_dates, "GC" FROM gc;

Second, CROSSTAB query:
TRANSFORM Count([ID]) AS CountOfID
SELECT [Category]
FROM UnionTables
GROUP BY [Category]
PIVOT Format([drv_dates],"mmm");

All-in-one nested:
TRANSFORM Count([ID]) AS CountOfID
SELECT [Category]
FROM (SELECT ID, drv_dates, "DRV" As Category FROM DRV_F
UNION SELECT ID, GC_dates, "GC" FROM gc) As UnionQuery
GROUP BY [Category]
PIVOT Format([drv_dates],"mmm");

Thanks June7 - This worked, however the dates are not in sequential order..it is sorting by ascending the month name and ignoring the year.. please see attached if you need to see the cha .... i mean graph . I did not quiet understand your instructions - but its showing me what Im looking for. Thank you.

[krutoigoga]

GROUP BY Format([drv_dates],"mmm/yyyy") fixed the date issue.
Thanks June 7!