434,847 Members | 2,296 Online
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

# Counting of records in a union query

 P: n/a I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code Count 1234 1 2468 1 1234 1 2468 1 1234 1 1111 1 1234 1 2468 1 1111 1 2222 1 I would like to have it in this format 1111 2 1234 4 2222 1 2468 3 The tables are all approx 60K each in size, so an append query would slow down the database too much, as they are all referenced to a particular time period. How would I write a Union query to calculate this? Also, is it possible to link two fields from two different tables in a union query, ie if the above numbers were linked in another table, then return the other specified field. IE from the tables above, using the below table to convert to the final table below. 1111 Blue 1234 Green 2222 Red 2468 Orange Blue 2 Green 4 Red 1 Orange 3 I would appreciate any assistance anyone can provide me! Cheers, Mattytee Nov 13 '05 #1
2 Replies

 P: n/a ma*********@hotmail.com wrote in news:11**********************@f14g2000cwb.googlegr oups.com: I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. EXAMPLE 1 Code Count 1234 1 2468 1 1234 1 2468 1 1234 1 1111 1 1234 1 2468 1 1111 1 2222 1 I would like to have it in this format EXAMPLE 2 1111 2 1234 4 2222 1 2468 3 The tables are all approx 60K each in size, so an append query would slow down the database too much, as they are all referenced to a particular time period. How would I write a Union query to calculate this? I don't see where you need a UNION query. I think you actually mean a join. A UNION query takes several individual queries, often based on different tables, and assembles the separate sets of records into a single recordset: SELECT tblFirstTable.Field1, tblFirstTable.Field2 FROM tblFirstTable UNION ALL SELECT tblSecondTable.Field1, tblSecondTable.Field2 FROM tblSecondTable; If tblFirstTable has 10 records and tblSecondTable has 10, you'd end up with 20 records. If there are duplicates between the two tables and you want to remove those, you'd change UNION ALL to just UNION. Also, is it possible to link two fields from two different tables in a union query, ie if the above numbers were linked in another table, then return the other specified field. IE from the tables above, using the below table to convert to the final table below. EXAMPLE 3 1111 Blue 1234 Green 2222 Red 2468 Orange EXAMPLE 4 Blue 2 Green 4 Red 1 Orange 3 I would appreciate any assistance anyone can provide me! I'm not sure I understand what you're asking for. Your 2nd example (I've put labels on them) looks like it totals the data in the 1st example. I think you're meaning to give color codes to the counts, in which case, you'd do something like this: SELECT Code, Count(*) As CodeCount FROM tblCodeList Save that as a query, say qryCodeCount. Then create another query and drop the table with the color lookup in it (example 3) and then drop qryCodeCount onto it. Then drag the field CodeCount to the 2nd column of the color lookup table. The result should be SQL that looks something like this: SELECT qryCodeCount.Code, tblColorLookup.Color FROM qryCodeCount INNER JOIN tblColorLookup ON qryCodeCount.Count = tblColorLookup.ColorID Now, that's probably erroneous SQL (I've never learned how to manually type joins -- I always use the QBE designer to write the joins for me), but it will be something like that. The result would be a result that looks like your example 3. -- David W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc Nov 13 '05 #2

 P: n/a ma*********@hotmail.com wrote: I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? From the rest of your message, I am under the impression that you have 20 tables with exactly the same structure? If so, the question is W H Y, in the name of all that's holy, are these not a single table? If I'm wrong, disregard this transmission! 8) -- Tim http://www.ucs.mun.ca/~tmarshal/ ^o< /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me Nov 13 '05 #3

### This discussion thread is closed

Replies have been disabled for this discussion.