Hello all,
I am creating a database that would allow us to track our forcasted product sales by period to each of our distributors. We also track actual sales but they don't have to convert (i.e. there doesn't need to be a tie in if a dealer forecasted 5 sales and only bought 4 other than 5 forecasted, 4 bought).
After a number of false starts and a decent amount of research I came up with the following design.
Tables:
Distributors (ID, Dealer_name)
Products (ID, Product_name)
Period (ID, Period)(Dec12, Jan13, Feb13, Mar13)
Transactions (ID, Dealer_name, Product_name, Period, Forcast, Sold).
Query
Dist_Query
Prod_Query
Per_Query
Tx_Query
Forms
Transaction Form
My idea is that anytime we get forecast info (or retro active sold info) it gets entered into a form which populates the Transaction table.
the Queries from each table I set up after reading about the evils of Lookups in tables.
I can get the Transactions table update from the form with the IDs for Cust, Prod, etc., OR I can get the Dropdowns to show the name of the Cust Prod but it onlly records the Forcast and Sold columns, it won;t drop the Customer ID in the transaction table.
help anyone?
thanks
EDIT: Don't be fooled by the use of the word forecasting, there is NO ACTUAL FORECASTING FUNCTIONALITY NEEDED