459,963 Members | 1,947 Online
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
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" 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 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" 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.