Summing exported data from 2 tables into 1 table

[Guest]

An access 2000 database i am currently working on is getting big fast.
i have two tables that are being used to keep the data. tbleDefects and TBL
defect count. TBL defect count has the following columns; ID (auto
increment), Date, PartNum (Part Number), shift, sorttime, totalsort, NCM_num,
containment, & PlantNum. tblDefects has the following; AutoID (auto
Increment), ID (same number from ID field in TBL defect count), defcode
(defect code), defquantity(defect quantity). this is allowing one record for
the part number, but multiple records for the different defcode and their
corresponding quantities.

what i want to do is create another table where on a monthly basis i can
take all data from the other tables and combine it into 1 record per defcode
per partnumber. ( there is 27 defect codes, so there should be at the most 27
records per month per part number). i am wanting to sum all of the defects
for the same defect code for the same part number.

ex:
Part number 1234abc had 3 different defects. def codes 1,2,3 respectively
and quantitys 4,5,6 respectively. this gives me one record in TBL defect
count, but three records in tbldefects. now after 5 days, assuming; the same
partnumber, defect codes, and quantities each day, the third table should
have three records in it, 1 for each defect code, along with the sum of
defects for each code.

this is meant just to archive the data for a few months at a time before
deleting, am hoping to keep the database from getting too large.

Thank you in advance for any help and insight you can give...

 

[John Nurick]

Can be done. Work with a copy of your database and take it gently,
experimenting till you get each stage right. (And also remember that an
Access database can hold a lot of data by most people's standards:
several hundred megabytes are not a problem if the database is properly
designed.)

1 First create a Select query that joins your two existing tables on ID
and delivers the fields Date, PartNumber, defcode, defquantity (and
therefore zillions of records). (By the way, it's best not to give
fields names like Date and Name, because these are also the names of
common functions or properties, and confusion can result.)

2 Next, add parameters so you can enter the date or range of dates you
want.

3 Make a copy of the query. Click the Totals button on the toolbar and
modify the query so it returns the total of defquantity grouped by
PartNumber and defcode and sorted on PartNumber, defcode.

4 Then create a new table with fields DefectDate (actually the first or
last day of the month in question), PartNumber, defcode, defquantity.

5 Convert the query (3) an Append query to append its records to the new
table.

6 Go back to the copy of the query before you made it a totals query.
Modify this so it deletes the records that the other query has
summarised in the new table.

Finally, remember to compact your database regularly. Deleting records
doesn't stop it growing unless you also compact it.

 

[Guest]

ok thanks, will give it a try when i have some time, appreciate the quick
response and keep on rockin'.