Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003

    Unanswered: SQL troubles please help....

    .Source = "select Development.[KinderNumber],Development.[Date],Development.[Client],Development.[MFG],Development.[Fixture],Development.[Dimensions (HxWxD)],Development.[MFG Price],Development.[Item Number], developmentpictures.[Image], developmentpictures.[Revision_ Letter]FROM developmentpictures, Development WHERE developmentpictures.[KinderNum]= Development.[KinderNumber]"

    There is my SQL statement that looks to 2 tables and one table is info and the other houses REVISION LETTER and PICTURE.

    What I need to do.....Is return the HIGHEST LETTER in the REVISION_Letter column for each ID# as of now I get all ID # 's with all revisions.

    I need to set up a SQL statement that will return the highest revision letter for each ID#.

    I tried to SELECT MAX(developmentpictures.[Revision_ Letter]) but it gave me a error?

    Please Help!!!!

  2. #2
    Join Date
    Dec 2001
    The "Top" predicate might help...

    Select TOP 1 MyField from MyTable order by MyField;

    Hope this helps,
    Peter De Baets
    Peter's Software - Microsoft Access Tools for Developers

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    try this --
    select D.[KinderNumber]
         , D.[Date]
         , D.[Client]
         , D.[MFG]
         , D.[Fixture]
         , D.[Dimensions (HxWxD)]
         , D.[MFG Price]
         , D.[Item Number]
         , dp.[Image]
         , dp.[Revision_ Letter]
      from Development D
      join developmentpictures dp
        on D.[KinderNumber] = dp.[KinderNum]
     where dp.[Revision_ Letter] =
           ( select max([Revision_ Letter])
               from developmentpictures
              where [KinderNum] = dp.[KinderNum] )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts