Thank you for your responses. I am sorry for sounding so vague. I am so immsersed in designing this database from scratch that it is getting difficult to talk to humans!
Each week I will be importing a speadsheet from Excel. It is a listing of customer invoice transactions. Each week's spreadsheet will have exactly the same structure: the same fields with the same names in the same order, same formatting. The only thing that will change is the length of the spreadsheet.
I import the spreadsheet into Access. The resulting table has much redundant data as follows. Though each row represents a unique transaction, certain fields such as Customer Name, for example, are repeated hundreds of times, once for each of that customer's transactions. Also repeated are UPC codes for the products involved and the corresponding product descriptions etc.
I run the Table Analyzer Wizard and split the imported table into smaller. related tables to eliminate, as much as possible, the duplicated data. For example, I create a Customer ID table with a one to many relationship to the detail lines in the original table, and do the same with other repetitious data such as a Product ID table for UPC codes, product descriptions, and an Invoice Detail table for repeating invoice numbers and invoice dates. Again, I create a one to many relationship from these sub tables to the detail lines in the original table. As you know, when you normalize a table like this using the Wizard, Access recreates your original table with a select query which mimics the appearance of your original table. However, the query is now using lookups to the new, smaller tables to eliminate redundant data fields.
I may be incorrect but I thought that if you wanted to run queries and reports against a range of the weekly spreadsheets you had imported and split this way, that they would all have to be put into one large table, say if you wanted to analyze or summarize 6 months' worth of these weekly tables. In order to append these separate normalized tables into one table, I thought one would have to convert the select queries the Access Table Analyzer Wizard generated when the tables were normalized into tables again with a Make Table query. So, my long-winded question is, if you convert the normalized tables (which are really select queries now) back into regular tables, do you undo the normalizing work you have done?
Or, is there a better way to keep the weekly spreadsheet import normalized but still be able to query against and report on multiple weeks' worth of imported files?
I have worked on and off with Access and studied it in online courses since 2002 but this is the first time designing a database from scratch.
Thanks for your help.
(PS. Thank you for the article links.)
