Summary/Total Report (97 SR2)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote