My table is gettting to large what do i do

[frys]

I have a problem, I have a 3 tables.

Residents
Programs
Attendance

i am tracking which Residents came to which programs.

I have 12 programs a day that 160 Residents attended and do this 7 days a week

so

12 X 160 = 1920 records in the attendance table per day

1920 X 30 = 57000 Records per month.

the size of the attendance table is getting huge. Is there any suggestion as to ways to "cut the fat" ...

P.S. I have to keep these records for 7 years

so in 7 years 4 838 400 records before i can start removing some.

I thought of archiving them but i can't figure out how to archive just a specific bunch.. since the date is attached to the program not the attendance

thanks for any help

[PianoMan64]

frvs,

Since I don't have much information about the structure of the tables and what fields you have. It really makes it very difficult to answer your question.

If you can send along specifications and ask the question using the information provided, it would give us (the community) a better idea as to how to begin to address you're problem and/or question.

Thanks,

Joe P.

[7 of 9]

PianoMan64 is right more information would be helpful but based on what information you did give; I would ask the question is it necessary to keep 7 years of records in one table? If so why not just have a temporary table with the current month’s records and at the end of the month append to a history table with the rest of the current year’s data and at the end of the year create a new table for year?

[Stewart Ross]

Hi. Access has a limit of 2GB on the size of any one table within a database, and to the database itself. 4m records could bring you close to this limit already.

I'm with 7 of 9 on this one; I'd suggest that you keep separate year copies of your database for archive purposes and start each year with a clean copy (i.e. one cleared of attendance records).

I do something similar with a student records database, which comprises daily table copies from a large-scale Oracle student-records system. The Access database is used for management information analysis and reporting, and I retain annual datasets from 2003 onwards for archive purposes. Doing so keeps the datasets manageable, and the overall database size well within the 2GB limit for each set.

If you find it difficult to follow up the separate year-copies approach we should be able to help you with a query which could join your programmes and attendance records to allow you to archive your older records, but as Pianoman said we would need more information from you to do so. We'd need the metadata for your tables - the field names and types, and details of primary/secondary keys.

-Stewart