Access equivalent to Excel's VLookup function

[Guest]

I’m looking for the equivalent in Access of the VLookup function in Excel,
when the lookup value is between values in the value lookup column. I want to
print a report that prints the cost of various purchased products. A query
of [quantity] * [price] for each product is simple enough but the problem is
that the price changes with time and I want the total value based on the
different prices for that product over time. So, I am thinking of generating
a table (Table 1) of Product, Date of Price Change, and Price. Now I want
the query to have a field that looks up the product’s price based on the date
purchased. So, with Product, Quantity, Date Purchased, and this look up
price as fields, I can complete the query. But the Date Purchased will most
likely fall in between values of the Date of Price Change field in Table 1.
So this is where the Access equivalent of the Excel VLookup function would be
nice to have. Can anyone think of a way to accomplish this in Access?

Thanks,
John

 

[John Vinson]

I’m looking for the equivalent in Access of the VLookup function in Excel,
when the lookup value is between values in the value lookup column. I want to
print a report that prints the cost of various purchased products. A query
of [quantity] * [price] for each product is simple enough but the problem is
that the price changes with time and I want the total value based on the
different prices for that product over time. So, I am thinking of generating
a table (Table 1) of Product, Date of Price Change, and Price. Now I want
the query to have a field that looks up the product’s price based on the date
purchased. So, with Product, Quantity, Date Purchased, and this look up
price as fields, I can complete the query. But the Date Purchased will most
likely fall in between values of the Date of Price Change field in Table 1.
So this is where the Access equivalent of the Excel VLookup function would be
nice to have. Can anyone think of a way to accomplish this in Access?

Thanks,
John

It's perfectly routine, and there are several ways to do it. DLookUp()
is one way - it's not the same as vlookup but it's similar.

However, the better way to do this is with a Query joining your
historical price table to the purchase table, using criteria on the
date of purchase to select the appropriate price change record.

John W. Vinson[MVP]

 

[Guest]

I’m looking for the equivalent in Access of the VLookup function in Excel,
when the lookup value is between values in the value lookup column. I want to
print a report that prints the cost of various purchased products. A query
of [quantity] * [price] for each product is simple enough but the problem is
that the price changes with time and I want the total value based on the
different prices for that product over time. So, I am thinking of generating
a table (Table 1) of Product, Date of Price Change, and Price. Now I want
the query to have a field that looks up the product’s price based on the date
purchased. So, with Product, Quantity, Date Purchased, and this look up
price as fields, I can complete the query. But the Date Purchased will most
likely fall in between values of the Date of Price Change field in Table 1.
So this is where the Access equivalent of the Excel VLookup function would be
nice to have. Can anyone think of a way to accomplish this in Access?

Thanks,
John

It's perfectly routine, and there are several ways to do it. DLookUp()
is one way - it's not the same as vlookup but it's similar.

However, the better way to do this is with a Query joining your
historical price table to the purchase table, using criteria on the
date of purchase to select the appropriate price change record.

John W. Vinson[MVP]

 

[Guest]

Thanks for your reply, John. I used the Query approach as you suggested but
the problem I am having is writing the expression that only selects the most
recent Date of Price Change and Price record prior to the Date Purchased.
The expression "<[Date Purchased]" for the criteria of the Date of Price
Change field, supplies all the Table 1 records for each Product, not just the
latest one with the Price value I need.

I am puzzled as to how to write this expression to retrieve just the one
record in Table 1 with the most current Date of Price Change and its
associated price.

John

I’m looking for the equivalent in Access of the VLookup function in Excel,
when the lookup value is between values in the value lookup column. I want to
print a report that prints the cost of various purchased products. A query
of [quantity] * [price] for each product is simple enough but the problem is
that the price changes with time and I want the total value based on the
different prices for that product over time. So, I am thinking of generating
a table (Table 1) of Product, Date of Price Change, and Price. Now I want
the query to have a field that looks up the product’s price based on the date
purchased. So, with Product, Quantity, Date Purchased, and this look up
price as fields, I can complete the query. But the Date Purchased will most
likely fall in between values of the Date of Price Change field in Table 1.
So this is where the Access equivalent of the Excel VLookup function would be
nice to have. Can anyone think of a way to accomplish this in Access?

