Data that varies (Prices)

[nicksource]

I have a simple Goods in Goods out Database, it has a list of products, and then you can set which goods have come in, and which goods have gone out to a customer.

I want to keep the data for the customers, so I can look at a certain customer and see what goods they have had in the past and the total cost of these.

My only issue is, if I store the price along side the product, in the 'products' table. If the price was to ever change, all the prices for previous goods that have gone out to customers, will change to, which will make it innacurate.

I was thinking of having a new table for prices, then when a price is entered for a product it creates a new row with that price and instead of the product refering to its tables price, it refers to the 'price' table ID for it.

I hope that makes sense, any ideas?

Thanks.

 

[KenHigg]

You should have some type table that records customer transactions. Then a table for transaction line items. That's where I would store the price.

 

[boblarson]

This is where denormalizaton can make sense. You can do what you were thinking but I would just store the price in the "order details" table (or whatever table you have that is like that).

 

[boblarson]

You should have some type table that records customer transactions. Then a table for transaction line items. That's where I would store the price.

AHA! Beat me this time, did you?

 

[nicksource]

Sounds like a good idea however would that mean me entering the data in each time?

 

[KenHigg]

AHA! Beat me this time, did you?

I'm learning to make my responses shorter to get in front of you

 

[boblarson]

You shouldn't have to enter the data. If you select the product from your combo or list box, you can have code to populate the price from the combo box if you make sure to have that column in the combo's rowsource (you don't have to show it, but it needs to be there and the number of columns set accordingly including the column widths - 0" to hide them).

 

[boblarson]

I'm learning to make my responses shorter to get in front of you

Ah, you've learned my secret. Start short and then go back and edit the post to add more

 

[KenHigg]

Sounds like a good idea however would that mean me entering the data in each time?

This is just an idea but I would think you could use a dlookup() to get the current price after you've selected an item - ?

 

[KenHigg]

You shouldn't have to enter the data. If you select the product from your combo or list box, you can have code to populate the price from the combo box if you make sure to have that column in the combo's rowsource (you don't have to show it, but it needs to be there and the number of columns set accordingly including the column widths - 0" to hide them).

That's a sneaky, albeit a cool, trick -

 

[nicksource]

Ahhhh I understand, sorry.

So the products table still stores the price but it duplicates the data into a customers transaction table, so if the products price does change, the customers transaction stays intact.

Thanks a lot guys, very fast response!

 

[KenHigg]

You caught on to that real fast - I've argued that 'till I was blue in the face before and still couldn't get the point across...

 

[nicksource]

I've this setup now but have run into a problem,

How do I take the 'price' from the 'products' field and insert it into the 'price' in the new Goodsout table when I add the new products?

 

[nicksource]

I can't figure this out.

I have a field that grabs my price from the products:

=DLookUp('[Price]','Products','[Products]![ID]=[ProdID]')

Then a 'price' field in my GoodsOut table, how do I transfer the price to this in a form? If in my 'price' field on the form I do =[FormFieldwithFormulaAbove] It doesn't enter it into the table, it just displays it again.

 

[boblarson]

You don't need a dlookup, just include it in the combo box. I'm attaching a simple sample:

 

[nicksource]

Thanks a lot Bob, that's a very helpful sample, I've got it going now.

 

[boblarson]

Glad to hear!

 

[georgedwilkinson]

So Nick, I've seen your graphic somewhere. Was that from Cosmo's Cosmic Adventures?

 

[nicksource]

It's from 'Commander Keen':

 

[georgedwilkinson]

Oooo, that's right. I loved that game!!!

But it was so hard to move Keen without him falling off the edge.