#  Other Applications & Softwares  > Access Tables & Databases >  >  Query 2 Tables

## kingdt

I have tried a few things and have now come to the point where i don't know what i'm doing  :Mad: 

I have 2 matching tables, both have the same columns/structure, but different data in those columns.
What i want to do is create a query that returns the count of the same column in each table and list the results organised by the date.

How can this be done?

For example:

Date--------|  Column Table 1  | Column Table 2

01/01/2011--------10-----------------20
02/01/2011--------15-----------------10


Thanks

----------


## MarvinP

Hi kingdt,

I think I'd put a column in each table that shows which table it is from and the make a single table from the two and do a Pivot Table on them.  See if the attached explains it with an example.  The problem is you may have a date in table 1 that isn't in table 2 or vice versa.  This makes a single table using Pivots more better. (bad english)

BTW - you should update your profile so we can see which version of excel you are using.  This would allow me to attach the correct format of file (.xls or .xlsx).

----------


## kingdt

Thanks for the reply, I see what you mean. Another way i just realised it to create 2 queries, one for each table, and then another query using those 2 queries pulling in the data i want. Bit messy, must be a cleaner way.

----------


## alansidman

If you are doing this in Access, then a simple aggregate query where you group on the date and count on the data.

----------


## kingdt

Hi alansidman. I tried that. I linked the dates of the two tables, used the date from one of the tables and grouped on that, and then did a count on the two columns from each of the tables, but that multiplies the results.

----------


## alansidman

mmmm.  I'm thinking you were right when you said you may have to create two queries.  Make them aggregate queries.  Then try and join the two queries into a third query. Do both tables have the same dates?  Or will some dates appear in one table and not in the other?

----------


## kingdt

Both have the same dates. Built another couple of simple tables and tried to do the same, but same result. If i have to do three queries then so be it, but there must be a way, maybe SQL???

----------


## alansidman

Upload a copy of your simple data base and let us give it a shot.  There are some really talented people here on this forum.  Let them have a shot at a real situation.

----------

