Multiple Records being returned in Query

[BillA]

Having problems with multiple records being returned as I attempt to build
this query.

Here are my 3 tables with key fields:
(leading underscores added for viewing)

___tbl_Project
ProjectID (pk)
Status {open, closed etc…}

With 1:M relationship to

__tbl_RenewORAmend
RenewAmendID (pk)
ProjectID (fk)
DateReceived {date field}
RenewAmend {cbo - choice between Renew or Amend}

With 1:M relationship to

__tbl_RenewAmendComments
CommentID (pk)
RenewAmendID (fk)
IssueDate {date of comment / issue}
Issue {text field}

__tbl_Project contains information about the project;
__Tbl_RenewORAmend contains information about project renewals and
amendments – each new date in the DateReceived field represents a new renewal
or amendment to the project (a project may have many renewals and/or
amendments);
__Tbl_RenewAmendComments captures comments to a renewal or amendment
(renewal and an amendment may have many comments)

My goal is to create a query that gives me all projects that are “open,†and
if there is an renewal or amendment, indicate which ie; "Renew" provide the
most recent “DateReceived†and then – the most recent “Issue†entered (for
the DateReceived record).

I have the SQL for capturing the TOP Issue, where I concatenate the
IssueDate & Issue fields. This is what the SQL looks like:
Issue: (SELECT TOP 1 (tbl_RenewAmendComments!IssueDate & ": " &
tbl_RenewAmendComments!Issue) FROM tbl_RenewAmendComments
WHERE tbl_RenewAmendComments.RenewAmendID =
tbl_RenewORAmend.RenewAmendID ORDER BY
tbl_RenewAmendComments.IssueDate DESC)

I have been struggling to create the query (or pre/sub queries) that don’t
give me multiple instances of records.

Any advice or suggestions would be appreciated.

Thanks,
Bill

 

[KARL DEWEY]

Try these two queries --
BillA_1 --
SELECT tbl_Project.ProjectID, tbl_Project.Status,
Max(tbl_RenewORAmend.DateReceived) AS MaxOfDateReceived
FROM tbl_Project INNER JOIN tbl_RenewORAmend ON tbl_Project.ProjectID =
tbl_RenewORAmend.ProjectID
GROUP BY tbl_Project.ProjectID, tbl_Project.Status
HAVING (((tbl_Project.Status)="Open"));

SELECT BillA_1.ProjectID, BillA_1.MaxOfDateReceived,
tbl_RenewAmendComments.RenewAmendID, tbl_RenewAmendComments.IssueDate,
tbl_RenewAmendComments.Issue
FROM BillA_1 INNER JOIN (tbl_RenewORAmend INNER JOIN tbl_RenewAmendComments
ON tbl_RenewORAmend.RenewAmendID = tbl_RenewAmendComments.RenewAmendID) ON
(BillA_1.MaxOfDateReceived = tbl_RenewORAmend.DateReceived) AND
(BillA_1.ProjectID = tbl_RenewORAmend.ProjectID);

 

[BillA]

Hello Karl,

I'm sorry for my tardy response, been away and have now just resolved my
problem with your suggestion.

I attempted to apply your queries and I kept on receiving duplicate records
from the table containing comments.
Your suggestions made me start thinking about the order of my queries and
after a couple of (gulp)... days of trial and error I finally have come up
with a solution. I tried a single nested subquery, but couldn't find the
proper syntax, so here is what I did:

As you suggested, I used 2 queries.
The first removes duplicated record ID (RenewAmendID) numbers, thus
identifying the only the records to be used in my next query.

SELECT T1.ProjectID, T1.RenewAmendID, T1.DateReceived
FROM tbl_RenewORAmend AS T1
WHERE (((T1.DateReceived)=(SELECT MAX(T2.DateReceived)
FROM tbl_RenewORAmend as T2
WHERE T2.ProjectID = T1.ProjectID)));

In the second query, where I can now bring in other tables without creating
more duplicated records, contains the concatenated 'issues' or comments for
each of the records identified in query 1.

SELECT subqry_RoA_Comments.ProjectID, subqry_RoA_Comments.RenewAmendID,
subqry_RoA_Comments.DateReceived,
(SELECT TOP 1 (tbl_RenewAmendComments!IssueDate & ": "
& tbl_RenewAmendComments!Issue)
FROM tbl_RenewAmendComments
WHERE tbl_RenewAmendComments.RenewAmendID =
subqry_RoA_Comments.RenewAmendID
ORDER BY IssueDate,CommentID DESC) AS Issues
FROM tbl_RenewAmendComments RIGHT JOIN subqry_RoA_Comments ON
tbl_RenewAmendComments.RenewAmendID = subqry_RoA_Comments.RenewAmendID;

Karl, thanks again for your time and assistance with my problem.
Bill
___________________________________________________________