Join 4 tables, one of which has no matching field

[Bojangles]

I am using Active Reports which allows only one data control. For my report recordset, I must have fields from 3 tables that have matching fields and then I need fields from 1 table that has no matching field.. I must use a LEFT JOIN along with an INNER JOIN to get what I need from the 3 tables that DO have matching fields. I have created a SELECT statement thats works great to get the fields from those 3 tables. BUT I cannot figure how to include the fields I need from the 4th table that has no matching field. Everything I try gives me an SQL error.

I HAVE been able to include all 4 tables if I use two INNER JOINS for the other 3 tables, but the INNER JOINS dont get all of the records in one of the tables, which I must have.

Below is the SELECT satement that gets exactly what I need for the first 3 tables using the LEFT JOIN and the INNER JOIN. But I also need Company.CompanyName and Company.CompanyAddress from the 4th table Company. (As I said, table Company has no field on which to match. I just simply want to use those fields in my report.

Here is the SELECT statement that works great except I'm not getting the fields I need from table Company.

SELECT LastPerMembers.[Last Name], LastPerMembers.[First Name], LastPerMembers.M, LastPerMembers.Address, LastPerMembers.City, LastPerMembers.State, LastPerMembers.Zip, LastPerMembers.SocSec, LastPerMembers.ID, LastPerMembers.BOPBal, Members.ID, Members.BOPBal, Members.BOPPas, Members.BOPDons, LastPerAllMembersTrans.TransDate, LastPerAllMembersTrans.TransType, LastPerAllMembersTrans.TransDesc, LastPerAllMembersTrans.ChgAmt, LastPerAllMembersTrans.CredAmt, LastPerAllMembersTrans.MemID, LastPerAllMembersTrans.TransID FROM (LastPerMembers LEFT JOIN LastPerAllMembersTrans ON LastPerMembers.ID = LastPerAllMembersTrans.MemID) INNER JOIN Members ON LastPerMembers.ID = Members.ID ORDER BY LastPerMembers.[Last Name], LastPerMembers.[First Name], LastPerMembers.M

What must I do to include the fields from table Company in this Recordset?

Would someone please help me?

Thanks much,
Bojangles

 

[MarkSweetland]

With no relational coluumns in the Company Table you're not going to be able to tell what company goes with which member (if that's the case). You could do a UNION ALL with the select from the Company table with the non-matching columns dummied up to ensure the number of columns returned are the same. But the end result will be the same, you get the company data in the report, but what goes where?


Mark

The law, in its majestic equality, forbids the rich, as well as the poor, to sleep under the bridges, to beg in the streets, and to steal bread.

Anatole France