Query all but Filter !1 1

[qitcryn]

Needing help on this: I have 2 tables Aging & Customers

The Aging Table has current account info..
The Customer Table has all calls to each account.

I need a query to look at all the records in Aging Table & Customers Table and show me all Aging records that have been called & not been called, and only show me the last call date.

I've tried "Max" which worked for customers that have been called, but won't show me those that have NOT been called.

Thanks,
Ivan

 

[lespaul]

you'll have to do a LEFT OUTER JOIN instead of an INNER JOIN to get the ones that are not there....check out Understanding SQL Joins

and then you need to UNION the two result sets to get them all in one query.

Leslie

 

[qitcryn]

Thanks for your reply..

The SQl looks like this:

SELECT Aging.[Cust #], Aging.[Sales Rep], Aging.[Customer Name], Aging.[001-030], Aging.[031-060], Aging.[061-090], Aging.[091-180], Aging.[181-365], Aging.[>>>-365], Aging.[Total Past Due], Aging.CURRENT, Aging.[Tot Outstand]
FROM Aging LEFT JOIN Customers ON Aging.[Cust #] = Customers.[Cust #]
WHERE (((Customers.[Cust #]) Is Null));


Where do i make the inner/outer join changes?

Thanks,

 

[qitcryn]

wait...i forgot the call date

SELECT Aging.[Cust #], Aging.[Sales Rep], Aging.[Customer Name], Aging.[001-030], Aging.[031-060], Aging.[061-090], Aging.[091-180], Aging.[181-365], Aging.[>>>-365], Aging.[Total Past Due], Aging.CURRENT, Aging.[Tot Outstand], Customers.[Cust #], Customers.CallDate
FROM Aging LEFT JOIN Customers ON Aging.[Cust #] = Customers.[Cust #];

 

[qitcryn]

thank you ,, thank you...the article helped out. )

I actually.. I figured it out.. In the tables i have, i should have used Right join...

So, the query looks at the aging and customers and shows all accounts in the Aging table and only last call made, whether a call has or has not been made. This way i can see if an account has never been called or not.

My SQL statement looks like this:

SELECT Aging.[Cust #], Aging.[Customer Name], Max(Customers.CallDate) AS MaxOfCallDate, Aging.[Sales Rep], Aging.[001-030], Aging.[031-060], Aging.[061-090], Aging.[091-180], Aging.[181-365], Aging.[Total Past Due], Aging.CURRENT, Aging.[Tot Outstand]
FROM Customers RIGHT JOIN Aging ON Customers.[Cust #] = Aging.[Cust #]
GROUP BY Aging.[Cust #], Aging.[Customer Name], Aging.[Sales Rep], Aging.[001-030], Aging.[031-060], Aging.[061-090], Aging.[091-180], Aging.[181-365], Aging.[Total Past Due], Aging.CURRENT, Aging.[Tot Outstand];