?
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
Medium Priority
?
273 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 93

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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 93

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 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 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