Better to link to 500-1000 excel sheets, or upload it all into access?

[Accesshelp2013]

Hi,



I have some design questions for a project I’m working on for work which requires creating a database and I’m fairly new to access—although I use excel quite frequently and do various financial modeling tasks in it. I have about 500 contracts that I model individually in excel (will probably grow to at least 1000). For each of these contracts, I determine investment amounts that the client should invest into them each month and project how much I think the investment will be worth at the end of the month. The excel output of the model is a schedule going forward of 720 months(rows) and about 22 columns worth of data. I want to put each of these schedules in access so I can query the data across 500 policies. Here are some sample queries I would like to run, “For all 500 policies, what is the total investment amount for November 2013? What is the total projected worth of the investment at the end of November 2013? Etc.


Here are the access design questions:

1. What is the best design way to accomplish this? It seems that uploading 500 tables of 270 rows and 22 columns is a lot of data and would make the database excessively large. It looks like each table adds about 1.5mb to the size of the database. So 500 would be like 750mb. And if I had like 4,000 it would be at least 3gb. Is this a lot for a database? Would it be better to store the excel outputs on the harddrive and link to them via access somehow? Is there a tutorial on how to do this?
2. If it’s better to upload the tables into access, is it better to do so as an individual table linked to each contract or as part of one large table that holds all the data in one table. It seems to me that the former is better as the latter takes the risk of somehow corrupting the entire file(not to mention how large it will be).

Thank you for your time.

[ssanfu]

IMHO,


1)

What is the best design way to accomplish this?

It depends... don't know anything about your fields/table structure.

It seems that uploading 500 tables of 270 rows and 22 columns is a lot of data and would make the database excessively large

That would be 135,000 records. Not so many..

I have a dB where one table has 1,551,739 records (to date) with 20 fields. There is a total of 26 tables. The dB file size is just under 237 MB. This is just the BE... The FE is about 6 MB.

It looks like each table adds about 1.5mb to the size of the database. So 500 would be like 750mb.

Did you do a Compact & Repair after the import(s)??


2)

is it better to do so as an individual table linked to each contract or as part of one large table that holds all the data in one table

If the Excel workbooks have the same structure, I would have the data in one table... again, I don't know what the data looks like. And you should have a normalized structure.

If you have each of the Excel workbooks in a separate table, I doubt that you can create a union query with 500+ tables.
You wanted to run queries like "For all 500 policies, what is the total investment amount for November 2013?". Not gonna happen!

My $0.02

[ItsMe]

If the Excel workbooks have the same structure, I would have the data in one table... again, I don't know what the data looks like. And you should have a normalized structure.

What Steve mentioned here is key.

You should take a long hard look at your worksheets and ask yourself what is similar and what is different between them. I know you are familiar with them but you need to view them from a different perspective. I wish I could say looking at example databases will instantly give you the necessary perspective but, that is just not the case.

There is no way for you to how to normalize the structure. You have to be able to ask yourself how you will assign primary keys to your records. You will need to define information that is somewhat dynamic yet repetitive and isolate this information in its own table. You will need to define data that changes often and place this in yet another table. Somehow, you need to define your tables in a normalized structure. It is a learned process.

You could start by using the Wizard to import one worksheet into a new table. Paying attention to the questions asked by the wizard may enlighten you to some basic fundamentals. Like Steve mentioned, you need to see how to get your 500 worksheets into a single table. The tab names don't change too often and these may represent records in another table relative to the first table. You may be used to naming a new worksheet and considering it like a new table but really it is probably just another customer or project that shares much of the same structure as previous worksheets.

These are the nuances of your worksheets you should start to identify.

Now you almost have a nickel. $0.02

[Accesshelp2013]

Hi ssanfu and ItsMe,

first thank you so much for taking the time to respond. Much appreciated! Based on your advice, I think I'll try to make one large table of all the data. I'll import each of the spreadsheets into this table. Each spreadsheet has the same format, number of rows, columns, etc, so I should be able to hopefully control the size of the table by limiting the fields to certain types(long integer, single, etc). Otherwise, it might be hard to link 500 individual tables together even if it's in access as opposed to excel. Thanks again!

I'll try the Compact and Repair feature to try and limit the table size as well.

[ItsMe]

Sounds like you are on your way. Don't get discouraged by my mentioning it is difficult to do. I only mention it so you expect to unwittingly work your way into a corner from time to time. It is going to happen. You just need to learn from it.

As you name your fields for your data tables and forms try to avoid, spaces, special characters, and reserved names. Access uses special characters and certain words to accomplish VBA tasks. For example, using the word "Key" as a field name can cause confusion as well as "Key#". An approach I sometimes use is to combine two words. MainKey for the primary key in my main table might be appropriate.

Tables, Forms, Queries, Reports, etc. should use a naming convention so they are easily identifiable and also avoid reserved words. tblMaster, frmCustomers, qryCustCombo, rptMonthly respectively.

[ssanfu]

Each spreadsheet has the same format, number of rows, columns, etc

The number of rows doesn't really matter. It is the field types. As ItsMe said, pick a naming convention and stick with it. try to make the field names descriptive but not too long. The longer a field name is. the more typing you have to to. And there are character limits in queries. An example would be "EventDesc". I could use "EventDescription", but .... too much typing for me - my confuser don't spell too well!!

"Name", "Date" and "Time" are really, really bad field names because they are reserved words. Plus they are not very descriptive.
Sere is a list of reserved words : http://allenbrowne.com/AppIssueBadWord.html

Good luck with your project. Post back if you have more questions.
And welcome to the forum


Oops, I forgot to say:
Forget everything you know about spreadsheets. Access is not a spreadsheet. You need a different way of thinking when working with a RDBMS ........