Results 1 to 3 of 3
  1. #1
    mslieder is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2005
    Posts
    10

    Query - one part to many revs - how to get the latest rev

    I have a query that gathers data from 2 tables. Table A contains a part number. Table B contains revision letters. So. . .for every part number, there may be 3 revision letters.

    Example:

    Table A
    123456


    123457
    123458

    Table B
    123456 A
    B
    123457 A
    B
    C
    123458 A
    B
    C
    D

    The query simply pulls data from Table A (the part number) and Table B (the revision letter). I want the query to show only the latest revision letter for each part number.

    HELP! I'm really hoping someone can help. It's probably a simple solution of which I can't figure out!

    Thanks in advance,
    mslieder :wink:

  2. #2
    StepUP is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Dec 2005
    Posts
    99
    Try this:

    SELECT [Table A].[part number], Max([Table B].[Revision]) AS MaxOfRevision
    FROM [Table A] INNER JOIN [Table B] ON [Table A].[part number] = [Table B].[part number]
    GROUP BY [Table A].[part number];

  3. #3
    mslieder is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2005
    Posts
    10

    It worked!

    Thank you!

    Quote Originally Posted by StepUP
    Try this:

    SELECT [Table A].[part number], Max([Table B].[Revision]) AS MaxOfRevision
    FROM [Table A] INNER JOIN [Table B] ON [Table A].[part number] = [Table B].[part number]
    GROUP BY [Table A].[part number];
    mslieder

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search any field and part thereof
    By Johan in forum Programming
    Replies: 0
    Last Post: 09-08-2008, 02:18 AM
  2. Search any field and part thereof
    By Johan in forum Forms
    Replies: 0
    Last Post: 09-03-2008, 08:01 AM
  3. Replies: 0
    Last Post: 03-05-2007, 08:04 PM
  4. Multiple Part Number Look=up
    By nywi6100 in forum Reports
    Replies: 0
    Last Post: 09-27-2006, 11:38 AM
  5. how do I create custom part numbers?
    By Sinatra Fan in forum Database Design
    Replies: 1
    Last Post: 09-01-2006, 04:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums