By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,963 Members | 1,947 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

Multiplying query columns

P: n/a
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
Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <wh********@hotmail.com> wrote in message
news:pd********************************@4ax.com...
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

Nov 23 '05 #2

P: n/a
Dave <wh********@hotmail.com> wrote in
news:pd********************************@4ax.com:
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

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.
Nov 23 '05 #3

P: n/a
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" <wh********@hotmail.com> wrote in message
news:pd********************************@4ax.com...
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


Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.