Results 1 to 3 of 3
  1. #1
    0REDSOX7 is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    6

    Question Identify and Assign Value to Minimum

    Hello all,

    I need some help.

    What I am doing is trying to have access identify and label various lanes of transit.

    So, for example. I have the below...

    Origin Destination Via Transit Time


    Hong Kong New York East Coast 38
    Hong Kong New York West Coast 32
    China New York East Coast 34
    China New York West Coast 29

    Is there a way in Access to have it identify that Hong Kong to NY via West Coast is the "DEFAULT" where HKG to NY via East Coast would be "ALTERNATE" ?? And then do the same for the next origin? The table I have now has over 71 different origins but I need it look at each origin indepentently.

    Im sure there is a way in SQL - but I'm not good at it and have been working on this for a few days without any success.

    Thanks guys!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I think this will work:

    SELECT Table1.Origin, Table1.Destination, Max(Table1.[Transit Time]) AS TransTime, Table1.Via, IIf(DMax("[Transit Time]","[Table1]","[Origin] = '" & [Origin] & "' AND [Destination] = '" & [Destination] & "'")=[Transit Time],"DEFAULT","ALTERNATE") AS Preference
    FROM Table1
    GROUP BY Table1.Origin, Table1.Destination, Table1.Via, IIf(DMax("[Transit Time]","[Table1]","[Origin] = '" & [Origin] & "' AND [Destination] = '" & [Destination] & "'")=[Transit Time],"DEFAULT","ALTERNATE");

    You would need to change Table1 with the name of your table.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. minimum number in report
    By rmiell in forum Reports
    Replies: 2
    Last Post: 10-10-2012, 02:33 PM
  2. Alert user when inventory below minimum
    By Micky in forum Access
    Replies: 5
    Last Post: 04-06-2012, 03:14 PM
  3. Criteria for a minimum itself
    By hawkins in forum Access
    Replies: 17
    Last Post: 07-27-2011, 12:57 PM
  4. one-to-many-or-none table relationship? (minimum cardinality)
    By racecar333 in forum Database Design
    Replies: 2
    Last Post: 02-24-2011, 07:11 AM
  5. Can't see minimum or maximum buttons?!?
    By Felix_too in forum Forms
    Replies: 2
    Last Post: 12-29-2010, 10:04 AM

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