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

On Thu, 9 Mar 2006 21:30:13 -0800, john431
<(E-Mail Removed)> wrote:

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

> On Thu, 9 Mar 2006 21:30:13 -0800, john431
> <(E-Mail Removed)> wrote:
>
> >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]
>

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

"John Vinson" wrote:

> On Thu, 9 Mar 2006 21:30:13 -0800, john431
> <(E-Mail Removed)> wrote:
>
> >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]
>

On Fri, 10 Mar 2006 21:46:27 -0800, john431
<(E-Mail Removed)> wrote:

>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]

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

"John Vinson" wrote:

> On Fri, 10 Mar 2006 21:46:27 -0800, john431
> <(E-Mail Removed)> wrote:
>
> >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]
>

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


"john431" <(E-Mail Removed)> wrote in message
news:26E87BA2-13A1-4CF4-A437-(E-Mail Removed)...
> 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
>
> "John Vinson" wrote:
>
>> On Fri, 10 Mar 2006 21:46:27 -0800, john431
>> <(E-Mail Removed)> wrote:
>>
>> >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,

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

Tom Ellison" wrote:

> 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
>
>
> "john431" <(E-Mail Removed)> wrote in message
> news:26E87BA2-13A1-4CF4-A437-(E-Mail Removed)...
> > 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
> >
> > "John Vinson" wrote:
> >
> >> On Fri, 10 Mar 2006 21:46:27 -0800, john431
> >> <(E-Mail Removed)> wrote:
> >>
> >> >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]
> >>
>
>
>

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


"john431" <(E-Mail Removed)> wrote in message
news:10054A25-F578-4882-8DFD-(E-Mail Removed)...
> 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
>
> Tom Ellison" wrote:
>
>> 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
>>
>>
>> "john431" <(E-Mail Removed)> wrote in message
>> news:26E87BA2-13A1-4CF4-A437-(E-Mail Removed)...
>> > 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
>> >
>> > "John Vinson" wrote:
>> >
>> >> On Fri, 10 Mar 2006 21:46:27 -0800, john431
>> >> <(E-Mail Removed)> wrote:
>> >>
>> >> >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]
>> >>
>>
>>
>>

On Sat, 11 Mar 2006 20:54:24 -0800, john431
<(E-Mail Removed)> wrote:

>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]