Big Query Problem.. any help appreciated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Big Query Problem.. any help appreciated

Author

Topic

Lea Ibbs
Starting Member

3 Posts

Posted - 2008-03-15 : 18:20:19

I am trying to set up a query to select PaymentDate, CustSurname, CustForename, AgentName showing only the last record for each customer if the last payment is over 7 days and also include customers with no payment.

the table structure is:

tblLoans
LoanID (PK)
CustID (FK)
AgentID(FK)

tblPayments
PaymentID (PK)
PaymentDate
LoanID (FK)

tblAgents
AgentID (PK)
AgentName

tblCustomers
CustID (PK)
CustSurname
Custforename

I have tried to solve this to no avail, any help would be appreciated.

the code I have tried so far (not complete) is

SELECT tblCustomers.CustSurname, tblCustomers.Custforename, tblAgents.AgentName, tblPayments.PaymentDate FROM (tblCustomers INNER JOIN (tblAgents INNER JOIN tblLoans ON tblAgents.AgentID = tblLoans.AgentID) ON tblCustomers.CustID = tblLoans.CustID) INNER JOIN tblPayments ON tblLoans.LoanID = tblPayments.LoanID WHERE tblPayments.PaymentDate = (SELECT MAX tblPayments.PaymentDate FROM (tblCustomers INNER JOIN (tblAgents INNER JOIN tblLoans ON tblAgents.AgentID = tblLoans.AgentID) ON tblCustomers.CustID = tblLoans.CustID) INNER JOIN tblPayments ON tblLoans.LoanID = tblPayments.LoanID AS A WHERE A. tblCustomers.CustSurname = tblCustomers.CustSurname);

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-15 : 22:00:59

Change the join from tblCustomers to the derived table for payments from an INNER join to a LEFT join

Filter the payment date column for >= 7 days ago OR NULL

Sounds like homework, so I won't give you the whole answer...;)

Poor planning on your part does not constitute an emergency on my part.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-15 : 22:07:40

quote:

Filter the payment date column for >= 7 days ago OR NULL

Be careful with "or is null" when using outer joins -- that doesn't work. See:

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Lea Ibbs
Starting Member

3 Posts

Posted - 2008-03-16 : 03:28:55

quote:
Originally posted by dataguru1971

Change the join from tblCustomers to the derived table for payments from an INNER join to a LEFT join

Filter the payment date column for >= 7 days ago OR NULL

Sounds like homework, so I won't give you the whole answer...;)

Poor planning on your part does not constitute an emergency on my part.

no not homework long time since I studied

just a mod for a friend and found myself rusty. Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources