Query which shows all data from different tables

[chessico]

Think this is probably simple but so long since I used the code! I have recently added a new table to my DB in response to a new
situation requiring the provision of Loyalty cards. I need to run an old query which worked fine previously.
My Tables used in the query were
tblMembers, tblPayments the query returned every member paid within a specific date range. 500 records
I have now made a new table of Loyalty Cards- tblLoyalCard with key field LoyalID and two fields LoyalCard1, LoyalCard2.
I have added as a foreign key LoyalID to my tblMembers
My query now only displays members records that have data in the new table -tblLoyalCard 356 records Members cannot be members without a payment however not every member has a loyalty card.


I want my query to display all records even if they do not have a loyalty card.
I added this new table because a member can have more than one card otherwise I would have simply added it to the members table as an attribute

[lfpm062010]

It looks like you need to use LEFT from tblMembers to tblLoyalCard on field LoyalID.

To read more about it.
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

[chessico]

Sounds Easy and I looked it up but code is complicated enough already so wouldn't know where to begin adding the Left Join. This is what I've got but it doesn't produce full set of current members
SELECT S_Members_Table.MemberID, S_Members_Table.DOB, S_Members_Table.Title, S_Members_Table.FirstName, S_Members_Table.LastName, S_Members_Table.Nickname, S_Members_Table.Address1, S_Members_Table.Address2, S_Members_Table.PostCode, S_Members_Table.Phone, S_Members_Table.Email, S_Members_Table.DateJoined, S_Members_Table.[Spouse Name], S_Members_Table.SpouseDOB, S_Members_Table.Occupation, S_Members_Table.Deceased, S_Payments_Table.PaymentAmount, S_Payments_Table.PaymentDate, tblLoyalCard.LoyalCardNo1, tblLoyalCard.LoyalCardNo2
FROM (S_Members_Type INNER JOIN (S_Members_Table INNER JOIN S_Payments_Table ON S_Members_Table.MemberID = S_Payments_Table.MemberID) ON S_Members_Type.MemberTypeID = S_Members_Table.MemberTypeID) INNER JOIN tblLoyalCard ON S_Members_Table.MemberID = tblLoyalCard.MemberID
WHERE (((S_Members_Table.Deceased)=False) AND ((S_Payments_Table.PaymentDate) Between IIf(Format(Date(),"mmdd")>"0630",DateSerial(Year(D ate()),7,1),DateSerial(Year(Date())-1,7,1)) And Date()));

[lfpm062010]

SELECT S_Members_Table.MemberID, S_Members_Table.DOB, S_Members_Table.Title, S_Members_Table.FirstName, S_Members_Table.LastName, S_Members_Table.Nickname, S_Members_Table.Address1, S_Members_Table.Address2, S_Members_Table.PostCode, S_Members_Table.Phone, S_Members_Table.Email, S_Members_Table.DateJoined, S_Members_Table.[Spouse Name], S_Members_Table.SpouseDOB, S_Members_Table.Occupation, S_Members_Table.Deceased, S_Payments_Table.PaymentAmount, S_Payments_Table.PaymentDate, tblLoyalCard.LoyalCardNo1, tblLoyalCard.LoyalCardNo2
FROM (S_Members_Type INNER JOIN (S_Members_Table INNER JOIN S_Payments_Table ON S_Members_Table.MemberID = S_Payments_Table.MemberID) ON S_Members_Type.MemberTypeID = S_Members_Table.MemberTypeID) LEFT JOIN tblLoyalCard ON S_Members_Table.MemberID = tblLoyalCard.MemberID
WHERE (((S_Members_Table.Deceased)=False) AND ((S_Payments_Table.PaymentDate) Between IIf(Format(Date(),"mmdd")>"0630",DateSerial(Year(D ate()),7,1),DateSerial(Year(Date())-1,7,1)) And Date()));

[chessico]

I tried this but get an error message
Syntax Error (missing operator) in query expression '(((S_Members_Table.Deceased)=False) AND ((S_Payments_Table.PaymentDate) Between IIf(Format(Date(),"mmdd")>"0630",DateSerial(Year(D ate()),7,1),DateSerial(Year(Date())-1,7,1)) And Date()))'.

[chessico]

Thank you - it does work. I put inner join here to experiment before but got syntax error so assumed I'd done something wrong. so after you actually typed it into the code I realised I was on right track until syntax message however on rechecking I noted that last part of code
had this space in it causing the error
Between IIf(Format(Date(),"mmdd")>"0630",DateSerial(Year(D ate()),7,1),DateSerial(Year(Date())-1,7,1)) And Date()));
- So Yes it works brilliantly Once again thank you

[lfpm062010]

That is great.

Sorry that I did not notice the space.
Copy and paste in the forum, sometimes get some extra space.