Thanks,
John

It's perfectly routine, and there are several ways to do it. DLookUp()
is one way - it's not the same as vlookup but it's similar.

However, the better way to do this is with a Query joining your
historical price table to the purchase table, using criteria on the
date of purchase to select the appropriate price change record.

John W. Vinson[MVP]

 

[John Vinson]

I am puzzled as to how to write this expression to retrieve just the one
record in Table 1 with the most current Date of Price Change and its
associated price.

Use a criterion something like

=DMax("[Date Of Price Change]", "[PriceTable]", "[ItemID] = " &
[ItemID])

John W. Vinson[MVP]

 

[Guest]

Thank you for your reply, John.

I tried the DMax criteria for the Price field, which is the value I am
looking for. I used =DMax("[Price]", "[Price Table]", "[ItemID]="&[ItemID]).
This gave me the maximum value of the price for all cases of the Purchase
Date. For example, if the price table for a product is:

Date of Price Change Price
6/5/05 $ 0.85
11/1/05 0.99
1/5/06 1.10

and the Purchase Date is between the two dates, say, 12/31/05, the correct
price is $0.99, not the $1.10 value that the DMax function returned. The
Excel VLookup function would return the correct value. A criteria is
required that only returns the Price for the latest Date of Price Change
which is less than or equal to the Purchase Date.

Any suggestions would be appreciated very much.

John

I am puzzled as to how to write this expression to retrieve just the one
record in Table 1 with the most current Date of Price Change and its
associated price.

Use a criterion something like

=DMax("[Date Of Price Change]", "[PriceTable]", "[ItemID] = " &
[ItemID])

John W. Vinson[MVP]

 

[Tom Ellison]

Dear John:

I'm assuming you have a table of "sales" for which you want to look up the
prices. This table might have:

DateOfSale
ItemID
Quantity

I will assume this is in a table called Sale.

SELECT S.DateOfSale, S.ItemID, S.Quantity,
(SELECT T.Price
FROM [Price Table] T
WHERE T.ItemID = S.ItemID
AND T.[Date of Price Change] =
(SELECT MAX([Date of Price Change])
FROM [Price Table] T1
WHERE T1.ItemID = S.ItemID
AND T1.[Date of Price Change] <= S.DateOfSale))
AS Price
FROM Sale S

Without some idea of what items and dates you have sales, the problem does
not make sense. That's why I have invented a table to contain these.

I have also assumed your [Price Table] would have a column for ItemID in it.
Surely you must specify a price change for each individual item, right?

The requires a 2 level correlated subquery. Jet often does not work
properly for this. Go figure! Anyway, good luck, and good hunting!

Tom Ellison

Thank you for your reply, John.

I tried the DMax criteria for the Price field, which is the value I am
looking for. I used =DMax("[Price]", "[Price Table]",
"[ItemID]="&[ItemID]).
This gave me the maximum value of the price for all cases of the Purchase
Date. For example, if the price table for a product is:

Date of Price Change Price
6/5/05 $ 0.85
11/1/05 0.99
1/5/06 1.10

and the Purchase Date is between the two dates, say, 12/31/05, the
correct
price is $0.99, not the $1.10 value that the DMax function returned. The
Excel VLookup function would return the correct value. A criteria is
required that only returns the Price for the latest Date of Price Change
which is less than or equal to the Purchase Date.

Any suggestions would be appreciated very much.

John

I am puzzled as to how to write this expression to retrieve just the one
record in Table 1 with the most current Date of Price Change and its
associated price.

Use a criterion something like

=DMax("[Date Of Price Change]", "[PriceTable]", "[ItemID] = " &
[ItemID])

John W. Vinson[MVP]

 

[Guest]

Tom,

