mattytee123@hotmail.com wrote in
news:1119914769.915142.176690@f14g2000cwb.googlegr oups.com:
[color=blue]
> 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.
>[/color]
EXAMPLE 1[color=blue]
> 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
>[/color]
EXAMPLE 2[color=blue]
> 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?[/color]
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.
[color=blue]
> 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.
>[/color]
EXAMPLE 3[color=blue]
> 1111 Blue
> 1234 Green
> 2222 Red
> 2468 Orange[/color]
EXAMPLE 4[color=blue]
> Blue 2
> Green 4
> Red 1
> Orange 3
>
> I would appreciate any assistance anyone can provide me![/color]
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 
