SQL Query MAX Date

[psulions83]

Hi,

I have the following query that runs but gives me too many lines.

I only want it to return the same amount of lines that exist on table C and give me the value for A.AMOUNT with the latest date which is in a column called DATE.

SELECT      B.BRAND,
      C.CONTRACT,
      C.CUSTOMER,
      A.AMOUNT
   FROM Tbl_Price A,
               Tbl_Data B,
               Tbl_12mth AS C
      WHERE A.PRODUCT = B.PRODUCT
           AND B.PRODUCT = C.PRODUCT

 

[Joe4]

Can you please post sample data from all three tables (we really only need to see all the fields used in the Query for each Table and the Date field)?

 

[Micron]

Agreed. However, I see no joins between tables, so you are probably getting LOTS of results. This would be known as a Cartesian product. If each table has 25 records, you would get 25x25x25 records if there was no criteria. That you are filtering on = fields means who knows how many in the results.

 

[james_lankford]

> I see no joins between tables,

they're doing old style joins

WHERE A.PRODUCT = B.PRODUCT
AND B.PRODUCT = C.PRODUCT

don't know if it still works in current version of access

 

[xenou]

I don't see anything in the query that references a max date so that may be part of the problem. The older join syntax does still work even if not so commonly seen these days.

This *might* work (unfortunately subqueries are not where MSAccess shines):

SELECT      
	B.BRAND,
	C.CONTRACT,
	C.CUSTOMER,
	A.AMOUNT
FROM
	Tbl_Price A 
	INNER JOIN Tbl_Data B
	ON A.PRODUCT = B.PRODUCT
	INNER JOIN Tbl_12mth C
	ON B.PRODUCT = C.PRODUCT
WHERE 
	A.[DATE] = (SELECT MAX(A2.[DATE]) FROM Tbl_Price A2 WHERE A2.PRODUCT = A.PRODUCT)

Agree that some sample data would help for testing and clarifying the data relationships.