You are correct in your assumptions, but from your reply it appears I am not
on the right track to solve this problem. Are there any other alternatives?
Could a user defined function in VBA code provide the same capabilities as
the Excel VLookup function? If so, does anyone know of any available code
for this? Or, is there a way for Access to link to Excel, perform the
VLookup function there, and import the values back to Access?

John

Dear John:

I'm assuming you have a table of "sales" for which you want to look up the
prices. This table might have:

DateOfSale
ItemID
Quantity

I will assume this is in a table called Sale.

SELECT S.DateOfSale, S.ItemID, S.Quantity,
(SELECT T.Price
FROM [Price Table] T
WHERE T.ItemID = S.ItemID
AND T.[Date of Price Change] =
(SELECT MAX([Date of Price Change])
FROM [Price Table] T1
WHERE T1.ItemID = S.ItemID
AND T1.[Date of Price Change] <= S.DateOfSale))
AS Price
FROM Sale S

Without some idea of what items and dates you have sales, the problem does
not make sense. That's why I have invented a table to contain these.

I have also assumed your [Price Table] would have a column for ItemID in it.
Surely you must specify a price change for each individual item, right?

The requires a 2 level correlated subquery. Jet often does not work
properly for this. Go figure! Anyway, good luck, and good hunting!

Tom Ellison

Thank you for your reply, John.

I tried the DMax criteria for the Price field, which is the value I am
looking for. I used =DMax("[Price]", "[Price Table]",
"[ItemID]="&[ItemID]).
This gave me the maximum value of the price for all cases of the Purchase
Date. For example, if the price table for a product is:

Date of Price Change Price
6/5/05 $ 0.85
11/1/05 0.99
1/5/06 1.10

and the Purchase Date is between the two dates, say, 12/31/05, the
correct
price is $0.99, not the $1.10 value that the DMax function returned. The
Excel VLookup function would return the correct value. A criteria is
required that only returns the Price for the latest Date of Price Change
which is less than or equal to the Purchase Date.

Any suggestions would be appreciated very much.

John

On Fri, 10 Mar 2006 21:46:27 -0800, john431

I am puzzled as to how to write this expression to retrieve just the one
record in Table 1 with the most current Date of Price Change and its
associated price.

Use a criterion something like

=DMax("[Date Of Price Change]", "[PriceTable]", "[ItemID] = " &
[ItemID])

John W. Vinson[MVP]

 

[Tom Ellison]

Dear John:

I do not believe the Excel VLookup will be able to act on a database.

Writing this in VBA would be somewhat time consuming and would not be very
flexible in meeting other similar challenges. I also doubt the VBA approach
would perform well. Finally, the VBA approach would likely query the
database to provide the same results, so you would be learning and using
query technology anyway.

Queries are the acknowledged way of getting things done in a database. The
position of queries in ease of writing and speed of performance is not
approached by other methods. It would seem you prefer other approaches,
probably out of familiarity. I can sympathize with that. Many of us
programmed in Fortran, Basic, C, and many other languages for years before
being introduced to SQL. But, to do what SQL does, there's not a
substitute. I believe you should figure on learning it if you wish to
progress.

Tom Ellison

Tom,

You are correct in your assumptions, but from your reply it appears I am
not
on the right track to solve this problem. Are there any other
alternatives?
Could a user defined function in VBA code provide the same capabilities as
the Excel VLookup function? If so, does anyone know of any available code
for this? Or, is there a way for Access to link to Excel, perform the
VLookup function there, and import the values back to Access?

John

Dear John:

I'm assuming you have a table of "sales" for which you want to look up
the
prices. This table might have:

DateOfSale
ItemID
Quantity

I will assume this is in a table called Sale.

SELECT S.DateOfSale, S.ItemID, S.Quantity,
(SELECT T.Price
FROM [Price Table] T
WHERE T.ItemID = S.ItemID
AND T.[Date of Price Change] =
(SELECT MAX([Date of Price Change])
FROM [Price Table] T1
WHERE T1.ItemID = S.ItemID
AND T1.[Date of Price Change] <= S.DateOfSale))
AS Price
FROM Sale S

