need SQL help

[JohnLute]

I have a subreport that queries data from 3 tables:

SELECT tblProfilesFacilities.*, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince,
tblFacilities.[Country/Region], tblProfiles.WhereUsedNotes
FROM tblProfiles INNER JOIN (tblProfilesFacilities INNER JOIN tblFacilities
ON tblProfilesFacilities.txtFacilityID = tblFacilities.txtFacilityID) ON
tblProfiles.txtProfileID = tblProfilesFacilities.txtProfileID;

I'd like for the "WhereUsedNotes" field to display in the subreport
regardless if there's a record in tblProfilesFacilities.

Anyone have any suggestions on how to revise the SQL?

Thanks in advance!

 

[Larry Linson]

That will require an outer join... inner joins are also known as equijoins
and require a record in both joined tables. I am presuming you did not write
SQL from scratch, so... in the Query Builder, click on the join line to
highlight it, then right click and choose Join Properties. Pick the one that
seems appropriate, and it will not be "only records in both tables".

Larry Linson
Microsoft Access MVP

 

[JohnLute]

Thanks, Larry! You're correct about not writing the SQL from scratch. I tried
all three join types and they all fail. In fact, only two of the join types
are recognized as valid. The one I pasted below and this one:

SELECT tblProfilesFacilities.*, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince,
tblFacilities.[Country/Region], tblProfiles.WhereUsedNotes
FROM tblProfiles RIGHT JOIN (tblProfilesFacilities INNER JOIN tblFacilities
ON tblProfilesFacilities.txtFacilityID = tblFacilities.txtFacilityID) ON
tblProfiles.txtProfileID = tblProfilesFacilities.txtProfileID;

What you've suggested makes sense but I can't see what I'm doing wrong.


--
www.Marzetti.com

That will require an outer join... inner joins are also known as equijoins
and require a record in both joined tables. I am presuming you did not write
SQL from scratch, so... in the Query Builder, click on the join line to
highlight it, then right click and choose Join Properties. Pick the one that
seems appropriate, and it will not be "only records in both tables".

Larry Linson
Microsoft Access MVP

I have a subreport that queries data from 3 tables:

SELECT tblProfilesFacilities.*, tblFacilities.FacilityName,
tblFacilities.City, tblFacilities.StateOrProvince,
tblFacilities.[Country/Region], tblProfiles.WhereUsedNotes
FROM tblProfiles INNER JOIN (tblProfilesFacilities INNER JOIN
tblFacilities
ON tblProfilesFacilities.txtFacilityID = tblFacilities.txtFacilityID) ON
tblProfiles.txtProfileID = tblProfilesFacilities.txtProfileID;

I'd like for the "WhereUsedNotes" field to display in the subreport
regardless if there's a record in tblProfilesFacilities.

Anyone have any suggestions on how to revise the SQL?

Thanks in advance!