473,573 Members | 4,181 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Counting of records in a union query

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 4336
ma*********@hot mail.com wrote in
news:11******** **************@ f14g2000cwb.goo glegroups.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.F ield1, tblFirstTable.F ield2
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.Co de, tblColorLookup. Color
FROM qryCodeCount INNER JOIN tblColorLookup
ON qryCodeCount.Co unt = 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
ma*********@hot mail.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2273
by: M Stery | last post by:
Have two base queries used for mailing labels with id,name,address,city,state,zip that are combined via a union query. One base query uses home address; the other base query uses biz address. I only want one label per person. In a few cases, I have a record with both a home address and a biz address, which I don't want in the labels. How or...
0
2012
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary form by field named TestID. The subform is used just for displaying site address data, data which is stored in another table named...
1
2983
by: snOOp | last post by:
I am trying to combine the data from two similar tables into one query, but I need for all of the records from both tables to show up and I want the ones that have matching 'emplid' to be combined into one record showing both the 'empstatus' and 'strole' fields. The following query works, but does not combine the matching records: SELECT...
1
10590
by: Brian | last post by:
I'm trying to find a way to search multiple tables for the same record. Say I have 3 tables all with a name column, I need to search all 3 tables and find matching names. Is there an easy way to accomplish this?
1
1332
by: Dixie | last post by:
I have 4 fields (byte) which can each contain a number between 1 and 40 odd.. The numbers in each of the fields represents the same information that can occur in one of 4 places. In each set of 4 (which would represent 1 record), each number can only occur in 1 of the 4 fields, it cannot be in the one record twice. I need a report that...
2
8071
by: Zeljko | last post by:
I'm creating Address book. Header of the main form (frmAddress) contains combo box (cboFilter) to filter records by Occupation on main Form(Ocupation1). That's working. Combo box also have "Show All" in one row. ==Combo box query: SELECT tblOCUPATION.id, tblOCUPATION.txtOcupation1 FROM tblOCUPATION UNION Select "(All)" as Bogus, Null as...
9
3054
by: Ericka | last post by:
I used the following query to count the number of records from each table. Select "Employee_Table" as Table_Name, Count(*) As Record_Total From Employer_table UNION ALL
2
4934
kcdoell
by: kcdoell | last post by:
Hello: I am trying to create a union query but do not have a lot of experience. Basically I have the below tables: The Tables: Table Name = tblPrior CreditRegIDFK; Number; Foreign Key for the Credit Region ID
0
1561
by: Hamayun Khan | last post by:
Hi All I am using the following query to select records from two tables. Select JobTitle,JobDesc,Scraped,logoimage,JobPostID,SchoolID,web,MemType,InstitutionName,PayScale,LEA,Contract as ContractType,HoursAbl,StartDate,ClosingDate,asap from tblJobScrap Union ( SELECT...
0
7789
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7707
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8037
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7800
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8086
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6432
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5296
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1325
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1048
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.