Solved

Access 2007, Query, Multiple records with the same Name needs to only return the record with the highest value in another field

Posted on 2008-10-28
8
265 Views
Last Modified: 2013-11-29
I have a database that tracks welds and weld repairs. When the user enters a certain field as rejected they can then select that record from a report as a repair which then duplicates that record and increases the counter in the Weld # postfix field. I need to write a query that will only pull all the records that are listed as rejected and then out of those only display the ones that have the highest postfixes for each group of records with the same Weld #.

Table Data

Weld #   Postfix   info   Comments           Status
   1             -           3          Stuff             Rejected
   1             1          4         Words            Rejected
   1             2          7         Thoughts        Rejected
   1             3          4          Musings         Rejected
   2             -          16        Sorrows         Rejected
   2             1          35       Complaints       Rejected
   2             2          12         Demands        Rejected
   3             -          8           Names            Rejected

The Query Should Return

Weld #   Postfix   info   Comments           Status
   1             3          4          Musings         Rejected
   2             2          12         Demands        Rejected
   3             -          8           Names            Rejected
0
Comment
Question by:FINGERHUT
  • 4
  • 2
  • 2
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22824224
SELECT t1.WeldNum, t1.Postfix, t1.info, t1.Comments
FROM SomeTable t1 INNER JOIN
    (SELECT t2.WeldNum, Max(t2.Postfix) AS MaxPostfix
    FROM SomeTable t2
    GROUP BY t2.WeldNum) AS t3 ON t1.WeldNum = t3.WeldNum AND t1.Postfix = t3.MaxPostfix
ORDER BY t1.WeldNum
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22824236
select * from <your_table>
join (select Weld, max(Postfix) as max_postfix from your_table>) t  on t.Weld = <your_table>. Weld and <your_table>. Postfix = t.max_postfix

0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22824250
forget my post, matthewspatrick's answer is good,

(and I missed group by clause :( )
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22824254
jamesgu,

:)

Regards,

Patrick
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:FINGERHUT
ID: 22824389
Patrick,

In your statement can both t1 and t2 be the same table? I am getting an error saying "syntax error in FROM clause.

Thanks,
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22824530
FINGERHUT said:
>>In your statement can both t1 and t2 be the same table?

Yes, it's a self-join.

>>I am getting an error saying "syntax error in FROM clause.

Please post your SQL so far.
0
 
LVL 1

Author Comment

by:FINGERHUT
ID: 22824977
Here is what I have so far.
 
 First to better explain my code the Creation Order Field is an auto numbering field which helps track the latest record since the post fix is not actually a number but a string which is difficult to sort by.
 
 SELECT t1.[Weld #], t1.[Creation Order], t1.[Weld # Postfix]
FROM  qryRepairLog t1 INNER JOIN
    (SELECT t2.[Weld #], Max(t2.[Creation Order) AS MaxCreationOrder
    FROM qryRepairLog t2
    GROUP BY t2.[Weld #]) AS t3 ON t1.[Weld #] = t3.[Weld #] AND t1.[Creation Order] = t3.MaxCreationOrder
ORDER BY t1.[Weld #]

Thanks,
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 22825885
Looks like you left out a ]...


SELECT t1.[Weld #], t1.[Creation Order], t1.[Weld # Postfix]
FROM  qryRepairLog t1 INNER JOIN
    (SELECT t2.[Weld #], Max(t2.[Creation Order]) AS MaxCreationOrder
    FROM qryRepairLog t2
    GROUP BY t2.[Weld #]) AS t3 ON t1.[Weld #] = t3.[Weld #] AND t1.[Creation Order] = t3.MaxCreationOrder
ORDER BY t1.[Weld #]


BTW, it is a really, really bad idea to use anything besides letters, numerals, and underscores as names of tables,
columns, and queries...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now