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
267 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22824254
jamesgu,

:)

Regards,

Patrick
0
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

770 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