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
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
Comment