Order Details

[jules]

Hi everyone. I am trying to build an order entry from scratch. I have
various Microsoft Press books which have helped a bit. My question is:

In the order details table in the book, there is a field for Unit Price but
would this not mean repeating information - the price is already in the
products table. The reason I am asking this is that I created a query for a
form and when I put in the unit price from the order details it didnt show
any information. It only showed the price when I took it from the Products
table, but then that wouldnt give a true picture of the orders if the
suppliers' prices went up? Is that right??

But if i built an expression in the query it would be DLookup and it would
take it from products anyway.

I think i have made my brain go round in circles here!! Can someone put me
out of my misery.

Thanks

 

[Duane Hookom]

The Unit Price is going to change over time (noticed gas prices lately).
The method I would use to populate the "current" unit price in the order
details is to use a combo box. The row source might be something like:
SELECT ProductID, Product, UnitPrice
FROM Products
ORDER BY Product;
Then add code to the after update event of the combo box:
Me.txtUnitPrice = Me.cboProduct.Column(2)
This will push the unit price from the products table into a control bound
to the UnitPrice field in the Order Details table.

 

[Allen Browne]

There's an important distinction to get right here, so good question.

The price in the Product table is the *current* price of the product. If you
believed that no price for any product would ever change, then you should
not store it in the Order Details table, because it would be redundant.

In the real world, product prices do change, so if the Order Details table
contains a different price than the one in the Product table, this would not
represent an error: it would be very meaningful data. Perhaps the invoice
was given at a discount, or perhaps the price has changed, but the two can
be validly different.

To generalize: if the related field could validly have a different value,
than you *need* the field--it is not redundant. If the field should never
have a different value because that would be an error, then you must *not*
have the field.

For an example of how to get the current product price out of the Product
table and into the Order Detail when the user selects a product in the
order, open the Northwind sample database, Orders Subform, and look at the
code in the After Update event procedure of the ProductID combo.

 

[jules]

Blimey!! I nearly fell off my chair when i saw the "big guys" had answered
my post!!! Thank you for your input. that makes things a bit clearer for
me now.

 

[Duane Hookom]

I don't mind being referred to as a "big guy" and it is an honor to be
grouped in the same category as Allen.

 

[jules]

lol I meant it figuratively of course!
I just have one little problem regarding this DLookup (if you are still
talking to me!). I actually should have realised it myself - the
OrderDetails subform is in a continuous form view with two synchronised
combos (1st combo - select category and 2nd combo select product) there is a
text box over the 2nd combo so it always shows currently selected product but
obviously the price column stays the same as the first row. I was wondering
how to solve this? should I requery the productID combo? put a text box?
Thanking you in the hope that you answer

 

[Duane Hookom]

Isn't your price text box bound to a field in the order details?

 

[jules]

Yes it is - the order details table. The only time the price was changing to
reflect the selected row was when I used the price from the products table in
the order details query - that's what prompted my first post.

 

[Duane Hookom]

You stated "obviously the price column stays the same as the first row".
What do you mean by price column. Isn't this the text box that is bound to
the price in the details table?

 

[jules]

Hi again - sorry to be dragging this out. Yes, it is bound to the table and
i really dont know why it isnt showing. I have a sneaky feeling its
something to do with this synchronised combo in a continuous form thing. It
took me about 2 or 3 months and £100 worth of books to find out how to do
that only to get this far and the price isnt showing!!! Aaargh. Other than
this explanation i cant work it out.

Basically, the order detail table has the usual serial number, description,
price etc. The query i have based the form on takes orderID, serial no and
price from order details and product description from products. I have
checked the relationships against the Garden Database sample in one of the
books i have. I dont really know where to go from here.

i appreciate your helping me with this but i dont really want to outstay my
welcome or waste any of your time. As the database is in its practice stage
anyway i might just start again and forget the synchronoised combo thing.

Best wishes
julia