Need to build Summary Table /Report

[massa]

I have built a DB with tables for each of the following years 1998 thru 2007.
All tables have the same fields with thousands of records for each year. I
need to update the totals from each year by location code(one of the fields
in the table) and the usd$ amounts associated with that code. Can anyone lend
a hand on how to run? Thank you,.

 

[mcescher]

I have built a DB with tables for each of the following years 1998 thru 2007.
All tables have the same fields with thousands of records for each year. I
need to update the totals from each year by  location code(one of the fields
in the table) and the usd$ amounts associated with that code. Can anyone lend
a hand on how to run? Thank you,.

It doesn't sound like your database is normalized. You would probably
be better served to have one table with all the data and a column to
indicate what year.

A query should help you get the information you're looking for. Add
one table in, and then pull down the LocationCode and Amount. Click
the Totals button at the top of the screen (The funny looking capital
E) Leave LocationCode as "Group By" and change Amount to "Sum"
underneath field names. That will give you a sum fore each
LocationCode.

You'll have to create this query for each of your yearly tables. This
is why it would be better to have just one big table. You could also
group by year, and then you would just need one query.

HTH,
Chris M.

 

[massa]

Thank you Chris for the explanation. Can you tell me how I would merge all
tables together into one master table. This would make it more efficient.
Thank you.
Amy

 

[mcescher]

Thank you Chris for the explanation. Can you tell me how I would merge all
tables together into one master table. This would make it more efficient.
Thank you.
Amy









- Show quoted text -

For 1998, do a make table query.

SELECT 1998 AS DataYear, [all the columns from your table] INTO
tblAllData FROM tbl1998Data;

Then for the rest of the years, do an append query.

INSERT INTO tblAllData (DataYear, [all the columns from your table])
SELECT 1999 AS DataYear, [all the columns from your table]
FROM tbl1999Data;

Run it, and then update the years, and run again for each of the years
until 2007
HTH,
Chris M.