Results 1 to 9 of 9
  1. #1
    kdbailey is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    228

    Using an iif statement with a calculated field

    In one field I am counting [Claim Number]. In another field I would like it to either show up as its original [Inspection] or "Miscellaneous", depending how many the count is (x).

    My new [Inspection] field:

    Inspection: IIf(Count([Claim Number])<2, "Miscellaneous", [Returns Table]![Inspection])

    The total is (Group By)



    I am receiving an error reading "Cannot have aggregate function in GROUP BY clause". How can I get this to work?

    Kris

  2. #2
    kdbailey is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    228
    Keep in mind that I do have another field that is counting the claim numbers.

    Cases: Claim Number (Totaled by Count)

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Change it to EXPRESSION not GROUP BY

  4. #4
    kdbailey is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    228
    I attempted this, but was unsuccessful because I had to count the initial group before I was able to relabel them. An additional table was required.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Maybe do the aggregate query then use it as source for another query - can even nest queries into one SQL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    kdbailey is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    228
    What I did was aggregate it into a table, and then do a simple update query. Is it possible to nest the two together?

    Code:
    DoCmd.RunSQL "DELETE [Temp Table].* FROM [Temp Table];"
    DoCmd.RunSQL totalcht
    DoCmd.RunSQL "UPDATE [Temp Table] SET [Temp Table].Inspection = 'Miscellaneous' WHERE ((([Temp Table].Cases)<" & minclaims & "));"
    here totalcht refers to a long string of previously determined sql declarations

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    What is totalcht? If it is just a SELECT query, there is no reason to 'run' a SELECT query.

    The nesting you reference is an INSERT SELECT sql. I would not save the aggregate data to a table. I would just run the aggregate query when needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    kdbailey is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    228
    totalcht is an append query to the table [Temp Table].

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Cannot nest action queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 4
    Last Post: 03-20-2014, 03:52 PM
  2. If then statement on calculated field
    By blakej in forum Access
    Replies: 9
    Last Post: 03-17-2014, 01:12 PM
  3. Replies: 2
    Last Post: 12-15-2013, 02:29 AM
  4. Replies: 1
    Last Post: 04-21-2013, 03:20 PM
  5. Replies: 3
    Last Post: 02-13-2013, 10:15 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