I am a beginner Access Programmer, so my understanding at this point is pretty basic, so that could be why I have run up against a wall with this one.

I am trying to create a database that creates Estimates. In the most basic setup, I have a customer table, an estimate table and an item table. The item table would have a description and a price per unit. With those tables I can set up a basic form that creates an estimate for someone. The wall I am referring to is when I ask myself, "how am I going to deal with price increases?" With the current setup, when I change the prices in the item table it will change all previous estimates prices.

How have others dealt with this scenario? I would have a hard time believing this is a rare question. In the real world this is going to happen. Yet I have several access 2007 books and none of them seem to address this. The closest thing I have seen that seems like it might be in the right direction would be some sort of update query.

Any thoughts on this would be great,
James

That's one of those issues about data normalization and when it can be beneficial to NOT completely normalize your data and store historical info. You have two options - one is more "technically correct" but one is easier to implement and will work fine.

1. You can create a price table where you have effective dates for your prices. This would be more normalized but can be a challenge when piecing together later.

OR

2. Just store the price with the estimate so you have the price given at the time.

Bob,
Thanks for the quick reply.
If I went with the 2nd option, how would I go about doing that? Would I be able to have some sort of master price list table that the estimate pulls prices from?
Thanks,
James

You would have a table with the current prices and maybe with a combo box you would select the price and have it store it into a text box that is bound to a field in the table where you are storing the estimate data.

Is there some way to get the combo box to save more than one value into multiple fields? What I would like to do is have a drop down box that lists all the product codes, then have it pull the Item Name, Description and Price over to the Estimate table. The combo box only seems to be able to save one value into a field.

If you have the query that is the rowsource for the combo box have all of the columns you want, you can hide them by setting the column widths to 0" and setting the Column Count to the correct number of columns.

So, for example, if I have 4 columns - Item Number, Item Name, Description, and Price, I can refer to those columns in the After Update event of the combo box and set other bound text boxes to those values.

So, I may have the column count set at 4, the widths set at 0";1";0";0" so that the item name shows but the bound column is set at 1 (Item Number). Then, in the After Update event of the combo box I can go with:


Me.YourItemNumberTextBoxName = Me.YourComboBoxName
Me.YourItemNameTextBoxName = Me.YourComboBoxName.Column(1)
Me.YourItemDescriptionName = Me.YourComboBoxName.Column(2)
Me.YourItemPriceName = Me.YourComboBoxName.Column(3)


The columns in the combo box are zero-based so the first column is column 0, the second is column 1, etc.

Thanks Bob. I think that is what I needed for that.
James

i use a slightly different means to get to the ends...

i store my historical prices in the orders table, but i do it at the time of the order creation by making the default value of the control for the historical price on the form as the value of the current price at the time of making the record.

see attachment. seems to work for me (so far - only recently started doing this).