Totals needed on multiple fields

[RenaG]

I am using Access 2007.

I have the following table created by a query:
ProgramName
Territory
PatientName
PatientID
Date
Ethnicity
Race

I need to create a report that is grouped by ProgramName, then Territory (which is either 'rural' or 'urban'). Something like this:

      Race                       Ethnicity            Patient          PatID       Date 
[B]Thoracic Oncology Program[/B]
  Rural
      Asian                    Non-Hispanic          Judy Jones        MR111       9/22/2011
      Asian                    Non-Hispanic          Tammy Jones       MR111       9/22/2011
      White/Caucasian          Non-Hispanic          Betty Jones       MR1234      9/21/2011
   Asian - 2 
   White/Caucasian - 1
   Non-Hispanic - 3
  Urban
      Asian                    Non-Hispanic          Sandy Jones      MR185       9/22/2011
      White/Caucasian          Hispanic              Andy Jones       MR165       9/21/2011
      White/Caucasian          Non-Hispanic          Trudy Jones      MR165       9/21/2011
   Asian - 1
   White/Caucasian - 2
   Hispanic - 1
   Non-Hispanic - 2
Total Thoracic:
   Asian - 3
   White/Caucasian - 3

[B]Brain Tumor Program[/B]
    blah blah blah

[B][I]Grand total:[/I][/B]
   Asian - 4
   White/Caucasian - 16
   Non-Hispanic - 15
   Hispanic - 5

I hope that paints a good and understandable picture for you.

Any suggestions on how to get this done?

TIA!
~RLG

 

[vbaInet]

[B]Brain Tumor Program[/B]
    blah blah blah

That's not a very thorough Brain Tumor program

[B][I]Grand total:[/I][/B]
   Asian - 4
   White/Caucasian - 16
   Non-Hispanic - 15
   Hispanic - 5

Create a queries to perform a count grouped on Race and the other grouped on Ethnicity. Display those queries in your report using subreports.

 

[RenaG]

That's not a very thorough Brain Tumor program

LOL! You are right .

I am off on another project right now but wanted to let you know that I got your post. I will try it as soon as I am able and post a reply.

Have a good weekend!

~RLG

 

[vbaInet]

Have a good weekend too!

 

[RenaG]

I'm baaaaaaack!!

Your suggestion was an excellent one! I created two crosstab queries and they work great - EXCEPT...

The main report is extracted using beginning and ending date parameters. When I try to put a Where with

>= [Forms]![frmRaceEthnicityDateParm]![BegDate] And 
<= [Forms]![frmRaceEthnicityDateParm]![EndDate]

I get this error:

The Microsoft Office Access database engine does not recognize '[Forms]![frmRaceEthnicityDateParm]![BegDate]' as a valid field
name or expression.

How do I added this parameter to the two crosstab queries or subreports?

TIA!
~RLG

 

[RenaG]

Got the answer to this question on this post:

http://www.access-programmers.co.uk/forums/showthread.php?p=1130913#post1130913

~RLG

 

[vbaInet]

There's nothing wrong with using Eval(), I just said it wouldn't be my preferential method.

When you added the reference to the Parameters list, did you select the appropriate Data Type?

 

[RenaG]

This is what I put in the Parameters window:

[Forms]![frmRaceEthnicityDateParm]![BegDate]

I selected Date/Time type.

Thanks!
~RLG

 

[vbaInet]

If that doesn't work then I suspect your query is too complex.

 

[RenaG]

I just tried it again and didn't get the same message. So I would like to keep playing with this. Assuming that the query isn't too complex, how do I use a parameter?

Thanks!
~RLG

PS - what time is there?

 

[vbaInet]

What do you mean by how do you use a parameter? Explain a bit more.

It's a UK forum so the timestamp on the posts "should" be U.K. time, however I've seen some weird behaviour many times. It's 15:50 hrs now.

 

[RenaG]

The date time stamp says 8:49 am.

I created a parameter. What is the next step? Or is that it? How is the parameter linked to the query and how does it know what field to parameter is to be applied to? Sorry. I know nothing about parameters so I'm not even sure how to ask my question.

~RLG

 

[RenaG]

After doing some reading, I think I understand about the parameters. It is just a way of declaring them so-to-speak, right? I added them on the crosstab query. Then changed the WHERE clause in the query to just be

Between [Forms]![frmRaceEthnicityDateParm]![BegDate] And [Forms]![frmRaceEthnicityDateParm]![EndDate]

When I ran the report I got the error:

You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport.
Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property

I did have columnHeadings set. I changed the WHERE to use the Eval() function, still got the same error. Removed the Parameters and the error went away.

It's working so I think I will stick with the adage:

If it ain't broke don't fix it.

I've got another issue but I'll keep poking around the forum to see if it has been address someplace. Otherwise - a new thread!

Thanks for all your time and help!

~RLG

 

[vbaInet]

After doing some reading, I think I understand about the parameters. It is just a way of declaring them so-to-speak, right?

Pretty much.

So if you had removed the Eval() and then put in the parameters should have worked (but that all depends on your query). Try it out and see if that works in your case.

Good luck with the rest of your project!