Perhaps an easy question... But I can't solve it.

I have two tables:

Tab 1: Includes itno, date and price
Tab 2: Includes itno och quantity

By connecting these two by itno I would like to get one (1) record, showing itno, latest date and a sum (price * quantity).

Example:

Tab 1:

Itno Date Price
1 Jan-10-2005 2,60
1 Feb-14-2005 3,30
1 Feb-7-2005 3,10
2 Jan-5-2005 5,50

Tab 2:
Itno Quant
1 120
2 330


I would like a record like this:

Itno Date Sum
1 Feb-14-2005 396


Please help me...

I am not convince that this will be an optimum solutions but give it a try

SELECT T1.ItemNo, Max(T1.[date]), (Max(T1.Price) * T2.Quant) As SUM FROM Table4 T1 INNER JOIN Table5 T2 ON T1.ItemNo = T2.ItemNo
Group By T1.ItemNo,T2.Quant

Almost there... The result from the solution above gives three lines for Itno 1, I would just like one row as a result, showing the result for the record of the itno with the highest date.

********* There goes me stumbling around in the SQL board... doh! err, this is access, but should work! ****************************


You need to hide the price away to establish the "latest" date first and then include the price back in again to establish the "sum" (err, product... but anyway)... so, for me, that means a pair of queries...

query1:

SELECT [tab 1].itno, Max([tab 1].date) AS MaxOfdate
FROM [tab 1]
GROUP BY [tab 1].itno;


query2:

SELECT Query1.itno, Query1.MaxOfdate, [tab 1].price, [tab 2].quant, [price]*[quant] AS [Sum]
FROM (Query1 INNER JOIN [tab 1] ON (Query1.MaxOfdate = [tab 1].date) AND (Query1.itno = [tab 1].itno)) INNER JOIN [tab 2] ON Query1.itno = [tab 2].itno;


I am sure this is probably do-able in one nice clean query by someone with far better logic than me... but I like to keep it simple...

Does this do what you want?

SELECT T1.ItemNo, T1.[Date], T1.Price * T2.Quant As TheSum
FROM [Tab 1] T1 INNER JOIN [Tab 2] T2 ON T1.Itno = T2.Itno
WHERE T1.[Date] = (SELECT MAX(Date) FROM [Tab 1])