Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summary/Total Report (97 SR2)

    I need to create a report/query that will subtotal, and I'm having a hard time with this. This is a database of fish our biologists examine. Each biologist might examine 300 fish in one day, and there are 4 species of fish. I have a field for the biologists' initials and a field with the name of the species examined at a specific date/time. I want the report to total the number of each species by biologist. So, on 12/29/1999 mm examined 44 coho, 100 chinook, etc. But, I don't have a clue how to start this. Want me to attach the db so you can look at it?? I can zip it.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Summary/Total Report (97 SR2)

    What you are describing sounds like a perfect use of a crosstab query. When you create a new query, there is a wizard for creating crosstabs.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary/Total Report (97 SR2)

    I don't know if there are other ways around this, but it sounds to me like you might need to use Pivot Tables to get this sort of info. I don't know if you can get pivot tables to display in an Access report, but you certainly can get them onto a form.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary/Total Report (97 SR2)

    BINGO! I've never used that before. It's way cool! Thanks much!!

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary/Total Report (97 SR2)

    Well, I thought the cross tab query was perfect, but it's not quite. It's very close. But, I want the dates to show by dateFor instance, instead of counting the number done total per biologist/examiner, I want to have something that shows something like this:
    examiner Date Coho Chinook Sockeye
    mm 9/30 34 6 12
    mm 10/2 15 4 22
    cl 9/30 12 18 4

    See?

    This is what I have now
    Attached Images Attached Images

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary/Total Report (97 SR2)

    From the query in Desgin View, add the Date field again. This time, use the Row Heading option in the Crosstab cell. It should show up in the query results.

    HTH,

    Tom

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary/Total Report (97 SR2)

    Amazing! Thanks for the quick response. That's perfect. I didn't realize I could use the same field twice for different purposes. The possibilities are endless! This is fun. Thanks.

Posting Permissions

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