Results 1 to 3 of 3
  1. #1
    turnbuk is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    10

    IIF Expression - No Repeat Please

    I have a query where field 1 is grouped by Exch_Name and where the Exch_Name field is taken from query [Time - Data].



    Field 2 uses this IIF expression:

    IIf([TIME - DATA!Exch_Name]=[TIME 00-01 COUNT]![Exch_Name],[TIME 00-01 COUNT]![CountOfExch_Name],"0")

    My goal is to keep the group of Exch_Name the same in field 1 no matter what and for field 2 to populate only if there is a number present from [Time 00-01 Count].

    The result right now is this:

    Name-1 | 0
    Name-2 | 0
    Name-2 | 1
    Name-3 | 0

    Because it found a number for Name-2 from [TIME 00-01 COUNT]![CountOfExch_Name] it added another Name-2 below the original group listing from field 1 and put the counts. How can I get the expression to not repeat if it found a number?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    make the query an aggregate query (click the SIGMA button in the query design view) in the totals line make sure your first field is GROUP BY and your second field is MAX. Then it will only display the maximum value which will give you this result:

    Name-1 | 0
    Name-2 | 1
    Name-3 | 0

  3. #3
    turnbuk is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    10
    I actually just figured that out then noticed I had a response to my question and BAM! it was exactly what I had done. It works! MAX was the key.

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

Similar Threads

  1. Enter repeat identical data
    By wdrspens in forum Forms
    Replies: 1
    Last Post: 03-16-2011, 06:08 AM
  2. Clearing old table for repeat import
    By jasonbarnes in forum Import/Export Data
    Replies: 4
    Last Post: 02-13-2011, 04:00 AM
  3. Looking for help with an expression
    By CoachBarker in forum Queries
    Replies: 3
    Last Post: 11-25-2010, 05:17 AM
  4. Problem with Trying to Repeat Records in VBA
    By P5C768 in forum Programming
    Replies: 9
    Last Post: 09-22-2010, 12:09 PM
  5. Expression value
    By KWarzala in forum Reports
    Replies: 0
    Last Post: 06-15-2010, 12:35 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 - Senior Forums