Last Record

G

Guest

In database, I have three tables. THe first one has the audit findings, the
second one has the responses from the clients, and the third one has the
follow-up work on th implementation of the recommendation. In the last table
there may be multiple records tied up to that same finding and to that same
client response. I want to generate a query which will show the audit
finding, the management response, an only the LAST audit follow-up entry. I
have designed the below query, but it does not work and I do not get the last
record in the table "follow-up entries for findings".

INSERT INTO [Audit Follow-up Report] ( [No], [Thrust Area], [Title of
Issue], [Risk Severity Code], Recommendation, [Responsible Department],
[Management Action Plan], [Target Completion Date], [Revised Target Date],
[Actual Completion Date], [Follow-up Status], [Change History], [Management
Status Description], [Auditor's Comments] )
SELECT [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], Last([tbl
Follow-up Entries for Findings].[Management's Status Description]) AS
[LastOfManagement's Status Description], Last([tbl Follow-up Entries for
Findings].[Auditor Comments]) AS [LastOfAuditor Comments]
FROM ([tbl Comments] LEFT JOIN ([tbl Follow-up status codes] RIGHT JOIN [tbl
Management Responses] ON [tbl Follow-up status codes].[Follow-up status
order] = [tbl Management Responses].[Follow-up status code]) ON [tbl
Comments].[Comment Table counter] = [tbl Management Responses].[Comment Table
counter]) LEFT JOIN [tbl Follow-up Entries for Findings] ON [tbl Management
Responses].[ID for tbl Management Responses] = [tbl Follow-up Entries for
Findings].[ID in tbl Management Responses]
GROUP BY [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], [tbl
Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="fa-lbr-04-03"))
ORDER BY [tbl Comments].[Order of appearance];

Any clue?
 
J

Jeff Boyce

Define "Last". Are you quite certain that Access uses your definition?

One approach might be to first use a query to obtain the "last" record for
each audit from the follow-up table (you'll need to decide how to define
"last" -- I assume you mean the most-recent-date). Once you have that query
returning a single row per audit, you could join your other tables and that
query together in a new query.

--
Regards

Jeff Boyce
<Office/Access MVP>

Barattolo_67 said:
In database, I have three tables. THe first one has the audit findings, the
second one has the responses from the clients, and the third one has the
follow-up work on th implementation of the recommendation. In the last table
there may be multiple records tied up to that same finding and to that same
client response. I want to generate a query which will show the audit
finding, the management response, an only the LAST audit follow-up entry. I
have designed the below query, but it does not work and I do not get the last
record in the table "follow-up entries for findings".

INSERT INTO [Audit Follow-up Report] ( [No], [Thrust Area], [Title of
Issue], [Risk Severity Code], Recommendation, [Responsible Department],
[Management Action Plan], [Target Completion Date], [Revised Target Date],
[Actual Completion Date], [Follow-up Status], [Change History], [Management
Status Description], [Auditor's Comments] )
SELECT [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], Last([tbl
Follow-up Entries for Findings].[Management's Status Description]) AS
[LastOfManagement's Status Description], Last([tbl Follow-up Entries for
Findings].[Auditor Comments]) AS [LastOfAuditor Comments]
FROM ([tbl Comments] LEFT JOIN ([tbl Follow-up status codes] RIGHT JOIN [tbl
Management Responses] ON [tbl Follow-up status codes].[Follow-up status
order] = [tbl Management Responses].[Follow-up status code]) ON [tbl
Comments].[Comment Table counter] = [tbl Management Responses].[Comment Table
counter]) LEFT JOIN [tbl Follow-up Entries for Findings] ON [tbl Management
Responses].[ID for tbl Management Responses] = [tbl Follow-up Entries for
Findings].[ID in tbl Management Responses]
GROUP BY [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], [tbl
Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="fa-lbr-04-03"))
ORDER BY [tbl Comments].[Order of appearance];

Any clue?
Click to expand...
 
G

Guest

Jeff,

Thanks for your reply. Yes, last means the one with the most recent
follow-up date. What you are suggesting, I have already tried and it did not
work. I have tried to set up a first query that pulls out the record with the
"Max" date in the follow-up entries (there is actually a 'follow-up date
field'). This appears to work, but when I join it back with the other tables,
then the grouping by max does not work no more, and I get all the follow-up
entries again.

Ideas?

"Jeff Boyce" ha scritto:
Define "Last". Are you quite certain that Access uses your definition?

One approach might be to first use a query to obtain the "last" record for
each audit from the follow-up table (you'll need to decide how to define
"last" -- I assume you mean the most-recent-date). Once you have that query
returning a single row per audit, you could join your other tables and that
query together in a new query.

--
Regards

Jeff Boyce
<Office/Access MVP>

Barattolo_67 said:
In database, I have three tables. THe first one has the audit findings, the
second one has the responses from the clients, and the third one has the
follow-up work on th implementation of the recommendation. In the last table
there may be multiple records tied up to that same finding and to that same
client response. I want to generate a query which will show the audit
finding, the management response, an only the LAST audit follow-up entry. I
have designed the below query, but it does not work and I do not get the last
record in the table "follow-up entries for findings".

INSERT INTO [Audit Follow-up Report] ( [No], [Thrust Area], [Title of
Issue], [Risk Severity Code], Recommendation, [Responsible Department],
[Management Action Plan], [Target Completion Date], [Revised Target Date],
[Actual Completion Date], [Follow-up Status], [Change History], [Management
Status Description], [Auditor's Comments] )
SELECT [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], Last([tbl
Follow-up Entries for Findings].[Management's Status Description]) AS
[LastOfManagement's Status Description], Last([tbl Follow-up Entries for
Findings].[Auditor Comments]) AS [LastOfAuditor Comments]
FROM ([tbl Comments] LEFT JOIN ([tbl Follow-up status codes] RIGHT JOIN [tbl
Management Responses] ON [tbl Follow-up status codes].[Follow-up status
order] = [tbl Management Responses].[Follow-up status code]) ON [tbl
Comments].[Comment Table counter] = [tbl Management Responses].[Comment Table
counter]) LEFT JOIN [tbl Follow-up Entries for Findings] ON [tbl Management
Responses].[ID for tbl Management Responses] = [tbl Follow-up Entries for
Findings].[ID in tbl Management Responses]
GROUP BY [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], [tbl
Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="fa-lbr-04-03"))
ORDER BY [tbl Comments].[Order of appearance];

Any clue?
Click to expand...
Click to expand...
 
J

Jeff Boyce

From your description, I can't tell if you are trying to make a single query
do all that, or if you have two separate queries. It sounded a little like
you are using a single query.

My earlier suggestion was to create the "Max" query, close it, create a new
query, add the table and the Max query to the new one, joined on the date
field in each (and what ever other fields need to match up).

Regards

Jeff Boyce
<Office/Access MVP>

Barattolo_67 said:
Jeff,

Thanks for your reply. Yes, last means the one with the most recent
follow-up date. What you are suggesting, I have already tried and it did not
work. I have tried to set up a first query that pulls out the record with the
"Max" date in the follow-up entries (there is actually a 'follow-up date
field'). This appears to work, but when I join it back with the other tables,
then the grouping by max does not work no more, and I get all the follow-up
entries again.

Ideas?

"Jeff Boyce" ha scritto:
Define "Last". Are you quite certain that Access uses your definition?

One approach might be to first use a query to obtain the "last" record for
each audit from the follow-up table (you'll need to decide how to define
"last" -- I assume you mean the most-recent-date). Once you have that query
returning a single row per audit, you could join your other tables and that
query together in a new query.

--
Regards

Jeff Boyce
<Office/Access MVP>

Barattolo_67 said:
In database, I have three tables. THe first one has the audit
Click to expand...
findings,
the
second one has the responses from the clients, and the third one has the
follow-up work on th implementation of the recommendation. In the last table
there may be multiple records tied up to that same finding and to that same
client response. I want to generate a query which will show the audit
finding, the management response, an only the LAST audit follow-up
Click to expand...
entry.
I
have designed the below query, but it does not work and I do not get
Click to expand...
the
last
record in the table "follow-up entries for findings".

INSERT INTO [Audit Follow-up Report] ( [No], [Thrust Area], [Title of
Issue], [Risk Severity Code], Recommendation, [Responsible Department],
[Management Action Plan], [Target Completion Date], [Revised Target Date],
[Actual Completion Date], [Follow-up Status], [Change History], [Management
Status Description], [Auditor's Comments] )
SELECT [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], Last([tbl
Follow-up Entries for Findings].[Management's Status Description]) AS
[LastOfManagement's Status Description], Last([tbl Follow-up Entries for
Findings].[Auditor Comments]) AS [LastOfAuditor Comments]
FROM ([tbl Comments] LEFT JOIN ([tbl Follow-up status codes] RIGHT
Click to expand...
JOIN
[tbl
Management Responses] ON [tbl Follow-up status codes].[Follow-up status
order] = [tbl Management Responses].[Follow-up status code]) ON [tbl
Comments].[Comment Table counter] = [tbl Management
Click to expand...
Responses].[Comment
Table
counter]) LEFT JOIN [tbl Follow-up Entries for Findings] ON [tbl Management
Responses].[ID for tbl Management Responses] = [tbl Follow-up Entries for
Findings].[ID in tbl Management Responses]
GROUP BY [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], [tbl
Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="fa-lbr-04-03"))
ORDER BY [tbl Comments].[Order of appearance];

Any clue?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Jeff,

One otr two queries does not matter, as far as I get the desired results.
However, right now I am using a single query.

Jeff Boyce said:
From your description, I can't tell if you are trying to make a single query
do all that, or if you have two separate queries. It sounded a little like
you are using a single query.

My earlier suggestion was to create the "Max" query, close it, create a new
query, add the table and the Max query to the new one, joined on the date
field in each (and what ever other fields need to match up).

Regards

Jeff Boyce
<Office/Access MVP>

Barattolo_67 said:
Jeff,

Thanks for your reply. Yes, last means the one with the most recent
follow-up date. What you are suggesting, I have already tried and it did not
work. I have tried to set up a first query that pulls out the record with the
"Max" date in the follow-up entries (there is actually a 'follow-up date
field'). This appears to work, but when I join it back with the other tables,
then the grouping by max does not work no more, and I get all the follow-up
entries again.

Ideas?

"Jeff Boyce" ha scritto:
Define "Last". Are you quite certain that Access uses your definition?

One approach might be to first use a query to obtain the "last" record for
each audit from the follow-up table (you'll need to decide how to define
"last" -- I assume you mean the most-recent-date). Once you have that query
returning a single row per audit, you could join your other tables and that
query together in a new query.

--
Regards

Jeff Boyce
<Office/Access MVP>

In database, I have three tables. THe first one has the audit findings,
the
second one has the responses from the clients, and the third one has the
follow-up work on th implementation of the recommendation. In the last
table
there may be multiple records tied up to that same finding and to that
same
client response. I want to generate a query which will show the audit
finding, the management response, an only the LAST audit follow-up entry.
I
have designed the below query, but it does not work and I do not get the
last
record in the table "follow-up entries for findings".

INSERT INTO [Audit Follow-up Report] ( [No], [Thrust Area], [Title of
Issue], [Risk Severity Code], Recommendation, [Responsible Department],
[Management Action Plan], [Target Completion Date], [Revised Target Date],
[Actual Completion Date], [Follow-up Status], [Change History],
[Management
Status Description], [Auditor's Comments] )
SELECT [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual
Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl
Management
Responses].[Completion Date Change History and Other Comments], Last([tbl
Follow-up Entries for Findings].[Management's Status Description]) AS
[LastOfManagement's Status Description], Last([tbl Follow-up Entries for
Findings].[Auditor Comments]) AS [LastOfAuditor Comments]
FROM ([tbl Comments] LEFT JOIN ([tbl Follow-up status codes] RIGHT JOIN
[tbl
Management Responses] ON [tbl Follow-up status codes].[Follow-up status
order] = [tbl Management Responses].[Follow-up status code]) ON [tbl
Comments].[Comment Table counter] = [tbl Management Responses].[Comment
Table
counter]) LEFT JOIN [tbl Follow-up Entries for Findings] ON [tbl
Management
Responses].[ID for tbl Management Responses] = [tbl Follow-up Entries for
Findings].[ID in tbl Management Responses]
GROUP BY [tbl Comments].[Order of appearance], [tbl Comments].[Cycle
Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual
Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl
Management
Responses].[Completion Date Change History and Other Comments], [tbl
Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="fa-lbr-04-03"))
ORDER BY [tbl Comments].[Order of appearance];

Any clue?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Group By Last 4
Tracking multiple man #'s with one work order # 6
Query Resulting in Duplicate Records 3
Grouping and Aggregating Data in Jet SQL 1
Help using Top Values or DMAX for a report 3
Show Last comment entered 1
Automation of normalization 2
Memo text date 2

Top