Multiplying query columns

Hello

I've created a student database for our program that I also need to
get statistics from. It's in Access 2003.

The statistics would be things like Male-Female percentage, percentage
of students from various parts of the world, that sort of thing. I was
going to start small by doing the gender statistics, but I've hit a
bump.

I have a query set up to give me all of the female students and a
query for all the male students. Now, when I do a query to get the
counts for both of these queries, it keeps on multiplying the two
columns together (there are 213 Male and 225 Female, and both columns
in the count query end up with "47925").

I did a Google search on this group and found a number of responses to
similar questions, all saying that the multiplication is because there
is no relationships defined between the two queries and a "Cartesian
Product" is being formulated instead. The problem is, I can't think
of any way to relate these two queries that would result in the proper
values being calculated. Relating the "Gender_Type ID" between the
two gets me zero results (because each one is either Male or Female,
so there is no common result) and it's pointless to relate the names
in the queries either.

I've been banging my head against this wall all afternoon, so any help
would be appreciated.

Thanks in advance.

Dave

re: Multiplying query columns

Sounds as though you need a UNION query, but without details about what your
queries are returning, I can't offer any suggestion as to what that query
should look like.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Dave" <whovian223@hotmail.com> wrote in message
news:pd97o19ms9c62fksbgrtgmtej43t69e79j@4ax.com...[color=blue]
> Hello
>
> I've created a student database for our program that I also need to
> get statistics from. It's in Access 2003.
>
> The statistics would be things like Male-Female percentage, percentage
> of students from various parts of the world, that sort of thing. I was
> going to start small by doing the gender statistics, but I've hit a
> bump.
>
> I have a query set up to give me all of the female students and a
> query for all the male students. Now, when I do a query to get the
> counts for both of these queries, it keeps on multiplying the two
> columns together (there are 213 Male and 225 Female, and both columns
> in the count query end up with "47925").
>
> I did a Google search on this group and found a number of responses to
> similar questions, all saying that the multiplication is because there
> is no relationships defined between the two queries and a "Cartesian
> Product" is being formulated instead. The problem is, I can't think
> of any way to relate these two queries that would result in the proper
> values being calculated. Relating the "Gender_Type ID" between the
> two gets me zero results (because each one is either Male or Female,
> so there is no common result) and it's pointless to relate the names
> in the queries either.
>
> I've been banging my head against this wall all afternoon, so any help
> would be appreciated.
>
> Thanks in advance.
>
> Dave[/color]

re: Multiplying query columns

Dave <whovian223@hotmail.com> wrote in
news:pd97o19ms9c62fksbgrtgmtej43t69e79j@4ax.com:
[color=blue]
> Hello
>
> I've created a student database for our program that I also
> need to get statistics from. It's in Access 2003.
>
>
> I have a query set up to give me all of the female students
> and a query for all the male students. Now, when I do a query
> to get the counts for both of these queries, it keeps on
> multiplying the two columns together (there are 213 Male and
> 225 Female, and both columns in the count query end up with
> "47925").
>
> Thanks in advance.
>
> Dave
>[/color]
If you approached it a little differently, you would have only
one record in each query so that when you put the tow results
together you get only one resuklting row:

Change the first two queries to count the female students, and
the male students, and the third query will return just one row.

There are other ways too, that will allow to sum males and
females in a single query,

SELECT sum(IIF(sex="M",0,1)) as Numfemales, count([sex]) as
numStudents);

--
Bob Quintal

PA is y I've altered my email address.

re: Multiplying query columns

As Doug stated, you haven't provided near enough information about what
you're doing for anyone to be of much help. Generally to get the sort of
statistic that you describe, you would need an aggregate query. Assuming
you have a table of students with some sort of unique ID, and a field called
gender, you would use something like this:

SELECT Gender, Count(StudentID) AS Students
FROM Student
Group BY Gender

This would return one line for each gender (hopefully just 2) with a count
for each.

HTH,
Randy


"Dave" <whovian223@hotmail.com> wrote in message
news:pd97o19ms9c62fksbgrtgmtej43t69e79j@4ax.com...[color=blue]
> Hello
>
> I've created a student database for our program that I also need to
> get statistics from. It's in Access 2003.
>
> The statistics would be things like Male-Female percentage, percentage
> of students from various parts of the world, that sort of thing. I was
> going to start small by doing the gender statistics, but I've hit a
> bump.
>
> I have a query set up to give me all of the female students and a
> query for all the male students. Now, when I do a query to get the
> counts for both of these queries, it keeps on multiplying the two
> columns together (there are 213 Male and 225 Female, and both columns
> in the count query end up with "47925").
>
> I did a Google search on this group and found a number of responses to
> similar questions, all saying that the multiplication is because there
> is no relationships defined between the two queries and a "Cartesian
> Product" is being formulated instead. The problem is, I can't think
> of any way to relate these two queries that would result in the proper
> values being calculated. Relating the "Gender_Type ID" between the
> two gets me zero results (because each one is either Male or Female,
> so there is no common result) and it's pointless to relate the names
> in the queries either.
>
> I've been banging my head against this wall all afternoon, so any help
> would be appreciated.
>
> Thanks in advance.
>
> Dave[/color]