Northwind

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Northwind, a DLookUp function on the [order details] subform populates
[unitprice] with the price of the product being ordered. This is to record
the Product price (which can change) at the time of sale. Here's the
question !
Does anyone know of a way of doing this at query level ? That is, not using
a subform to add products to an order, but using only the query that feeds
the form. It's the only hurdle that prevents me from building an order
processing model that uses only tables and queries. TIA, Simon
 
This is the sticking point:
record the Product price (which can change) at the time of sale

If it can change, How do you know what it will be?
How will your process know who ordered what?
 
The Product price can change for subsequant orders. The original price is
saved to [unitprice] in the [order details] table by a DLookUp function on
the [order details subform].

I want to perform this operation in the query that feeds the form i.e.
[order details extended].

Somehow, I need to run a macro or code when the product is added to an
order. This is in a query not a form. Ideally, I'd like to set a table
property validation rule that [Product Price] = [UnitPrice]. But these
fields are in different tables and represent each side in a one to many query.

TIA Simon



Klatuu said:
This is the sticking point:
record the Product price (which can change) at the time of sale

If it can change, How do you know what it will be?
How will your process know who ordered what?

SimonW said:
In Northwind, a DLookUp function on the [order details] subform populates
[unitprice] with the price of the product being ordered. This is to record
the Product price (which can change) at the time of sale. Here's the
question !
Does anyone know of a way of doing this at query level ? That is, not using
a subform to add products to an order, but using only the query that feeds
the form. It's the only hurdle that prevents me from building an order
processing model that uses only tables and queries. TIA, Simon
Click to expand...
Click to expand...
 
Since you are using a query for this, why not just join the product table and
use the unit price from there?

SimonW said:
The Product price can change for subsequant orders. The original price is
saved to [unitprice] in the [order details] table by a DLookUp function on
the [order details subform].

I want to perform this operation in the query that feeds the form i.e.
[order details extended].

Somehow, I need to run a macro or code when the product is added to an
order. This is in a query not a form. Ideally, I'd like to set a table
property validation rule that [Product Price] = [UnitPrice]. But these
fields are in different tables and represent each side in a one to many query.

TIA Simon



Klatuu said:
This is the sticking point:
record the Product price (which can change) at the time of sale

If it can change, How do you know what it will be?
How will your process know who ordered what?

SimonW said:
In Northwind, a DLookUp function on the [order details] subform populates
[unitprice] with the price of the product being ordered. This is to record
the Product price (which can change) at the time of sale. Here's the
question !
Does anyone know of a way of doing this at query level ? That is, not using
a subform to add products to an order, but using only the query that feeds
the form. It's the only hurdle that prevents me from building an order
processing model that uses only tables and queries. TIA, Simon
Click to expand...
Click to expand...
Click to expand...
 
I'd like to set a table
propertyvalidationrule that [Product Price] = [UnitPrice]. But these
fields are in different tables...
Click to expand...

The table Validation Rule in table properties is for row- (record-)
level validation i.e. can refer to values in different columns (fields)
in the same row.

Assuming Access 2000 or above (i.e. Jet 4.0), you can use a (truly)
table-level CHECK constraint. A usage example may be found here:

Description of the new features that are included in Microsoft Jet 4.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;275561

"One new feature added to the Jet CREATE TABLE syntax is Check
Constraints. This new SQL grammar allows the user to specify business
rules that can span more than one table."
...and represent each side in a one to many query.
Click to expand...

There may be a timing issue because it is not possible to
INSERT/UPDATE/DELETE rows from two tables simultaneously and the CHECK
may bite before both tables have been altered. An example may be found
here:

http://groups-beta.google.com/group/microsoft.public.access/msg/80f53c76fa01c832

Jamie.

--
 
Back
Top