Database Design For Repetitive Weekly Imports

[JTesche]

I am new to access, but have extensive knowledge in excel (without VBA experience) and have come to a point that excel files are getting too large and cumbersome to operate efficiently so I am stepping into Access. I plan on self teaching myself but the one thing I can't do as well is the actual design of the database.



Specifically I am unsure if I should be using one table for additional weekly data imports or each week as it's own table.

Ex: I am reporting progress in construction, so hours earned quantities installed etc. and we do reports on a weekly basis. Generally speaking we are just interested in the current week's progress, but on occasion we need to do analysis against previous data.

Sheet A - Excel dump of a tracking program. Done every week (Currently at 110,000 lines each, grows ~5,000 a week)
Sheet B - Excel Dump of a control program. Done every week (1000 lines each, grows ~5 a week)
Sheet C - Living document to containing additional information for the data in sheets A and B (1000 lines with 5 lines manually updated each week)

I am unsure if it would be better to contain each weeks Sheet A in one table, with a new field containing the data for which week it is from, or if it would be better to create a new table for each new week.

• Typical analysis would be Week 10 Sheet A with Week 10 Sheet B and Sheet C, but would like to have the option to use Week 10 Sheet A with Week 5 Sheet B etc.

Hopefully this is clear, and thank you for the help!

Jared

[June7]

Access has a 2GB size limit.

I have a split db with almost 200 tables in it. The largest has 67000 records. The backend (the data) is 158MB and the frontend (GUI) is 40MB. This db tracks laboratory samples. We process less then 4500 samples a year and it has taken over 20 years to accumulate these records. So I expect we will be using Access for quite some time.

Of course, I have no idea how much data each of your records actually contains but it sounds like you could quickly outgrow Access. Might require multiple database files, maybe one for each year.

Use a single table for each sheet and append records each week. SheetA table should have a WeekDate field, NOT a new field each week. Access table has a limit of 255 fields.

Review tutorials at http://www.rogersaccesslibrary.com/