By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,115 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,115 IT Pros & Developers. It's quick & easy.

Need a Query to Run X times for a date range

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Purchases Order].ProductID, Max([Purchases Order].PurchaseDate) AS PurchaseDateOfMax, Last([Purchases Order].UnitCost) AS CostOfLast
  2. FROM [Purchases Order]
  3. WHERE ((([Purchases Order].PurchaseDate)<[Date]))
  4. 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
May 9 '09 #1
Share this Question
Share on Google+
12 Replies


Denburt
Expert 100+
P: 1,356
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Purchases Order].ProductID, [Purchases Order].PurchaseDate, [Purchases Order].UnitCost
  2. FROM [Purchases Order]
  3. WHERE ((([Purchases Order].PurchaseDate) Between #5/1/2009# and 5/31/2009))
May 10 '09 #2

FishVal
Expert 2.5K+
P: 2,653
What about grouping results by [ProductID] and [PurchaseDate]?

Regards,
Fish.
May 10 '09 #3

P: 4
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.
May 10 '09 #4

NeoPa
Expert Mod 15k+
P: 31,419
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?
May 11 '09 #5

Denburt
Expert 100+
P: 1,356
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Purchases Order].ProductID, [Purchases Order].PurchaseDate, [Purchases Order].UnitCost
  2.  FROM [Purchases Order]
  3.  WHERE ((([Purchases Order].PurchaseDate) > #5/1/2009# and ([Purchases Order].PurchaseDate) <(Select Max(SalesDate) From Sales Where sales Order=1)))
May 11 '09 #6

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Sales Order].OrderDate, Customers.CompanyName, [Sales Order].QuantitySold, [Sales Order].UnitPrice, Last([Purchases Order].UnitCost) AS UnitCostOfLast, Max([Purchases Order].PurchaseDate) AS PurchaseDateOfMax
  2. FROM Customers INNER JOIN ([Sales Order] INNER JOIN [Purchases Order] ON [Sales Order].ProductID = [Purchases Order].ProductID) ON Customers.CustomerID = [Sales Order].CustomerID
  3. WHERE ((([Purchases Order].PurchaseDate)<[OrderDate]))
  4. GROUP BY [Sales Order].OrderDate, Customers.CompanyName, [Sales Order].QuantitySold, [Sales Order].UnitPrice
  5. HAVING ((([Sales Order].OrderDate) Between [Start Date] And [End Date]));
  6.  
May 11 '09 #7

Denburt
Expert 100+
P: 1,356
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:
Expand|Select|Wrap|Line Numbers
  1.     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
  2.     FROM Customers INNER JOIN ([Sales Order] INNER JOIN [Purchases Order] ON [Sales Order].ProductID = [Purchases Order].ProductID) ON Customers.CustomerID = [Sales Order].CustomerID
  3.     WHERE ((([Purchases Order].PurchaseDate)<[OrderDate]))
  4.     GROUP BY [Sales Order].OrderDate, Customers.CompanyName, [Sales Order].QuantitySold, [Sales Order].UnitPrice
  5.   HAVING ((([Sales Order].OrderDate) Between [Start Date] And [End Date]));
May 11 '09 #8

NeoPa
Expert Mod 15k+
P: 31,419
@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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Sales Order].OrderDate,
  2.        Customers.CompanyName,
  3.        [Sales Order].QuantitySold,
  4.        [Sales Order].UnitPrice,
  5.        Val(Mid(Max(Format([Purchases Order].PurchaseDate,'yyyymmdd') &
  6.                    [Purchases Order].UnitCost),9,99)) AS LastUnitCost,
  7.        Max([Purchases Order].PurchaseDate) AS MaxOfPurchaseDate
May 11 '09 #9

P: 4
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!
May 12 '09 #10

NeoPa
Expert Mod 15k+
P: 31,419
Pleased to have helped JamieBoy :)

Welcome to Bytes!
May 12 '09 #11

Denburt
Expert 100+
P: 1,356
Glad we could help jamieboy. Interesting solution NeoPa nice work.
May 12 '09 #12

NeoPa
Expert Mod 15k+
P: 31,419
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.
May 12 '09 #13

Post your reply

Sign in to post your reply or Sign up for a free account.