Order Details

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

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.
--
Duane Hookom
MS Access MVP
--

"jules" wrote in message
...
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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jules" wrote in message
...
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

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.

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jules" wrote in message
...
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

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.

--
Duane Hookom
MS Access MVP


"jules" wrote in message
...
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.

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jules" wrote in message
...
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

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" wrote:

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.

--
Duane Hookom
MS Access MVP


"jules" wrote in message
...
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.

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jules" wrote in message
...
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

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

--
Duane Hookom
MS Access MVP
--

"jules" wrote in message
...
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" wrote:

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.

--
Duane Hookom
MS Access MVP


"jules" wrote in message
...
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.

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jules" wrote in message
...
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

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" wrote:

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

--
Duane Hookom
MS Access MVP
--

"jules" wrote in message
...
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" wrote:

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.

--
Duane Hookom
MS Access MVP


"jules" wrote in message
...
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.

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jules" wrote in message
...
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

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?

--
Duane Hookom
MS Access MVP
--

"jules" wrote in message
...
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" wrote:

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

--
Duane Hookom
MS Access MVP
--

"jules" wrote in message
...
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" wrote:

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.

--
Duane Hookom
MS Access MVP


"jules" wrote in message
...
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.

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jules" wrote in message
...
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

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

"Duane Hookom" wrote:

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?

--
Duane Hookom
MS Access MVP
--

"jules" wrote in message
...
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" wrote:

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

--
Duane Hookom
MS Access MVP
--

"jules" wrote in message
...
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" wrote:

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.

--
Duane Hookom
MS Access MVP


"jules" wrote in message
...
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.

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jules" wrote in message
...
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