CODEWITHSUNDEEP
sqlms-accessjoin
u_u-de
u_u-de

Reputation: 103

MS Access Help to run a query

I have two tables as follows.enter image description here

I want to get all members who are active and payment period = fortnightly and corresponding savings record for each member for a specific year and month. Note that some of the members may not have a savings record for given year and month. I have a query written, but it does not return the members who does not have a savings record in savings table.

SELECT Member.memberID As [ID],Member.firstName AS [First Name],
       Member.lastName AS [Last Name], Member.paymentPeriod AS [Payment Period],
       Savings.savingId AS [Savings Id], Savings.enteredAmount AS [Amount]
FROM Member
    LEFT JOIN Savings ON Member.memberID = Savings.memberId
WHERE Member.isActive = 'Active' AND Member.paymentPeriod = 'Fortnightly'
      AND Year(Savings.enteredDate)=2019 AND Month(Savings.enteredDate) = 1;

Upvotes: 0

Views: 49

Answers (1)

jarlh
jarlh

Reputation: 44786

Move the SAVINGS conditions from WHERE to ON to get true LEFT JOIN result:

SELECT Member.memberID As [ID],Member.firstName AS [First Name],
       Member.lastName AS [Last Name], Member.paymentPeriod AS [Payment Period],
       Savings.savingId AS [Savings Id], Savings.enteredAmount AS [Amount]
FROM Member
    LEFT JOIN Savings ON Member.memberID = Savings.memberId
      AND Year(Savings.enteredDate)=2019 AND Month(Savings.enteredDate) = 1
WHERE Member.isActive = 'Active' AND Member.paymentPeriod = 'Fortnightly';

(With those conditions in the WHERE clause, you'll get regular INNER JOIN result.)

Edit: Attempt to avoid "Join expression not supported error":

SELECT Member.memberID As [ID],Member.firstName AS [First Name],
       Member.lastName AS [Last Name], Member.paymentPeriod AS [Payment Period],
       Savings.savingId AS [Savings Id], Savings.enteredAmount AS [Amount]
FROM Member
    LEFT JOIN Savings ON Member.memberID = Savings.memberId
WHERE Member.isActive = 'Active' AND Member.paymentPeriod = 'Fortnightly'
      AND (Year(Savings.enteredDate)=2019 or Year(Savings.enteredDate) IS NULL)
      AND (Month(Savings.enteredDate) = 1 or Month(Savings.enteredDate) IS NULL);

Upvotes: 1

Related Questions