Access database question

[tbronnenberg]

Good Afternoon, I have an access 2010 question regarding table building. We are creating a database that will allow the months/ years to keep growing. We currently receive a monthly file that needs to be imported each month. The file provides us with staffing counts along with total hours by person. In order to show historical data, we would like to provide 6 years worth along with future monthly files. My question is will one database be needed to house 6 years worth of monthly files or would multiple databases be required. Also, we have multiple sources which is the monthly files regarding staffing counts which would have a unique identifier; however the financial file is driven in a completely different matter. How can I combine the files to provide the most accurate data when a unique identifer would not be available?



Thank you!

[ranman256]

Yes, 1 db will hold millions of records.

When you import data, the table can generate unique IDs (autonumber) use these local IDs to join data.

[tbronnenberg]

I have another question regarding the above original question, There will be not only 6 years worth of files for one source, but 6 years of files for 2-3 other sources that will need to be combined into the one database. Is this more efficient to have one database to hold all of this or multiple databases. Can you tell me if there are size restraints that could impact this massive detail?

Thank you!

[tbronnenberg]

Also, my records are too large for one database, would you recommend having one database per year? thanks

[orange]

Please tell us more about

my records are too large for one database

[bnorton]

You almost always want to keep your data in a single database. If you have historical data that you need to keep only for backup purposes or that needs to be accessed rarely like once a year, then you might want to split it into multiple databases. But these are exceptions. I'm not sure what you mean by your records being too large, but that usually means that there are too many fields per record, and that usually means that you need to a little database design work. We can help you with that.

[tbronnenberg]

You almost always want to keep your data in a single database. If you have historical data that you need to keep only for backup purposes or that needs to be accessed rarely like once a year, then you might want to split it into multiple databases. But these are exceptions. I'm not sure what you mean by your records being too large, but that usually means that there are too many fields per record, and that usually means that you need to a little database design work. We can help you with that.

The issue is there will be twelve tables per source per year. So, for example, there will be 72 tables for the staffing report as they are imported monthly and are per year dating back to 2009. Also, this will be on going since so there will be even more tables. there will be two other sources that will also need to be brought into the table, therefore creating 72 + 72 more tables for each month / by year. My question is, since this is so large, would this be easier to have a single database that would house all of the 3 sources and do this by year? Let me know if this doesn't make sense and you need more detail. Thank you!!!

[June7]

Conventional database would have one table for all years and sources with date and source fields to categorize records.

What is the 'staffing report' - exactly what are you importing - data or PDF?

[ssanfu]

The issue is there will be twelve tables per source per year.

For one table... what are the fields? Are all 12 tables identical in structure?


If you mean that you have 12 tables - one for each month, your design is flawed (not normalized). You should have one table with an additional field for the date.

And if the 3 sources have the same fields, then you would have one table with a additional field for the source.