Need a Query to Run X times for a date range

[jamieboy86]

Hi Everyone, I'm new here and to access as well,

The problem I'm having right now is that I made a Query that upon the input of a date, it will search for the last purchase I made and at what cost.

SELECT [Purchases Order].ProductID, Max([Purchases Order].PurchaseDate) AS PurchaseDateOfMax, Last([Purchases Order].UnitCost) AS CostOfLast
FROM [Purchases Order]
WHERE ((([Purchases Order].PurchaseDate)<[Date]))
GROUP BY [Purchases Order].ProductID;

That works fine, but I want it to work for over a date range between [start date] and [end date]. With this current query it will always just give me the latest purchase with [end date] as frame of reference.

What I would like to do is to have it run for each day over that date range. If it's between 5/1 and 5/3, it'll run it 3 times at 5/1 5/2 and 5/3, and display these values.

Any ideas? Will this require programming?

Thanks in Advance

[Denburt]

If you want it to run three times then yes I could see some coding involded.

May I suggest dropping the Totals and use a basic sql select statement:

SELECT [Purchases Order].ProductID, [Purchases Order].PurchaseDate, [Purchases Order].UnitCost
FROM [Purchases Order]
WHERE ((([Purchases Order].PurchaseDate) Between #5/1/2009# and 5/31/2009))

[FishVal]

What about grouping results by [ProductID] and [PurchaseDate]?

Regards,
Fish.

[jamieboy86]

OK thanks for the tips. Using that query, I'm able to list all the purchases I made for a range of dates.

The problem I face now is when i put together another query to match up my sales price and purchase price, it lists out every single combination.

For Ex)
Purchases made on 5/1 5/3 5/7
Sales order #1 on 5/6
Sales order #2 on 5/8

my query results give me 3 entries each for sales order #1 and #2, doing all the combinations.

The result I want, however, is to match up the sales order with ONLY the last purchase date before my sales date:

Sales order #1 with 5/3 purchase
Sales order #2 with 5/7 purchase

Essentially, what i want is the LIFO accounting method.

Thanks again.

[NeoPa]

So, you want to look backwards from a set date, to the previous transaction for a product. You also want to repeat this for successive dates in a range.

Will it not be likely that, for any given product, the date may be the same for each repetition? If the last date was not within the range of dates being run for, this will be the case. Is this what you require?

[Denburt]

What are you using as a reference between the Purchases and sales? Would this be the productID a Customer or some other reference?

There are lots of ways to accomplish this but I am just gonna through this one out there it may not be perfect it's just freehand and untested:

SELECT [Purchases Order].ProductID, [Purchases Order].PurchaseDate, [Purchases Order].UnitCost
 FROM [Purchases Order]
 WHERE ((([Purchases Order].PurchaseDate) > #5/1/2009# and ([Purchases Order].PurchaseDate) <(Select Max(SalesDate) From Sales Where sales Order=1)))

[jamieboy86]

Thanks to you guys I've got it to work finally. But there's one last tweak i was hoping you guys might be able to help. This query comes out perfectly provided that I always enter my purchase order in order ( 5/2 5/3 5/4). If I happen to "forget" to record a purchase order from a few days ago(5/1), it all unwinds.

Now when this happens for a sales order (eg 5/5):
My PurchaseDateOfMax still correctly displays the most recent purchase date (5/4), however my price because it is a LAST function, I realized that it is the LAST recorded entry, and it will incorrectly provide me with the LAST recorded entry cost which will be (5/1 entry) and not (5/3 entry).

Is there a way to not use the LAST function for price but instead make it search for the corresponding field value in the record entry of the PurchaseDateOfMax?
I've tried just simply using GROUP BY, but then it just lists all the combinations (cost at 5/1 5/2 5/3 5/4) with that sales order.

SELECT [Sales Order].OrderDate, Customers.CompanyName, [Sales Order].QuantitySold, [Sales Order].UnitPrice, Last([Purchases Order].UnitCost) AS UnitCostOfLast, Max([Purchases Order].PurchaseDate) AS PurchaseDateOfMax
FROM Customers INNER JOIN ([Sales Order] INNER JOIN [Purchases Order] ON [Sales Order].ProductID = [Purchases Order].ProductID) ON Customers.CustomerID = [Sales Order].CustomerID
WHERE ((([Purchases Order].PurchaseDate)<[OrderDate]))
GROUP BY [Sales Order].OrderDate, Customers.CompanyName, [Sales Order].QuantitySold, [Sales Order].UnitPrice
HAVING ((([Sales Order].OrderDate) Between [Start Date] And [End Date]));
 

[Denburt]

Glad that we could be of help now lets see if we can help with this next part.

Yes it does look like your getting there. One way would be to leave out the "purchase order unit cost" in this query create a new query based on this one and add the purchase order table again, then join the two on the appropriate joins (Dates, customer etc.) and you should have the results you require.

Another method would achieve the same results although MS Access doesn't always like nested SQL statements again this is typed freehand and not tested but should give you the results required:

    SELECT [Sales Order].OrderDate, Customers.CompanyName, [Sales Order].QuantitySold, [Sales Order].UnitPrice, (Select ([Purchases Order].UnitCost) From [Purchases Order] Where ((PurchaseDate =#PurchaseDateOfMax#) and ([Purchases Order].CustomerID = Customers.CustomerID)) AS UnitCostOfLast, Max([Purchases Order].PurchaseDate) AS PurchaseDateOfMax
    FROM Customers INNER JOIN ([Sales Order] INNER JOIN [Purchases Order] ON [Sales Order].ProductID = [Purchases Order].ProductID) ON Customers.CustomerID = [Sales Order].CustomerID
    WHERE ((([Purchases Order].PurchaseDate)<[OrderDate]))
    GROUP BY [Sales Order].OrderDate, Customers.CompanyName, [Sales Order].QuantitySold, [Sales Order].UnitPrice
  HAVING ((([Sales Order].OrderDate) Between [Start Date] And [End Date]));

[NeoPa]

@jamieboy86
It's certainly about using Last() instead of Max().

One way to use Max() in this scenario, when you want a value in a record associated with the Max value of another field (as in this case), is to join the fields together in a string, call Max() for this value, then extract the required data out of the resultant max value, and convert it into its original format (date, number, etc). You need, for this to work, to understand the data you're working with :

SELECT [Sales Order].OrderDate,
       Customers.CompanyName,
       [Sales Order].QuantitySold,
       [Sales Order].UnitPrice,
       Val(Mid(Max(Format([Purchases Order].PurchaseDate,'yyyymmdd') &
                   [Purchases Order].UnitCost),9,99)) AS LastUnitCost,
       Max([Purchases Order].PurchaseDate) AS MaxOfPurchaseDate

[jamieboy86]

Thank you Denburt and NeoPa. Your suggestions worked beautifully. Finally I can move past this problem that I've been stuck on for the past week!

Cheers!

[NeoPa]

Pleased to have helped JamieBoy :)

Welcome to Bytes!

[Denburt]

Glad we could help jamieboy. Interesting solution NeoPa nice work.

[NeoPa]

Thanks Den.

I use it a lot, but it is limited to whatever can be contained within a string value. I don't find too many problems mind you, but it's still a bit of a kludge I know.