Prices based on dates

[Troutabout]

I want to get a price for a specific period based on a single date. My table
has Customer#, Effective Date, and Price. (This table has many dates in
ascending order, but different increments.) I want to be able to join this
table with another that has a Sales Date and Gallons in order to replicate
sales dollars for that date.

The logic is that the record would be the last effective period in the list
that the date is greater than.

How can I program this in MS Access using the typical tables. I am not
fluent in the SQL query, but willing to give it a try.

 

[Steve]

It seems like you need to look at the design of your tables first. From what
you have posted you need tables that look something like:
TblCustomer
CustomerID
CustomerNum
FirstName
LastName
etc

TblProduct
ProductID
ProductDesc
ProductPrice (per gallon)

TblSale
SaleID
CustomerID
SaleDate

TblSaleLineItem
SaleLineItemID
SaleID
ProductID
Gallons
ProductPrice

When you enter a line item sale in TblSaleLineItem, you record the current
ProductPrice. Doing this, the product price recorded in the database always
reflects the actual price at the time of the sale. You can then easily get
the sales dollars of any period by setting up appropriate criteria for
SaleDate and summing the ProductPrice time Gallons.

Steve
(e-mail address removed)

 

[Allen Browne]

The table structure you have is good. You need to then create a quiery that
gives you a calculated [EndDate] as well, so you can then get the [Price]
for the [Sales Date] between the [Effective Date] and the [EndDate].

Tom Ellison explains how to build such a query here:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

Alternatively, you could use a subquery:
(SELECT TOP 1 Price
FROM Table1 AS Dupe
WHERE Dupe.[Customer#] = Table1.[Customer#]
AND Dupe.[Effective Date] < Table2.[Sales Date]
ORDER BY Dupe.[Effective Date] DESC, Dupe.ID)

More about subqueries:
http://allenbrowne.com/subquery-01.html

 

[John... Visio MVP]

As usual, incomplete. You left out effective date.

Now that you have gone off and had your sulk, I hope you plan on trying to
be helpful rather than hassle posters for work.

John... Visio MVP

 

[Amicron]

This solution isn't as elegant as the one Allen suggested, but you
could also use DLOOKUP in a query to determine the correct price on
that date.

SpecificPrice: DLOOKUP(Price, MyTable, SaleDate>StartDate AND
SaleDate<EndDate)

It will run slower, but might be easier for you to code, and you can
easily throw more criteria in there if you need to (CustomerID, etc.)

I cover DLOOKUP here: http://599cd.com/tips/access/dlookup-function/?key=usenet

Hope this helps.

Richard Rost
http://www.AccessLearningZone.com?key=usenet

 

[Keith Wilby]

As usual, incomplete. You left out effective date.

Spot the incompetent:

1 - "It seems like you need to look at the design of your tables first."
2 - "The table structure you have is good."

I know which one my money's on.

 

[Troutabout]

Allen, thanks for the help. The access query I am using is linking to an
Oracle ODBC, so I am unable to change the tables. The insight you gave me
coupled with a collegue's help solved the case.

In essence, I joined the sales item table(one) with the rate tables(many)
deduced the effective date ranges which were less than the sales dates and
returned the max of those items. (All of this is done in the typical query
view in Access, so to support future viewers who do not know SQL.)

I appreciate your help and your website.

Randy

The table structure you have is good. You need to then create a quiery that
gives you a calculated [EndDate] as well, so you can then get the [Price]
for the [Sales Date] between the [Effective Date] and the [EndDate].

Tom Ellison explains how to build such a query here:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

Alternatively, you could use a subquery:
(SELECT TOP 1 Price
FROM Table1 AS Dupe
WHERE Dupe.[Customer#] = Table1.[Customer#]
AND Dupe.[Effective Date] < Table2.[Sales Date]
ORDER BY Dupe.[Effective Date] DESC, Dupe.ID)

More about subqueries:
http://allenbrowne.com/subquery-01.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I want to get a price for a specific period based on a single date. My
table
has Customer#, Effective Date, and Price. (This table has many dates in
ascending order, but different increments.) I want to be able to join
this
table with another that has a Sales Date and Gallons in order to replicate
sales dollars for that date.

The logic is that the record would be the last effective period in the
list
that the date is greater than.

How can I program this in MS Access using the typical tables. I am not
fluent in the SQL query, but willing to give it a try.