By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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.