Query 2 Tables

**kingdt** · 12-13-2011, 11:39 AM

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

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** · 12-13-2011, 12:12 PM

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** · 12-13-2011, 12:14 PM

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** · 12-13-2011, 05:06 PM

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

**kingdt** · 12-13-2011, 06:08 PM

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** · 12-13-2011, 07:02 PM

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** · 12-14-2011, 04:54 AM

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** · 12-14-2011, 02:08 PM

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.