Prices based on dates

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.

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



"Troutabout" wrote in message
...
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.

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 he
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Troutabout" wrote in message
...
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.

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

"Steve" wrote in message
m...
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



"Troutabout" wrote in message
...
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.

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, SaleDateStartDate AND
SaleDateEndDate)

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 he http://599cd.com/tips/access/dlookup...on/?key=usenet

Hope this helps.

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

"John... Visio MVP" wrote in message
...
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.

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

"Allen Browne" wrote:

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 he
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Troutabout" wrote in message
...
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.