Windows 10 
Access 2016
Greetings.
I currently have a forecasting database with 3 separate tables.
1. INFO (which is my main table) has a product#, part# and all pertinent information relating to the product.
2. ORDERTABLE which duplicates product# and has fields for my forecasting "orders"
3. SALESOUTTABLE which duplicates product# and has fields for sales out per item from 2018 to present by month.
I know that I'm not supposed to have duplicate information in separate tables but my issue is this.
Due to the structure of our organization I am not allowed to directly access our main "somewhat archaic but functional" database but can pull data "reports" in excel format from it.
Each table that I have has fields/records that are updated on a daily basis with a downloaded spreadsheet and an update query, plus has input from me added.
The problem I have is that each spreadsheet has it's own quirks, spaces, etc that make them unique and different.
Fortunately, we don't add additional skus that often and being that I'm the only one using this database, I just update the INFO table which has autonumber ID and then copy the product# to the other 2 tables which I have set up with a longinteger ID and duplicate the autonumber ID .
It's been working this way for 2 years with no issues but having some free time I'd like to re-design which will allow me to further enhance my Access knowledge at the same time.
I'd consider myself a beginner at best now and would definitely like to build this with a better relational structure, right now I have relationships built using the product# but no foreign keys yet.
I've read a lot of online help sites regarding primary key, foreign keys but without duplicating the product# in each table, I've not been able to get them to work?
If anyone can lend advice or point me to a good resource, I'd be extremely grateful.
Thanks in advance for any help.
Reply With Quote
