Group By Last

G

Guest

I have designed an append query originating from 4 diffrent tables, and have
set the Group By for two fields to last. However, this does not work, and
when I run the query, I do not get the last record. Here follows the query in
SQL view:

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],
First([tbl Comments].Recommendation) AS FirstOfRecommendation, [tbl
Management Responses].[Responsible Department], First([tbl Management
Responses].[Management Action Plan]) AS [FirstOfManagement 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], First([tbl
Follow-up Entries for Findings].[Management's Status Description]) AS
[FirstOfManagement's Status Description], First([tbl Follow-up Entries for
Findings].[Auditor Comments]) AS [FirstOfAuditor 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
Management Responses].[Responsible Department], [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-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];

Any clue of what might be going on?
 
A

Allen Browne

Did you mean MAX instead of LAST?

Most of the time Last is both useless and ineffient. Last just means, "After
you have wasted time reading all the records, give the the value from the
last one." Unless it is also sorted on this field, there is no guarantee
that this will be the highest value. Max gives the highest value if that's
what you intended.

I don't really have a clue about what's going on in your query anyway, as I
couldn't find the Last in your query statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Barattolo_67 said:
I have designed an append query originating from 4 diffrent tables, and
have
set the Group By for two fields to last. However, this does not work, and
when I run the query, I do not get the last record. Here follows the query
in
SQL view:

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],
First([tbl Comments].Recommendation) AS FirstOfRecommendation, [tbl
Management Responses].[Responsible Department], First([tbl Management
Responses].[Management Action Plan]) AS [FirstOfManagement 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], First([tbl
Follow-up Entries for Findings].[Management's Status Description]) AS
[FirstOfManagement's Status Description], First([tbl Follow-up Entries for
Findings].[Auditor Comments]) AS [FirstOfAuditor 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
Management Responses].[Responsible Department], [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-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];

Any clue of what might be going on?
Click to expand...
 
G

Guest

Allen,
Thanks for your reply. No, I meant LAST or FIRST (it does not work in any
case). Here's what's going on. I work in Audit. In the database there is one
record relating to an audit finding and its recommendation. Then, in another
table, there is follow-up work (on the implementation of the audit
recommendation). What I need is a query that will show the record with the
audit issue and recommendation, and ONLY the LAST follow-up entry linked to
that audit issue and recommendation. Any clue?

"Allen Browne" ha scritto:
Did you mean MAX instead of LAST?

Most of the time Last is both useless and ineffient. Last just means, "After
you have wasted time reading all the records, give the the value from the
last one." Unless it is also sorted on this field, there is no guarantee
that this will be the highest value. Max gives the highest value if that's
what you intended.

I don't really have a clue about what's going on in your query anyway, as I
couldn't find the Last in your query statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Barattolo_67 said:
I have designed an append query originating from 4 diffrent tables, and
have
set the Group By for two fields to last. However, this does not work, and
when I run the query, I do not get the last record. Here follows the query
in
SQL view:

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],
First([tbl Comments].Recommendation) AS FirstOfRecommendation, [tbl
Management Responses].[Responsible Department], First([tbl Management
Responses].[Management Action Plan]) AS [FirstOfManagement 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], First([tbl
Follow-up Entries for Findings].[Management's Status Description]) AS
[FirstOfManagement's Status Description], First([tbl Follow-up Entries for
Findings].[Auditor Comments]) AS [FirstOfAuditor 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
Management Responses].[Responsible Department], [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-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];

Any clue of what might be going on?
Click to expand...
Click to expand...
 
A

Allen Browne

Your query is grouping on 11 fields.
If you want the lowest value on any record in the grouping use Min.
If you want the highest value on any record in the grouping use Max.
If you don't care which value gets returned, use First so that Access can
return the first thing it finds in that grouping.

If you are attempting to do something other than that, use one of the 4
methods described in this article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The query statement (reformatted) was:

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],
First([tbl Comments].Recommendation) AS FirstOfRecommendation,
[tbl Management Responses].[Responsible Department],
First([tbl Management Responses].[Management Action Plan])
AS [FirstOfManagement 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],
First([tbl Follow-up Entries for Findings].[Management's Status
Description])
AS [FirstOfManagement's Status Description],
First([tbl Follow-up Entries for Findings].[Auditor Comments])
AS [FirstOfAuditor 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 Management Responses].[Responsible Department],
[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-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];
 
G

Guest

Allen,

Many thank. The article you pointed to me is useful.

Regards

Allen Browne said:
Your query is grouping on 11 fields.
If you want the lowest value on any record in the grouping use Min.
If you want the highest value on any record in the grouping use Max.
If you don't care which value gets returned, use First so that Access can
return the first thing it finds in that grouping.

If you are attempting to do something other than that, use one of the 4
methods described in this article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The query statement (reformatted) was:

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],
First([tbl Comments].Recommendation) AS FirstOfRecommendation,
[tbl Management Responses].[Responsible Department],
First([tbl Management Responses].[Management Action Plan])
AS [FirstOfManagement 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],
First([tbl Follow-up Entries for Findings].[Management's Status
Description])
AS [FirstOfManagement's Status Description],
First([tbl Follow-up Entries for Findings].[Auditor Comments])
AS [FirstOfAuditor 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 Management Responses].[Responsible Department],
[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-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];

Barattolo_67 said:
Allen,
Thanks for your reply. No, I meant LAST or FIRST (it does not work in any
case). Here's what's going on. I work in Audit. In the database there is
one
record relating to an audit finding and its recommendation. Then, in
another
table, there is follow-up work (on the implementation of the audit
recommendation). What I need is a query that will show the record with the
audit issue and recommendation, and ONLY the LAST follow-up entry linked
to
that audit issue and recommendation. Any clue?

"Allen Browne" ha scritto:
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

Last Record 4
QryDef 3
Before update event 2
Query Resulting in Duplicate Records 3
group by and count 1
Copy Cells Based on Criteria in VBA 3
Query is too complex 2
Help using Top Values or DMAX for a report 3

Top