Dynamic query and outer join difficulty

[Archie999]

Hi All,

With help from this site and the Microsoft site I have created a dynamic query that I use as a recordset for a report. However, there is one thing that I have been unsuccessful in doing thus far and would appreciate an expert's opinion

To summarize by DB, here are the applicable tables and their relationship:



tblIssue - Main Parent Table

tblIssueIncident - tblIssue.MyIssueID = tblIssueIncident.IssueID

tblIssueUpdate - tblIssueIncident.MyIssueIncidentID = tblIssueUpdate.IssueIncidentID

So I have a form that feeds a dynamic query to give me a report of all issues and their updates and this works. I would like another query that will return only the latest update to the form (for a summary report).

To facilitate this I created another query to retreive the MAX update date (this is called qrySummaryQuery1). FYI - this query looks like this:

SELECT Max(tblIssueUpdate.UpdateDate) AS MaxOfUpdateDate, tblIssueIncident.MyIssueIncidentID AS IncidentID
FROM tblIssueIncident INNER JOIN tblIssueUpdate ON tblIssueIncident.MyIssueIncidentID = tblIssueUpdate.IssueIncidentID
GROUP BY tblIssueIncident.MyIssueIncidentID;




Based on all of that this is the dynamic query I came up with:

"Select * From tblIssue INNER JOIN ((tblIssueIncident INNER JOIN qrySummaryQuery1 ON " _
& "tblIssueIncident.MyIssueIncidentID = qrySummaryQuery1.IncidentID) " _
& "INNER JOIN tblIssueUpdate ON " _
& "(tblIssueIncident.MyIssueIncidentID = tblIssueUpdate.IssueIncidentID) AND " _
& "(qrySummaryQuery1.MaxOfUpdateDate = tblIssueUpdate.UpdateDate)) ON " _
& "tblIssue.MyIssueID = tblIssueIncident.IssueID " _
& (" where " + Mid(Xwhere, 6) & ";"))

This works except that it does NOT return issues that do not have an update yet. In order to supply all issues regardless of whether or not they have an update I would need a LEFT Join but when I change line 3 from INNER to LEFT, Access complains that the join is unsupported. How can I create a join that will return only the latest updates, if an update exists AND still return the issue incident even if there is no update.

I hope that I have supplied enough information. If not let me know and I can perhaps email you my DB. I would appreciate any insight into how to make this work.

Thanks

Arch

 

[Archie999]

Got This To Work

Hi Again,

I spend more time on this and finally found something that works. Thanks anyways... It was complicated to explain.

FYI for any future readers that have a similar issue. What I did was to modify my SummaryQuery (which previously simply retreived the latest update) and had it include all fields from the tables that I wanted to join. So, when I joined the query and the table it was not complicated and a LEFT join was easy:

"Select * From tblIssue LEFT JOIN qrySummaryQuery1 ON " _
& "tblIssue.MyIssueID = qrySummaryQuery1.IssueID " _
& (" where " + Mid(Xwhere, 6) & ";"))