Solved: Advanced Query

[jauner]

I have a query that currently shows an audittotal and I need to have it only show me the highest audit total if the same account is audited twice.

Here is the sql for it:

SELECT DISTINCT [tblAudit Detail].[Audit ID], [tblAudit Detail].[Account Number], [tblAudit Detail].[Account Name], [tblAudit Detail].[Account Type], [tblAudit Detail].SIC, [tblAudit Detail].[Time on Account], [tblAudit Detail].Consultant, [tblAudit Detail].AccountConsultant, [tblAudit Detail].[Managing Consultant], [tblAudit Detail].Auditor, [tblAudit Detail].[Audit Date], tblAssessment.[Assmnt Item 1], tblAssessment.[Assmnt Score 1], tblAssessment.[Assmnt Item 2], tblAssessment.[Assmnt Score 2], tblAssessment.[Assmnt Item 3], tblAssessment.[Assmnt Score 3], tblAssessment.[Assmnt Item 4], tblAssessment.[Assmnt Score 4], tblAssessment.[Assmnt Item 5], tblAssessment.[Assmnt Score 5], tblAssessment.[Assmnt Item 6], tblAssessment.[Assmnt Score 6], tblAssessment.[Assmnt Item 7], tblAssessment.[Assmnt Score 7], tblAssessment.[Assmnt Item 8], tblAssessment.[Assmnt Score 8], tblAssessment.[Assmnt Comments], [tblLP Planning].[LP Planning Item 1], [tblLP Planning].[LP Planning Score 1], [tblLP Planning].[LP Planning Item 2], [tblLP Planning].[LP Planning Score 2], [tblLP Planning].[LP Planning Item 3], [tblLP Planning].[LP Planning Score 3], [tblLP Planning].[LP Planning Item 4], [tblLP Planning].[LP Planning Score 4], [tblLP Planning].[LP Planning Item 5], [tblLP Planning].[LP Planning Score 5], [tblLP Planning].[LP Planning Item 6], [tblLP Planning].[LP Planning Score 6], [tblLP Planning].[LP Planning Item 7], [tblLP Planning].[LP Planning Score 7], [tblLP Planning].[LP Planning Comments], tblMLS.[MLS Item 1], tblMLS.[MLS Score 1], tblMLS.[MLS Item 2], tblMLS.[MLS Score 2], tblMLS.[MLS Item 3], tblMLS.[MLS Score 3], tblMLS.[MLS Item 4], tblMLS.[MLS Score 4], tblMLS.[MLS Item 5], tblMLS.[MLS Score 5], tblMLS.[MLS Item 6], tblMLS.[MLS Score 6], tblMLS.[MLS Item 7], tblMLS.[MLS Score 7], tblMLS.[MLS Item 8], tblMLS.[MLS Score 8], tblMLS.[MLS Item 9], tblMLS.[MLS Score 9], tblMLS.[MLS Item 10], tblMLS.[MLS Score 10], tblMLS.[MLS Comments], tblResources.[Resource Item 1], tblResources.[Resource Score 1], tblResources.[Resource Item 2], tblResources.[Resource Score 2], tblResources.[Resource Item 3], tblResources.[Resource Score 3], tblResources.[Resource Item 4], tblResources.[Resource Score 4], tblResources.[Resource Item 5], tblResources.[Resource Score 5], tblResources.[Resource Item 6], tblResources.[Resource Score 6], tblResources.[Resource Item 7], tblResources.[Resource Score 7], tblResources.[Resource Item 8], tblResources.[Resource Score 8], tblResources.[Resource Item 9], tblResources.[Resource Score 9], tblResources.[Resource Item 10], tblResources.[Resource Score 10], (tblAssessment![Assmnt Score 1]+tblAssessment![Assmnt Score 2]+tblAssessment![Assmnt Score 3]+tblAssessment![Assmnt Score 4]+tblAssessment![Assmnt Score 5]+tblAssessment![Assmnt Score 6]+tblAssessment![Assmnt Score 7]+tblAssessment![Assmnt Score 8])*30 AS AssmntTotal, ([tblLP Planning]![LP Planning Score 1]+[tblLP Planning]![LP Planning Score 2]+[tblLP Planning]![LP Planning Score 3]+[tblLP Planning]![LP Planning Score 4]+[tblLP Planning]![LP Planning Score 5]+[tblLP Planning]![LP Planning Score 6]+[tblLP Planning]![LP Planning Score 7])*30 AS LPPlanTotal, (tblMLS![MLS Score 1]+tblMLS![MLS Score 2]+tblMLS![MLS Score 3]+tblMLS![MLS Score 4]+tblMLS![MLS Score 5]+tblMLS![MLS Score 6]+tblMLS![MLS Score 7]+tblMLS![MLS Score 8]+tblMLS![MLS Score 9]+tblMLS![MLS Score 10])*30 AS MLSTotal, (tblResources![Resource Score 1]+tblResources![Resource Score 2]+tblResources![Resource Score 3]+tblResources![Resource Score 4]+tblResources![Resource Score 5]+tblResources![Resource Score 6]+tblResources![Resource Score 7]+tblResources![Resource Score 8]+tblResources![Resource Score 9]+tblResources![Resource Score 10])*30 AS ResourceTotal, [AssmntTotal]+[LPPLanTotal]+[MLSTotal]+[ResourceTotal] AS AuditTotal, tblResources.[Resource Comments]
FROM ((([tblAudit Detail] INNER JOIN tblAssessment ON [tblAudit Detail].[Audit ID] = tblAssessment.[Audit ID]) INNER JOIN [tblLP Planning] ON [tblAudit Detail].[Audit ID] = [tblLP Planning].[Audit ID]) INNER JOIN tblMLS ON [tblAudit Detail].[Audit ID] = tblMLS.[Audit ID]) INNER JOIN tblResources ON ([tblAudit Detail].[Audit ID] = tblResources.[Audit ID]) AND (tblAssessment.[Audit ID] = tblResources.[Audit ID]);

I apologize for the length. The field I need to have the highest one on is AuditTotal. So basically if the account number is the same, I need to show the highest audit total.

Please help.

:bug:

[jauner]

I did figure this out. I ended doing another query to filter out the data.

[Norie]

Jauner

I'm glad you've solved the problem, but kind of off topic why do you have all these fields?

Do you really need 8 seperate fields for Assmnt Score, Assmnt Item etc?

[jauner]

I am maintaining a database for a customer and this is the way they originally designed it and didnt want it rewritten

[geekgirlau]

Sounds like a one page memo presented to your customer on the benefits of normalisation wouldn't go astray :creator: