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
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 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