Find most current date

[2077delta]

I have two tables. Table 1 lists product codes, month and volume in lbs. Table 2 lists product codes, month and average sales price. A product in Table 1 may not have an average sales price in Table 2 for that month, but would have a sales price listed for some prior month. What I'm looking for is a query that would let me select all the products from Table 1 for a given month i.e. May 2003 and the most current sales price for that product from Table 2. The one restriction is that the sales price selected from Table 2 can't be from a month more current than the month selected for Table 1. For example let's say product 1001 has an average sales price in Table 2 for Jan 03, Feb 03 and Apr 03 but not Mar 03. The table below represents the result set I would expect based on the month selected in Table 1:

Volume Avg Sales
Month Price Month
Table 1 Table 2
Jan 03 Jan 03
Feb 03 Feb 03
Mar 03 Feb 03
Apr 03 Apr 03

I've tried every combination of the MAX function I could think of but nothing work the way I want. I'm sure someone out there know a solution for this.

 

[Russell Hauf]

You can combine the DLookup and DMax functions to do this. Start a new query, add table 1, add the "Month" field, then in the next column put something like this:

• AvgPrice: DLookUp("[fldAvgPrice]","tblB","[fldMonth]=#" & DMax("[fldMonth]","tblB","[fldMonth] <=#" & [fldMonth] & "#") & "#")

Where my tables are named tblA and tblB (for 1 and 2, respectively), and my fields are named fldMonth, fldAvgPrice (I disregarded any field in table 1 that was not the "month" field). Here is my SQL:

• SELECT tblA.fldMonth, DLookUp("[fldAvgPrice]","tblB","[fldMonth]=#" & DMax("[fldMonth]","tblB","[fldMonth] <=#" & [fldMonth] & "#") & "#") AS AvgPrice
  FROM tblA;

HTH,

Russell

 

[2077delta]

First, thank you for your response.

Second, I forgot to mention that I need to return the product code. Everytime I introduce this piece, the query fails. Let's simplify the request and see if I can get this to work. Let's use Table two that has multiple instances of each product code with different months and average sales prices. Can I create a query that will return the most current sales price for all product codes but but more current than date X. I would like date X to be a parameter that I supply when I run the query. If I put in 04/26/03, the resulting set might look like this:

Product Date ASP
T10011 04/26/03 1.11
T10012 03/29/03 0.98
T10013 04/26/03 1.05
T10014 01/25/03 0.75

The key is I only want each product listed once with the applicable most current average sales price not greater than the date I supply. I hope this is a little clearer than my last request.

Thanks in advance