Only add values to by earliest dated row

[djmousie]

I currently have two tables. The MASTER TABLE has a list of accounts and quantities ordered by effective date. The SECOND table has additional quantities that need to be merged in by account #, however,the quantities need to be merged on where the earliest dated account row is. For example,

MASTER TABLE
Account # Qty Eff Date
123 25 6/1/07
123 25 6/15/07

TABLE 2
Account # Qty
123 50

Currently, if i try to add a new field in the MASTER table, if includes the quantity of 50 twice, from TABLE 2, since the account #'s are the same (see below)

MAKE TABLE QUERY RESULTS
Account # Qty Eff date Table 2 Qty
123 25 6/1/07 50
123 25 6/15/04 50

The query should only spit back the following results, any help?

MAKE TABLE QUERY RESULTS
Account # Qty Eff date Table 2 Qty
123 25 6/1/07 50
123 25 6/15/04 <NULL>

 

[PHV]

What about this ?
SELECT M.[Account #], M.Qty, M.[Eff Date], T.Qty2
FROM [MASTER TABLE] AS M LEFT JOIN (
SELECT A.[Account #], Max(A.[Eff Date]) AS LastDate, Sum(B.Qty) AS Qty2 FROM [MASTER TABLE] AS A
INNER JOIN [TABLE 2] AS B ON A.[Account #] = B.[Account #] GROUP BY A.[Account #]
) AS T ON M.[Account #] = T.[Account #] AND M.[Eff Date] = T.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[djmousie]

That sort of worked,not sure if it's me that did it wrong, but its summing Qty2 twice, so in the example above, Qty is coming back as 100 not 50.

 

[PHV]

And this ?
SELECT M.[Account #], M.Qty, M.[Eff Date], T.Qty2
FROM [MASTER TABLE] AS M LEFT JOIN (
SELECT A.[Account #], Max(A.[Eff Date]) AS LastDate, B.Qty AS Qty2 FROM [MASTER TABLE] AS A
INNER JOIN [TABLE 2] AS B ON A.[Account #] = B.[Account #] GROUP BY A.[Account #], B.Qty
) AS T ON M.[Account #] = T.[Account #] AND M.[Eff Date] = T.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[djmousie]

Yes, that works perfectly, thanks a ton!

 

[djmousie]

Actually one more, thing. What if i removed the dating piece all together and I only wanted QTY2 to appear next to one of the similar account #'s, like below...

MAKE TABLE QUERY RESULTS
Account # Qty Table 2 Qty
123 25 50
123 25 <NULL>