SQL Query MAX Date

psulions83

Board Regular
Joined
Nov 16, 2015
Messages
109
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.

Code:
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,908
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 11, 2009
Messages
1,172
> 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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,690
Office Version
  1. 2013
Platform
  1. Windows
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):
Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,019
Messages
5,526,290
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top