Suggestions on how to structure my tables

[adamlaing]

I am creating a database where the user can import data from a number of spreadsheets (one spreadsheet for each company). Currently I have one table for each company with the year being the primary key. Each year when a rolled over spreadsheet needs to be generated for a certain company I have Access import the data to a blank template in excel which will display the prior 4 years of data (for informational purposes) and leave a column blank for the end user to fill in the current year data. When returned the current year data is imported into the database.

My problem is that I cannot see a way to do this without having a seperate table for each company. If I have one table with all company data I cannot use the year as the primary key. Any suggestions on how I can reduce the amount of tables I need. Setting up the rest of the tables wont be a problem, but future users of the database will have to go through the process of creating tables for new companies and creating all the queries and macros associated with that table.

 

[GabrielR]

I understand your problem, I would not use the year as Primary Key though, I always use an Access generated Autonumber as PK.

To answer your question, I would set up a lookup table with the companies information and the other table with the imports from excel... That is only two tables and one place to query.

 

[Pat Hartman]

Access/Jet supports compound primary keys of up to 10 columns. To create a compound key, highlight (cntl-click) up to 10 columns. Then will all the key columns highlighted, press the key button on the toolbar.

Company and Year would make the rows unique.

If you choose to use an autonumber primary key (better in certain cases), you would need to create a unique index to prevent duplicates. To do that, you need to open the indexes dialog. In the index name field, type a name for the index. Then select company from the field name dropdown and at the bottom of the form change unique to Yes. Then on the next line leave the index name field blank and choose year from the field name dropdown.

BTW, Year is a poor choice as a column name. Do not use function or property names and also avoid embedded spaces and special characters. In Excel, the form and the table are the same so you choose people friendly column names. In a database, the user should never see a raw table so it is best to use computer friendly names and in your forms, use captions with people friendly names.