Help with DB design

[pbowler]

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

[ssanfu]

Having a field named "ID" in multiple tables gets confusing....
You might think about this as a structure:

Table Distributors
-------------------------
Dist_ID (PK - Autonumber)
Dealer_name (Text)

Table Products
----------------------
Prod_ID (PK - Autonumber)
Product_name (Text)

Table Period
-------------------
Per_ID (PK - Autonumber)
Period (Text) (Dec12, Jan13, Feb13, Mar13)

Table Transactions
----------------------------
Trans_ID (PK - Autonumber)
Dealer_name_FK (FK - Long - link to Table Distributors)
Product_name_FK (FK - Long - link to Table Products)
Period_FK (FK - Long - link to Table Period )
Forcast (Single)
Sold (Single)

I can get the Dropdowns to show the name of the Cust Prod

In the combo box (NOT a look up field!), the bound column should be the PK, then set the column count property to the number of columns in the SQL. So if there are two columns, the PK and a name, the column Width Property should have 0 entered. This will hide the PK field and display the name.

[pbowler]

OOPS, my mistake, by ID I meant PK, I left it as the default which access names ID.

so in Cust, Prod, Period there are only two columns, PK then my info.

I will double check my setup and my combo boxes (they are combo boxes).

By long integer Link to Table xxxx are we talking about a lookup?

I don't mind but I thought that was a No No


making changes now!
thanks!

[ssanfu]

By long integer Link to Table xxxx are we talking about a lookup?

I don't mind but I thought that was a No No

Look up FIELDS are a Bad.
Look up TABLES are Good. They are usually the source for a combo box/list box.