Three Table SQL

[CementCarver]

I am having difficulty getting a sql statement working that will display data from three tables. I have tables FULLGROWER, FULLPACKER and FULLMARKETER. All are joined by a MEMBER_ID column.



My current sql statement:

SELECT BUSINESS.*, FULLGROWER.Farm_No, FULLGROWER.District AS District_FULLGROWER, FULL_MARKETER.[Business Name], FULLPACKER.[Business Name]
FROM ((BUSINESS LEFT JOIN FULLMARKETER ON BUSINESS.Member_ID = FULLMARKETER.MEMBER_ID) INNER JOIN FULLGROWER ON BUSINESS.Member_ID = FULLGROWER.MEMBER_ID) LEFT JOIN FULLPACKER ON BUSINESS.Member_ID = FULLPACKER.MEMBER_ID;

The sql statement is not working like I need it to be, since if a record only exists in the FULLPACKER table, the form doesn't load. Nor will it load if only a record exists in the FULLMARKETER table.

What I need it to do is this: a record could exist in one, two or all three tables or any combination of the three.

Can anyone provide me with the correct statement?

CementCarver

[June7]

What is nature of table relationships? Maybe you should join each of these 3 tables to Members table? Why are you joining for a form anyway? This would not allow for adding new records to the tables.

[CementCarver]

June7, the initial form is a gateway form, basically allowing my client to go in three directions, meaning that they can view the grower, packer and/or marketer data. The new record capability is given when they find the MEMBER company and then add new records.

The main table.... MEMBER is linked to the GROWER, PACKER and/or MARKETER via their MEMBER_ID. So the MEMBER can either be a grower, packer or marketer or any combination of the three.

CementCarver

[June7]

Do these three tables have identical (or nearly identical) fields? Should be one table with another field for the category. Otherwise, use a UNION query to get the three tables into a single dataset that can be filtered/sorted. Cannot edit UNION query. No query designer for UNION, must type or copy/paste into SQL View of query builder.

SELECT MemberID, LastName, FirstName, "Grower" AS Category FROM Grower
UNION SELECT MemberID, LastName, FirstName, "Packer" FROM Packer
UNION SELECT MemberID, LastName, FirstName, "Marketer" FROM Marketer;

[CementCarver]

Thanks June7, I suspected that it would be a union.

Appreciate your assistance.....CementCarver