Query on a joined Table

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
This is the (really long) SQL string:

Code:
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

Well-known Member
Joined
May 4, 2005
Messages
918
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,199
Messages
5,835,933
Members
430,396
Latest member
dzifna

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top