Stumped. Table Relationship / normalization

[bnickelsen]

I am having trouble visualizing how to break out the following info into functional Tables

I have a table to hold Items

tblListG1Items
IDItemList
ITemNumber
ItemName

I then have a detail table

tblDetailG1Items
IDItemDetail
FKToItemList
ItemMFG
ItemMSP
ItemLead

Here is where I get lost.
Each item has a variable for each month
So item 1
January = .01
February = .03
ECT.
Once set the Variables rarely change, at most once per year.

I will need to be able to pull an item and based on what month it is pulled uses the variable to multiply several other factures.

My first Thought was,

tblVarItemList
IDVarList
FKToItemList
VarJanF
VarFebF
VarMarF
--And so on--

It just does not look right.
Any Suggestions on what might work Better.

 

[Mile-O]

tblItems
ItemID - Number
ItemName - Text

tblMonthlyRates
ItemID - Number/PK
MonthID - Number/ PK
Rate

 

[bnickelsen]

Thank you.

Will give that a try.

 

[bnickelsen]

Another Question?

I can see how this might work. But As I Think about the form I will need to make I am not sure this will work.

For the imput form I would like to Have a drop down box with a list to select the item to work with. Then have a data sheet layout for the varible. Showing all months January thru December.

I don't see how to make this work. (Maybe blinded by my Excel layout.)

 

[bnickelsen]

More info.

As I try to think of the best solution I keep coming up with more questions. So I thought I might expand on what I need in hopes that I might work it out or someone may see what I am missing.

Ok from the Tables above Item is actually Locations and the variable is a cost rate for that location per day based on the month.

I.E. Location 1 might be $100 per day in January and $110 per day in February.

The cost rate might change once or twice a year. The rate will be used in several ways.
First when building a cost estimate I will need to query the highest current rate for that location. So from above it would be February ($110).The estimated rate will only be used as a budgetary number.

Next we would need billing info. If we did work in January we would Bill $100 per day, And February it would be $110 per day.

If the rate changes from the time we estimate it to the time we bill for it we still use the new rate regardless of the previous rate. The budgetary cost will be compared to actual cost to see if we stayed with in the budget. There is no need for historical data on the rates other then the budgetary cost. Again the budgetary cost would be Rate time estimated days. There is not a pattern to when the rates might change. At any given time anyone location and or month in that location might go up or down. The person updating the Rates would benefit from a datasheet view of the rates and being able to scroll over to month that changed and making the adjustment.

I know there is a lot of info here -- sorry.

Any suggestions would be appreciated.

 

[Pat Hartman]

SJ's suggestion is the correct way to store this data. You have a 1-many relationship (I know it is only 12 but that is still many in this case). If you store it "flattened" like a spreadsheet, you will be happy with your data entry form but everything else you do will require some amount of code or 12 queries instead of 1. It is possible to store the data properly and then use an unbound form for data entry. Unbound forms require a great deal of coding and are not for the faint of heart.

I have created an example that I called a Bound Denormalized Form. I am going to post it to the database samples section so look for it in a few minutes.

OK, I posted the sample database

 

[bnickelsen]

I think I am on the right track.

I attached what I have so far. I know I need to work on naming the tables. Just looking for oppions on Relationships.

Any comments would be appreciated