Apply SELECT to all records

[2077delta]

I have the following SELECT statement:

SELECT TOP 1 tblSalesHistory.[Product Code], tblSalesHistory.Month, tblSalesHistory.AvgSalesPrice
FROM tblSalesHistory
WHERE (((tblSalesHistory.[Product Code])="t0371") AND ((tblSalesHistory.Month)<=[Test Month]))
ORDER BY tblSalesHistory.Month DESC;

The statement works perfectly when I'm looking for only one product code, but when I want to return all product codes in the table it only returns the products that have a date matching the "Test Month". The table has a listing of product codes with the month and average sales price, but not all products are listed each month. I'm trying to find a listing of the the most current sales price for EVERY product as of a certain date ("Test Month"). The only way a product would not appear on the is if it doesn't have a sales price listed for a month prior to the "Test Month". I would like the output to include the product code, the sales price and the month applicable to the sales price. I've tried DLOOKUP and DMAX but to no avail. I've posted this question on several other sites with no response, but I believe the gurus on this site will have an answer.

Thanks in advance.

 

[NateO]

Hello again Delta, try the following:

SELECT tblSalesHistory.[Product Code], tblSalesHistory.Month, tblSalesHistory.AvgSalesPrice
FROM tblSalesHistory
WHERE tblSalesHistory.Month<#4/1/2003# And (tblSalesHistory.Month)=(Select Max(Month) from tblSalesHistory as myVar
    Where  myVar.[Product Code] = tblSalesHistory.[Product Code])
ORDER BY tblSalesHistory.[Product Code];

Or

SELECT tblSalesHistory.[Product Code], tblSalesHistory.Month, tblSalesHistory.AvgSalesPrice
FROM tblSalesHistory
WHERE tblSalesHistory.Month < [Pick a Date] And (tblSalesHistory.Month)=(Select Max(Month) from tblSalesHistory as myVar
    Where myVar.[Product Code] = tblSalesHistory.[Product Code])
ORDER BY tblSalesHistory.[Product Code];

Worked fine for me on your data. Still, not loving Access.

 

[2077delta]

Nate, you may hate Access but you know how to make it work. This is oooh so close. I did notice that doesn't always return all the records I want. When I provide the date parameter, [Pick a date], it returns only the products whose maximum date is less than the parameter. What I actually want is the maximum date (not greater than [Pick a date]) for all products that have a date in the table less than [Pick a date]. For example if product T0037 had 03/31/03, 02/28/03, 12/31/02, 11/30/02 and 09/30/02 as its dates, if [Pad] was 03/31/02 it would return the sales price for 02/28/03, if [Pad] was 02/28/02 it would return 12/31/02, if [Pad] was 12/31/02 it would return 11/30/02 and so on. Currently, if I were to use 02/28/03 for [Pad] it would not return a record for product T0037 because the max date is greater than 02/28/03.

 

[2077delta]

I did get the query to work by a query that returns only the records from tblSalesHistory with a date <= [Pick a Date], I'm using the parameter [LCM Date] in place of [Pick a date]. Here is the code for this query

SELECT tblSalesHistory.[Product Code], tblSalesHistory.Month, tblSalesHistory.AvgSalesPrice
FROM tblSalesHistory
WHERE (((tblSalesHistory.Month)<=[LCM Date]))
ORDER BY tblSalesHistory.[Product Code], tblSalesHistory.Month;

and here is the code from the query using the records from qryASP1

SELECT qryASP1.[Product Code], qryASP1.Month, qryASP1.AvgSalesPrice
FROM qryASP1
WHERE (((qryASP1.Month)<=[LCM Date] And (qryASP1.Month)=(Select Max(Month) from qryASP1 as myVar
Where myVar.[Product Code] = qryASP1.[Product Code])));

The only problem I have with this solution is it is ungodly slow. Is there some way to combine these into one query?

Once again, thanks for all your help.

 

[NateO]

You're welcome,

I actually like Access, I just feel like she's fighting me all the way most of the time.

I might if I could discern what you're asking. Isn't the second one simply a more refined (and expensive, two queries on one) version of the first. How would you combine them (what would the result be)? I thought they were both quick on my PC, like ~1 second on your data...

 

[2077delta]

There is a very subtle difference between the two solutions. Your solution selects only products whose MAX date is less than [Pick a date] then selects the MAX date and corresponding sales price from that set of data. My solution picks all products that have a sales price month equal to or less than [Pick a date] then returns the MAX date for each product in THAT group. Here is the test. Modify your code to look for a month <= to [Pick a date] then look for product code 37653. If you run your solution it only returns this product when [Pick a date] is >=03/29/03. My solution, albeit a slow one, returns this product code with [Pick a date] being as far back as 01/26/02 because I have a sales price dated 01/26/02 in the table for this product. If I could build the qryASP1 into your code it should generate the result I'm looking for, only I'm not sure how/where to put it.

 

[NateO]

Thanks (I think ), as I wasn't following you, think I am now.

See if the following:

1) Works, I think it does (worked on 37653 when 2/1/2002 entered)
2) Is faster, it's pretty chop-chop on my machine.

SELECT tblSalesHistory.[Product Code], tblSalesHistory.Month, tblSalesHistory.AvgSalesPrice
FROM tblSalesHistory
where tblSalesHistory.Month=(Select Max(Month) from tblSalesHistory as myVar     
      Where myVar.[Product Code] = tblSalesHistory.[Product Code]
         Having myVar.Month <= [Pick a Date])
ORDER BY tblSalesHistory.[Product Code];

If so you owe me a beer. Just kidding, have a nice evening. I hate Access.

 

[2077delta]

If I were to believe the picture, I would guess your to young for that beverage, but if I'm guessing right, I would say it's more along the line of a six pack and a couple of fine cigars. Is there a way to recognize you and/or anyone else on the board who goes out of their way to help us less fortunates? Thanks for all the help.

 

[NateO]

If I were to believe the picture, I would guess your to young for that beverage, but if I'm guessing right, I would say it's more along the line of a six pack and a couple of fine cigars. Is there a way to recognize you and/or anyone else on the board who goes out of their way to help us less fortunates? Thanks for all the help.

You're welcome and right.

Just kidding (again). Well, thanks is great, enough recognition for me. I wanted to be of help and glad I could be.

Also Access is a great choice to do something like this, Excel would really lumber with your task, so I say I hate it in jest, it's a good piece of software for a lot of tasks including this one.