Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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

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
FINGERHUT
Asked:
FINGERHUT
  • 4
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
jamesguCommented:
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
 
jamesguCommented:
forget my post, matthewspatrick's answer is good,

(and I missed group by clause :( )
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Patrick MatthewsCommented:
jamesgu,

:)

Regards,

Patrick
0
 
FINGERHUTAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
FINGERHUTAuthor Commented:
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
 
Patrick MatthewsCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now