Dlookup in reports

[Tellu]

I have Dlookup-function in report which lookups the price of a product based
on customerID and productID. This works fine as far as it consers product
details. But this report is an invoice which should have a total sum also. In
report I can't refer to the name of a calculated field in sum-fuction. What
should I do. Do I have to put my dlookup to the query or...

Thankful for Your help!

 

[Allen Browne]

Tellu, I'm going go suggest that you solve this by redesigning the table.

From what you describe, different customsers have different prices for the
same product. Therefore I assume you have tables like this:
- Customer table, with CustomerID primary key
- Product table, with ProductID primary key
- Price table with fields:
CustomerID relates to Customer.CustomerID
ProductID relates to Product.ProductID
PriceEach Currency.
- Invoice table, with fields:
InvoiceID primary key
CustomerID relates to Customer.CustomerID
InvoiceDate Date/Time
- InvoiceDetail table (i.e. line items of an invoice), with fields:
InvoiceID relates to Invoice.InvoiceID
ProductID relates to Product.ProductID
Quantity Number

Now it seems that you are trying to DLookup() to get price, based on
CustomerID and ProductID.

BUT: what happens when you have a price increase? If you change the
PriceEach in the Price table, all your current invoices will then be
incorrect! You need the PriceEach field in the InvoiceDetail table *as well*
as in the Price table. Price.PriceEach gives the current price.
InvoiceDetail.PriceEach gives the price at the time of that invoice.
Do you see that these are 2 different things?

So, add a PriceEach field to the InvoiceDetail table. In the subform where
you enter the invoice line items, you can use the AfterUpdate event of the
ProductID combo to DLookup() the current price for the CustomerID +
ProductID, and drop it into the subform for you automatically.

Now you have the PriceEach field in the query that feeds your report. So, in
the InvoiceID group footer section, you can put a text box with Control
Source:
=Sum([Quantity] * [PriceEach])

There may be other factors you need as well (such as tax), but that's the
concept.

 

[Tellu]

Dear, Allen,
Thanks a lot. This point of view was something I didn't think. I would have
an unpleasent surprise later without this "set value" -thing. Tables you
mentioned I had already, what I did, I put a Set value -macro to my ProductID
combo. It really looks up the price. And yes, I have another field too that
needs a lookup-value. But now I know how to handle this problem! Thanks again!
--
Thankful for Your help!


"Allen Browne" kirjoitti:

Tellu, I'm going go suggest that you solve this by redesigning the table.

From what you describe, different customsers have different prices for the
same product. Therefore I assume you have tables like this:
- Customer table, with CustomerID primary key
- Product table, with ProductID primary key
- Price table with fields:
CustomerID relates to Customer.CustomerID
ProductID relates to Product.ProductID
PriceEach Currency.
- Invoice table, with fields:
InvoiceID primary key
CustomerID relates to Customer.CustomerID
InvoiceDate Date/Time
- InvoiceDetail table (i.e. line items of an invoice), with fields:
InvoiceID relates to Invoice.InvoiceID
ProductID relates to Product.ProductID
Quantity Number

Now it seems that you are trying to DLookup() to get price, based on
CustomerID and ProductID.

BUT: what happens when you have a price increase? If you change the
PriceEach in the Price table, all your current invoices will then be
incorrect! You need the PriceEach field in the InvoiceDetail table *as well*
as in the Price table. Price.PriceEach gives the current price.
InvoiceDetail.PriceEach gives the price at the time of that invoice.
Do you see that these are 2 different things?

So, add a PriceEach field to the InvoiceDetail table. In the subform where
you enter the invoice line items, you can use the AfterUpdate event of the
ProductID combo to DLookup() the current price for the CustomerID +
ProductID, and drop it into the subform for you automatically.

Now you have the PriceEach field in the query that feeds your report. So, in
the InvoiceID group footer section, you can put a text box with Control
Source:
=Sum([Quantity] * [PriceEach])

There may be other factors you need as well (such as tax), but that's the
concept.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have Dlookup-function in report which lookups the price of a product
based
on customerID and productID. This works fine as far as it consers product
details. But this report is an invoice which should have a total sum also.
In
report I can't refer to the name of a calculated field in sum-fuction.
What
should I do. Do I have to put my dlookup to the query or...

Thankful for Your help!