SQL Select Field in Query Without Affecting Query Results

Question

I am trying to run a query to that will find the most recent PRICE per customer and part. My parameters are This_Customer, This_Part, and This_Date. Any parameter left null should return all values for that field.

TABLE1

ID  |  Customer  |  Part  |  BegDate   |  Price
101    1            A        1/1/2013     $1
102    2            A        2/1/2013     $2
103    2            A        3/1/2013     $3
104    1            B        4/1/2013     $4
105    2            B        5/1/2013     $5

By running the two following queries, I have been able to accomplish finding the appropriate RECORDS per customer, part, and date, but IT WILL ONLY WORK IF I EXCLUDE PRICE.

The first query uses the DateDiff function with <=0 criteria to find all dates less than This_Date.

Query 1 “DaysSinceQuery”:

SELECT DateDiff("d",This_Date,BegDate) AS DaysSince, Table1.Part, Table1.Customer, Table1.BegDate, Table1.Price, Table1.ID
FROM Table1
WHERE (((DateDiff("d",This_Date,BegDate))<=0) AND ((Table1.Part) Like IIf(This_Part<>"",This_Part,"*")) AND ((Table1.Customer) Like IIf(This_Customer<>"",This_Customer,"*")));

The second query uses the Max function to find the record with the fewest number of days from This_Date (negative DaysSince value closest or equal to zero).

Query 2 “NearestDateQuery”:

SELECT Max(DaysSinceQuery.DaysSince) AS NearestDate, Table1.Part, Table1.Customer
FROM Table1 INNER JOIN DaysSinceQuery ON Table1.ID = DaysSinceQuery.ID
GROUP BY Table1.Part, Table1.Customer;

How do I add the Table1.Price values to NearestDateQuery without affecting the NearestDateQuery results? It seems like there should be a simple solution, but I just can’t get it.

For further clarity, below are numerous examples of how this query should work.

Thank you for any help!

QUERY EXAMPLE 1
This_Customer     2
This_Part         A
This_Date         2/15/2013

ID  |  Customer  |  Part  |  BegDate   |  Price
102    2            A        2/1/2013     $2


QUERY EXAMPLE 2
This_Customer     NULL
This_Part         A
This_Date         2/15/2013

ID  |  Customer  |  Part  |  BegDate   |  Price
102    2            A        2/1/2013     $2


QUERY EXAMPLE 3
This_Customer     2
This_Part         NULL
This_Date         5/15/2013

ID  |  Customer  |  Part  |  BegDate   |  Price
103    2            A        3/1/2013     $3
105    2            B        5/1/2013     $5


QUERY EXAMPLE 4
This_Customer     2
This_Part         A
This_Date         NULL

ID  |  Customer  |  Part  |  BegDate   |  Price
102    2            A        2/1/2013     $2
103    2            A        3/1/2013     $3


QUERY EXAMPLE 5
This_Customer     NULL
This_Part         A
This_Date         NULL

ID  |  Customer  |  Part  |  BegDate   |  Price
101    1            A        1/1/2013     $1
102    2            A        2/1/2013     $2
103    2            A        3/1/2013     $3


QUERY EXAMPLE 6
This_Customer     NULL
This_Part         NULL
This_Date         4/15/2013

ID  |  Customer  |  Part  |  BegDate   |  Price
103    2            A        3/1/2013     $3
104    1            B        4/1/2013     $4


QUERY EXAMPLE 7
This_Customer     2
This_Part         NULL
This_Date         NULL

ID  |  Customer  |  Part  |  BegDate   |  Price
102    2            A        2/1/2013     $2
103    2            A        3/1/2013     $3
105    2            B        5/1/2013     $5

Which RDBMS is this? The query syntax doesn't quite match anything I've seen, although very similar to SQL server. — Joachim Isaksson, Jul 12 '13 at 16:39
Joachim, I am doing this in Access. I reviewed my post and found a few typos that were not in my original SQL code in Access. The typoes are now corrected. Sorry for the error. My problem still remains. — likearock83, Jul 12 '13 at 17:02

score 0 · Accepted Answer · answered Jul 12 '13 at 17:47

I would use a "pointer" query that grabs the most recent date for each Customer and Part (in this case I'm just using todays date by using the Date() function, but you can make that a variable):

SELECT Table1.Customer, Table1.Part, Max(Table1.BegDate) AS MaxOfBegDate
FROM Table1
GROUP BY Table1.Customer, Table1.Part
HAVING (((Max(Table1.BegDate))<=Date()));

You probably have to change the HAVING clause to include Part and Customer.

Then join that pointer to your original table to bring in the ID and Price for that proper date:

SELECT Table1.ID, Table1.Customer, Table1.Part, Table1.BegDate, Table1.Price
FROM Query1 INNER JOIN Table1 ON (Query1.MaxOfBegDate = Table1.BegDate) AND (Query1.Part = Table1.Part) AND (Query1.Customer = Table1.Customer);

Johnny, thank you! I added a WHERE clause on the second query for Part and Customer and it worked perfectly. In hindsight, I when wrong by using WHERE instead of HAVING in the first query. If anyone viewing this post in the future wants to learn more about WHERE vs HAVING, read the post I found at the following link. http://stackoverflow.com/a/9253267/2577090 — likearock83, Jul 12 '13 at 18:30

SQL Select Field in Query Without Affecting Query Results

1 Answers1