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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,399
Members
414,063
Latest member
N_Bates

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