Counting...

[ErikZ]

Well it seemed simple at first.
I have States and each state has multiple regions.
My database has a list of people, each in a state and a region.
I have to group by the states, and then the regions in the states. Then have a count of those people. So it will look like this:

IL
01 16
02 3
04 32
WY
02 13
04 12

Getting reports to group people into these catagories is easy. Getting reports to NOT print their names and just count is driving me crazy! What are the magic words?

Later,
ErikZ

 

[Qaroven]

How bout do the grouping in the report query?
What tables do you have?
Ben
+61 403 395 052

 

[RickSpr]

If you don't want it in a report, you can use a totals query. Create a query, and add your state, region, and people tables. Choose View>Totals from the menu. Next add the state, region, and any field (except the *) from the people. The totals line in the grid will display Group By for each of these. Change the totals line for the people field to Count. You'll probably also want to set the Sort parameters for the state and region. When you switch the query to Datasheet View, you'll have what you want.

The corresponding SQL statement has Count(field name) as one of the fields in the SELECT clause, and a GROUP BY clause specifying the grouping. Rick Sprague

 

[ErikZ]

One table. Has Name, state, region.

There are two reports. The first one is easier, group by states, then region. At the bottom, total all the regions for the state.

The second report groups all the states into three groups. (Determined by the company). If have to put each state in one of these groups and then list the totals at the end.

I was able to do the region totals from Rick's instructions, but trying the same thing using the "State" column (To get a total for the state) gave me the exact same output as the region sorting.

And I can't sit down and work/think on it cause my boss has me flying all over the place doing other stuff! Arg!

Just lock me in a closet somewhere so I can think please.

Later,
ErikZ

 

[RickSpr]

I don't quite understand what you're doing in the second report. How will you "put each state into one of these groups"? And how does the company figure in?

In the next paragraph, where you're trying to get totals by state, what groups have you defined in your report definition? If you're actually getting region totals, have you removed the region from the Sorting and Grouping dialog? Is this the same report as that mentioned above? Rick Sprague