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]

At the risk of being wrong, don't you just want a max, i.e.,

SELECT [Product Code], max( [tblSalesHistory. Month]), AvgSalesPrice
FROM tblSalesHistory
WHERE ((([Product Code])="t0371"))
ORDER BY tblSalesHistory.Month DESC

*Air code*

 

[2077delta]

I've tried your code and got an error message that the query does not include "Product Code" as part of an aggregrate function. I've tried the max function and it works fine when I want one product, but when I want all products it doesn't work properly. This particular table lists approximately 500 productes with their average sales price for each of the last 18 months. The sales prices vary from month to month and not every product is sold every month so not every product has a price listed for every month. What I want to see is a listing of every product with its most current sales price as of lets say March 31, 2003. If a product does not have a sales price in the table for March 31, 2003, but does have a sales price for a month prior to that, it would list the products' price from the most recent month prior to March 31, 2003 along with the applicable month. If you would like me to email the table ( it has approximately 4,700 records) for you to look at, please let me know. If you can solve this, I would be greatly indebted.

Thanks for all your help.

 

[NateO]

Don't you want to remove the following criteria?

WHERE ((([Product Code])="t0371"))

 

[2077delta]

Yes, there would be no criteria for Product Code.

 

[NateO]

More air code:


SELECT [Product Code], Max([tblSalesHistory. Month]) as [Month], AvgSalesPrice
FROM tblSalesHistory
ORDER BY tblSalesHistory.Month DESC;

If this doesn't work, send me the table:

Nate@NotAtTheOfficeexperts.com

Remove NotAt.

 

[2077delta]

I tried sending the file and twice receeived fatal errors indication that the host was unknown. Do you has an alternate address or is your mail server current out of commission?

 

[NateO]

Did you remove the first 5 characters from the following string:

NotAtTheOfficeexperts.com

? Me no want no spam, Daddy no like.

 

[NateO]

Hello again, yes, I was indeed outside of the aggregate function, how naughty of me. Please try the following:

Select [Product Code], Month, AvgSalesPrice 
From tblSalesHistory Where 
       tblSalesHistory.Month=(Select Max(Month) from tblSalesHistory as myVar Where 
       myVar.[Product Code] = tblSalesHistory.[Product Code]) 
ORDER BY Month DESC;

This version's tested, I really should test my sql before offering it, sorry about that.

Basically you want to do a sub query within the query for the max date. Hope this helps, if you can't get this to fire for some reason I have the file. I hate Access.

 

[2077delta]

Thanks Nate this works very well. I have joined a another table (tblMonthlyData) to tblSalesHistory to limit the product codes. The SQL is a s follows:

SELECT tblMonthlyData.MonthEnd, tblSalesHistory.[Product Code], tblSalesHistory.Month, tblSalesHistory.AvgSalesPrice
FROM tblMonthlyData LEFT JOIN tblSalesHistory ON tblMonthlyData.ProductCode = tblSalesHistory.[Product Code]
WHERE (((tblMonthlyData.MonthEnd)=[LCM Month]) AND ((tblSalesHistory.Month)=(Select Max(Month) from tblSalesHistory as myVar Where myVar.[Product Code] = tblSalesHistory.[Product Code])))
ORDER BY tblSalesHistory.[Product Code];

I have one final piece to this puzzle. You will notice that I limit the product codes I select from tblMonthlyData with a parameter labeled [LCM Month]. I would like the average sales prices selected from tblSalesHistory to be no greater than the [LCM Month] parameter. For example if I set the date parameter to March 31, 2003 the query would return the most current average sales price from tblSalesHistory equal to or less than March 31, 2003.

Thanks so much for all your assistance.