Without some idea of what items and dates you have sales, the problem
does
not make sense. That's why I have invented a table to contain these.

I have also assumed your [Price Table] would have a column for ItemID in
it.
Surely you must specify a price change for each individual item, right?

The requires a 2 level correlated subquery. Jet often does not work
properly for this. Go figure! Anyway, good luck, and good hunting!

Tom Ellison

Thank you for your reply, John.

I tried the DMax criteria for the Price field, which is the value I am
looking for. I used =DMax("[Price]", "[Price Table]",
"[ItemID]="&[ItemID]).
This gave me the maximum value of the price for all cases of the
Purchase
Date. For example, if the price table for a product is:

Date of Price Change Price
6/5/05 $ 0.85
11/1/05 0.99
1/5/06 1.10

and the Purchase Date is between the two dates, say, 12/31/05, the
correct
price is $0.99, not the $1.10 value that the DMax function returned.
The
Excel VLookup function would return the correct value. A criteria is
required that only returns the Price for the latest Date of Price
Change
which is less than or equal to the Purchase Date.

Any suggestions would be appreciated very much.

John

:

On Fri, 10 Mar 2006 21:46:27 -0800, john431

I am puzzled as to how to write this expression to retrieve just the
one
record in Table 1 with the most current Date of Price Change and its
associated price.

Use a criterion something like

=DMax("[Date Of Price Change]", "[PriceTable]", "[ItemID] = " &
[ItemID])

John W. Vinson[MVP]

 

[John Vinson]

Thank you for your reply, John.

I tried the DMax criteria for the Price field, which is the value I am
looking for. I used =DMax("[Price]", "[Price Table]", "[ItemID]="&[ItemID]).
This gave me the maximum value of the price for all cases of the Purchase
Date. For example, if the price table for a product is:

To get the most recent price using domain functions, try

=DLookUp("[Price]", "[Price Table]", "[ItemID] = " & [ItemID] & " AND
[Date Of Price Change] = #" & DMax("[Date Of Price Change]", "[Price
Table]", "[ItemID] = " & [ItemID]))

Or... much more efficient - use a Query. Just because VLookUp is the
typical solution in Excel doesn't mean that there is a "VLookUp
Equivalent" in Access. Excel is a spreadsheet; Access is a relational
database! They have different structures, different techniques, and
require different thinking.

John W. Vinson[MVP]

 

[Guest]

Tom & John,

Thanks very much for your help on this matter.

Tom, I am taking your advice on learning SQL. I tried your SQL construct
and it works! I simplified the problem, however. Both tables are linked to
another table. For example, the price table is actually Price TableA with
fields of ItemID, Attribute #1, Attibrute #2, etc. and links with Price
TableB with fields of ItemID (the link), Date of Price Change, and Price.
That is, looking at the Price TableA with Price TableB as a subtable shows
the price history for each item. How does this affect the SQL statement you
sent me? Hope it doesn't complicate it too much.

John, I will try your lookup expression and compare the results.

Again, thanks to both of you for your help.

John

Thank you for your reply, John.

I tried the DMax criteria for the Price field, which is the value I am
looking for. I used =DMax("[Price]", "[Price Table]", "[ItemID]="&[ItemID]).
This gave me the maximum value of the price for all cases of the Purchase
Date. For example, if the price table for a product is:

To get the most recent price using domain functions, try

=DLookUp("[Price]", "[Price Table]", "[ItemID] = " & [ItemID] & " AND
[Date Of Price Change] = #" & DMax("[Date Of Price Change]", "[Price
Table]", "[ItemID] = " & [ItemID]))

Or... much more efficient - use a Query. Just because VLookUp is the
typical solution in Excel doesn't mean that there is a "VLookUp
Equivalent" in Access. Excel is a spreadsheet; Access is a relational
database! They have different structures, different techniques, and
require different thinking.

John W. Vinson[MVP]