CODEWITHSUNDEEP
sqlvbams-access
Steve Cooke
Steve Cooke

Reputation: 457

MS Access 2002/VBA - join a number of lookup tables to main query?

Consider:

contact
=======
contact_id, not null
name, not null
electorate_id, null allowed
member_type_id, null allowed

member_types
===========+
member_type_id
member_type

electorates
===========
electorate_id
electorate

These are tables I wish to use to populate a form. I have tried this a few ways, but the query returns no data:

SELECT contact_id, name, member_type, electorate
FROM contact LEFT JOIN member_types 
ON contacts.member_type_id=member_types.member_type_id,
contacts LEFT JOIN electorates
ON contacts.electorate_id=electorates.electorate_id

Also:

"SELECT contact_id, name, member_types.member_type, electorates.electorate
FROM contacts, member_types, electorates
WHERE contacts.contact_id=" & contact_id & " 
and electorates.electorate_id=contacts.electorate_id
and member_types.member_type_id = contacts.member_type_id"

Both of these fail. Can someone suggest a query that works please?

Upvotes: 0

Views: 33

Answers (1)

simo.3792
simo.3792

Reputation: 2236

SELECT contact_id, name, member_type, electorate
FROM contact 
LEFT JOIN member_types ON contact.member_type_id=member_types.member_type_id
LEFT JOIN electorates ON contact.electorate_id=electorates.electorate_id

Don't re-specify the contact table in row 4. Also you have contacts in the ON clauses, not contact.

Hopefully LEFT JOINS are supported in ACCESS 2002.

Also, your second query will only work if both the electorate_id and member_id are set. That's why you need the LEFT JOIN.

Upvotes: 1

Related Questions