Count and Update

[Guest]

Hello -

I have ~30 small tables. From these tables I need to count the number of
records which match particular criteria and update a main table.

For example, the small table for Report X contains account numbers in one
column and several possible area codes in another column. (One account
number: one area code.) I need to take the count of account numbers which
match each area code and update the line in the main table which refers to
the report and the area codes of each smaller table.

I am having trouble trying to find a way to do this. Please help.

Thank you!

 

[Michel Walsh]

Get you data into ONE table, or one query, maybe doing something like:



SELECT 1 AS originalTable, columnOne, column2 FROM table1
UNION ALL
SELECT 2 , columnOne, column2 FROM table2
UNION ALL
....
UNION ALL
SELECT 30 , columnOne, column2 FROM table30



Save it, say, under the name q1, then it is a matter to do something like:


SELECT columnOne, COUNT(*)
FROM q1
GROUP BY columnOne




Hoping it may help,
Vanderghast, Access MVP

 

['69 Camaro]

Hi.

I am having trouble trying to find a way to do this.

The problem lies in your data structure. The best way to fix this is to
combine the separate report tables into a single table if they have the same
structure (from your description it sounds like they do), and use a query
that identifies and counts the records that meet your criteria. For
example:

SELECT COUNT(AcctNum) AS NumAccts, AreaCode
FROM tblAccts
GROUP BY AreaCode;

This way, your "main" table will never be out of date because someone forgot
to update it when new records were added or deleted in any of the "small"
tables. The number of accounts per area code is automatically calculated in
the query above.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.