LEFT Join Woes

[Archie999]

Hi All,

I have an issue that's driving me crazy because logically, to me anyway, this should work... Access 2002 doesn't see it that way :-(

My goal is this:

I have a parent table (IssueIncident) and a child table (IssueUpdate). I am making a summary type report that should display all issues from parent issues table, and with it only the latest corresponding update from the child table. If there are no updates, I would like to display the issue from the parent table anyway.

After checking several posts here I decided that I would be best creating more than one query for this. The first query (zTestLatestDate) will simply find the latest updates for me:

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

Now, this works. I get only the latest update AND because of the LEFT JOIN I get issues from parent table that have no child records. So far so good but here is where it gets wierd.

I just want to join this query with the rest of the data from the issue and update table for my report. For simplicity sake I am just going to talk about the update table below because if I get it going on that I should be good!

I created the following query to try and JOIN my zTestLatestDate query to the IssueUpdate table

SELECT <all fields from IssueUpdate - I wont list them>
FROM zTestLatestDate INNER JOIN tblIssueUpdate ON zTestLatestDate.MyIssueIncidentID = tblIssueUpdate.IssueIncidentID
WHERE (((tblIssueUpdate.UpdateDate)=[zTestLatestDate].[MaxOfUpdateDate]));

But this does not work. It will not display Issues from parent table without an update. In other words I've lost my LEFT JOIN. Note that I have tried the above query as a LEFT JOIN in itself and have played around with it quite a bit and cannot get the desired result.

Sorry if I've prattled on. It's hard to explain these things! I hope someone can see the error of my ways

I would appreciate any advice. Let me know if you want to take a peek at the DB.

Thank You!!

Arch

 

[neileg]

FROM zTestLatestDate INNER JOIN tblIssueUpdate ON zTestLatestDate.MyIssueIncidentID = tblIssueUpdate.IssueIncidentID

This should be a Left Join. Are you saying you have tried this and it still doesn't work?

 

[Archie999]

Yup, that's exactly what I'm saying. It still does not work.

Thanks

 

[Jon K]

I think you need a series of three queries. See example attached.

 

[Archie999]

Thank you so much Jon. You the man!

That worked beautifully. I was on the right track but I can see the error of my ways.

I'm not quite there though

To complicate things I need to Join your Query3 to a dynamic query to acheive my goal.

When I try to do that it asks me for MaxofUpdateDate. It should know that, shouldn't it?? In my mind if you run your Query3 it should automatically run the queries 1 and 2 since then are all kinda joined. does this not happen in my dynamic query.

I have attached the pertinent parts of my DB including your queries. Could you or anyone take a look and see where I am going wrong.

Thanks again for your insight.

Arch