If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Prices based on dates
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. |
#3
|
|||
|
|||
Prices based on dates
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. |
#4
|
|||
|
|||
Prices based on dates
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. |
#5
|
|||
|
|||
Prices based on dates
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 |
#6
|
|||
|
|||
Prices based on dates
"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. |
#7
|
|||
|
|||
Prices based on dates
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. |
Thread Tools | |
Display Modes | |
|
|