Query on a joined Table

[Montez659]

I need a bit of help thinking through a query I am working on. I have three tables, one being a joined table - tblSiteVisits, tblIssueCodes, tblSiteVisitsJOINEDIssueCodes. I think I may have messed up my intention when creating this db.

Reps go out on Site Visits - logged into tblSiteVisits. If they encounter a problem, that problem is logged into the Joined table. tblIssueCodes have multiple columns so a m-v field wouldn't work (and they are of the devil anyways!). However, it is possible that not every site visit has an issue, and thus no issue code would be attached to that record (i have the joined table as a subform to choose the codes from a combobox and record notes , resolved date, etc.

So I try to run a query, and the only record that is returning (in my test tables) is the one with issue codes attached to it. I need all of the records to return, and not just the ones with issue codes attached to them. Can't figure out what I am doing incorrectly.

As always, insight and help is appreciated.

 

[Montez659]

This is the (really long) SQL string:

SELECT tblSiteVisits.visitID AS tblSiteVisits_visitID, tblSiteVisits.visitDate, tblSiteVisits.visitType, tblSiteVisits.provName, tblSiteVisits.provType, tblSiteVisits.provType.Value, tblSiteVisits.provID, tblSiteVisits.provGroup, tblSiteVisits.groupID, tblSiteVisits.npiID, tblSiteVisits.provAddress, tblSiteVisits.provCity, tblSiteVisits.provState, tblSiteVisits.provCounty, tblSiteVisits.provPhone, tblSiteVisits.meetingWith, tblSiteVisits.contactEmail, tblSiteVisits.visitReported, tblSiteVisits.enteredFacets, tblSiteVisits.primaryRep, tblSiteVisits.assistingRep, tblSiteVisits.visitTime, tblSiteVisits.visitPriority, tblSiteVisits.visitLevel, tblSiteVisits.clinicUrgent, tblSiteVisits.clinicPersistent, tblSiteVisits.clinicRoutine, tblSiteVisits.clinicPrevent, tblSiteVisits.refLabName, tblSiteVisits.ediClearinghouse, tblSiteVisitsJOINEDIssueCodes.issueID AS tblSiteVisitsJOINEDIssueCodes_issueID, tblSiteVisitsJOINEDIssueCodes.visitID AS tblSiteVisitsJOINEDIssueCodes_visitID, tblSiteVisitsJOINEDIssueCodes.issueNotes, tblSiteVisitsJOINEDIssueCodes.dateResolved, tblSiteVisitsJOINEDIssueCodes.issueResolved, [Issue Codes].issueID AS [Issue Codes_issueID], [Issue Codes].issueCode, [Issue Codes].issueDescription
FROM tblSiteVisits INNER JOIN ([Issue Codes] INNER JOIN tblSiteVisitsJOINEDIssueCodes ON [Issue Codes].[issueID] = tblSiteVisitsJOINEDIssueCodes.[issueID]) ON tblSiteVisits.[visitID] = tblSiteVisitsJOINEDIssueCodes.[visitID];

 

[Montez659]

Ok, I got to the point where I did an Outer join on tblSiteVisits to tblSiteVisitsJOINEDIssueCodes. Not quite sure what to do from here, although I suspect that as soon as I am done writing this, I will figure it out. Murphy's Law I suppose.