Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table/3=Report (2003)

    I'm trying to divide a table into four categories based on three fields and put the totals for each field all on a single report. This is easy to do using four queries, but I can't figure out how to tie them all together in a report.
    If it helps,
    The first category is comprised of specific items (e.g. between 0001 and 0004 and between 0089 and 0100)
    The other three categories do not have those specific items, but are divided based on two check boxes.
    How should I approach this?
    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table/3=Report (2003)

    Two check boxes offer 4 possible combinations (On/On, On/Off, Off/On and Off/Off) so you'd seem to have 5 categories (the first one pluse the 4 possible combinations of the check boxes). Or is there some extra info you haven't told us about?

  3. #3
    New Lounger
    Join Date
    Sep 2006
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table/3=Report (2003)

    Nope, you're right. Four combinations, but I'm only concerned with three of them. The fourth (which would be On/On) isn't relevant.
    Sorry,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table/3=Report (2003)

    You could create a query and add a calculated column:

    Category: IIf([Field1] Between "0001" And "0004" Or [Field1] Between "0089" and "0100", 1, IIf([Field2]=False And [Field3]=False, 2, IIf([Field2]=True And [Field3]=False, 3, 4)))

    Replace Field1, Field2 and Field3 with the appropriate field names. You can, of course, change the return values 1, 2, 3 and 4 around to suit your needs.
    You can use Category to group the report on.

  5. #5
    New Lounger
    Join Date
    Sep 2006
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table/3=Report (2003)

    That worked great, thanks.

    The next step is giving me real troubles -- I need to adjust the sum value of categories 2, 3 , and 4, but not of 1. Is there some way to rig that up in the report?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table/3=Report (2003)

    You could create a totals query based on the one I suggested in my previous reply, grouped by Category, with a condition <>1 on that field.

  7. #7
    New Lounger
    Join Date
    Sep 2006
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks

    You're amazing.

Posting Permissions

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