Counting Problem

hi everybody-

i'm having a counting problem i hope you guys and gals could give me
some help with. i have a query that retrieves a bevy of information
from several different tables.

first let me give you a little background. this database is kind of
like a human resources database. it collects info about people, where
they work, and if they have any work-related issues where they work.

i have a table with people info in it, i.e. name, address, etc... i
have another table including info about where they work, i.e. division
name, address, telephone, etc...

each division is part of a bigger company. for example, "x" is a
division of company "y." each company can have many different
divisions. for example, "company y" can have division a, division b,
and division x.

here are some sample examples of my tables:

Table Person
id = primary key
last name
first name
etc...

Table Company
id = primary key; number; foreign key in Division table
name = company name

Table Division
id = primary key; number
name = name of division
companyid = foreign key to Company table


Company A has these three divisions: gas, oil, rubber
Company B has four divisions: parts, tires, motors, glass

right now my so-called "Division" table has 3 fields: id, division,
company.

id division company
1 gas Company A
2 oil Company A
3 rubber Company A
3 parts Company B
4 tires Company B
5 motors Company B
6 glass Company B

etc....


i'm running a query for a report that 1st groups Companies, then their
divisions, and then the people in each division. i'm getting some of
the results i want, but when i want to count the number of Companies,
i'm getting all of them that return in the query, not just the total
of group headings.

for example, here is what my report looks like:


Company A (1st grouping level based on "company" field from my table)
Gas (2nd grouping level based on "division" from the same table)
John Doe (from a related table in the query)
James Brown
Oil
Mindy Franlin
Taylor Dent
John Hamilton
Rubber
Ellen March

Company B
Tires
Wendy Morris
Danny Bonaduci
Guy Pierce
Motors
Jill Johnson
Mike Jones
Glass
Andy Moore
Tom Reese

etc...

in the report footer, i use the count function to count the number of
different companies. i get 6 for the number of companies. i'm using a
text box with the control source set to: "=Count([company])" without
the quotes. i'm not using "=Count(*)"

3 from Company A because it is adding: Gas, Oil, and Rubber.
3 from Company B beczause it is adding: Ties, Motors, and Glass.

i tried putting the count function in the Company footer, but it is
still counting the number of divisions. for example. i'm getting a
total of 3 in the "Company A" group footer and 3 in the "Company B"
group footer. i know why this is happening, but i don't know how to
only count each Company once.

I have my tables set up this way to separate the companies from their
division because i need to know which division is part of which
company.

how can i count the number of distict companies that are retrieved in
my query without counting the total number of times a company is
returned? does that make sense? i want the total to come out to "2"
(once for Company A and once for Company B) instead of 6. is there a
keyword like "distinct" i should use? or should i go back to the
query, add the "company" field a qnd time, then use one of the totals
functions?

i haven't tried any other counts yet, i.e. the total number of people
in each division and then the total number of people in each company,
but i'm hoping they'll work.

thanks for reading my long post! thans in advance for your help/
advice! i truly appreciate it!

take care,

megan

re: Counting Problem

meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0408031417.78976437@posting.google. com>...[color=blue]
> hi everybody-
>
> i'm having a counting problem i hope you guys and gals could give me
> some help with. i have a query that retrieves a bevy of information
> from several different tables.
>[/color]

Megan,

You did a great job explaining what your problem is. I checked
reports I created in the past that grouped on multiple fields and I'm
not happy with what I did in those cases. When I couldn't use SUM or
COUNT directly I created Public functions in a module that would
ensure I got the total or count that I wanted. For example:

Control Source for the txtNumberOfCompanies textbox in the Footer:

=CountSQLRecords("SELECT CompanyName FROM ... GROUP BY CompanyName;")

Note that the SQL string used to get the count is somewhat similar to
the one used for the RecordSource except you are now free to group the
same way the report groups in order to get your count. You can also
test out your SQL strings first by saving your report's RecordSource
as a query and making sure the groupings give you what you want. I
look forward to seeing if others have solved this problem so that I
can simplify my reports. P.S., using 'Name' as the name of a field
could possibly confuse Access.

James A. Fortune

re: Counting Problem

jafortun@oakland.edu (James Fortune) wrote in message news:<a6ed3ce7.0408040108.434c7b94@posting.google. com>...[color=blue]
> meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0408031417.78976437@posting.google. com>...[color=green]
> > hi everybody-
> >
> > i'm having a counting problem i hope you guys and gals could give me
> > some help with. i have a query that retrieves a bevy of information
> > from several different tables.
> >[/color]
>
> Megan,
>
> You did a great job explaining what your problem is. I checked
> reports I created in the past that grouped on multiple fields and I'm
> not happy with what I did in those cases. When I couldn't use SUM or
> COUNT directly I created Public functions in a module that would
> ensure I got the total or count that I wanted. For example:
>
> Control Source for the txtNumberOfCompanies textbox in the Footer:
>
> =CountSQLRecords("SELECT CompanyName FROM ... GROUP BY CompanyName;")
>
> Note that the SQL string used to get the count is somewhat similar to
> the one used for the RecordSource except you are now free to group the
> same way the report groups in order to get your count. You can also
> test out your SQL strings first by saving your report's RecordSource
> as a query and making sure the groupings give you what you want. I
> look forward to seeing if others have solved this problem so that I
> can simplify my reports. P.S., using 'Name' as the name of a field
> could possibly confuse Access.
>
> James A. Fortune[/color]


Thanks for the help James!!!

I haven't tried your idea yet, but I came up with another idea. I put
a text box with "Name = RecordCount" (without the quotes) in the
"Companies" Group Heading with the Control Source = 1. Then, I put
another text box in the "Companies" Group Footer, with the Control
Source = [RecordCount] and it gave me the total I needed.

I didn't know you could use SQL with a text box's control source.
Cool! There's a lot of things I'd like to try now!

P.S. Thanks for the compliment about doing a good job explaining what
I'm trying to do! I always try to be as clear and detailed as possible
while still trying to be as concise as possible. I read a lot of your
posts even if they don't apply to me in order to learn something new
or an alternate way to accomplish something. Thanks again! Take it
easy!

Megan

re: Counting Problem

meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0408041854.361bac25@posting.google. com>...
[color=blue]
> Thanks for the help James!!!
>
> I haven't tried your idea yet, but I came up with another idea. I put
> a text box with "Name = RecordCount" (without the quotes) in the
> "Companies" Group Heading with the Control Source = 1. Then, I put
> another text box in the "Companies" Group Footer, with the Control
> Source = [RecordCount] and it gave me the total I needed.
>
> I didn't know you could use SQL with a text box's control source.
> Cool! There's a lot of things I'd like to try now!
>
> P.S. Thanks for the compliment about doing a good job explaining what
> I'm trying to do! I always try to be as clear and detailed as possible
> while still trying to be as concise as possible. I read a lot of your
> posts even if they don't apply to me in order to learn something new
> or an alternate way to accomplish something. Thanks again! Take it
> easy!
>
> Megan[/color]

I guess I need to compliment you on your resourcefulness. It's
important to keep simplifying things without losing the essentials. I
learned something new from you also. Thanks for your encouragement.

James A. Fortune