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 3 3512
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
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Bing Wu |
last post by:
Hi Folks,
I have a very large table containing 170 million rows of coordinats:
CREATE TABLE "DB2ADMIN"."COORDINATE" (
"FID" INTEGER NOT NULL ,
"AID" INTEGER NOT NULL ,
"X" REAL NOT NULL ,...
|
by: Matthew Kramer |
last post by:
How could I create a query which multiples all the values in one field
of a table by 1000? Would it be done the same way to do the same thing
on a query from a query of table? Thanks.
Mat
...
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID ...
|
by: scott.k.fraley |
last post by:
....and the SELECT thats trying to pull from said Query doesn't like it
one bit! ;)
I'm working on this project (in Access 2002) and there is a report
who's RecordSource is the following...
|
by: jho |
last post by:
hello,
I have a pricing sheet form and have the following fields in one row:
SKU
DESC
UNIT PRICE
QTY
PRICE
I want to select the sku in the form and have the corresponding desc
and unit...
|
by: funky |
last post by:
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction....
|
by: mareeus |
last post by:
Hi all,
I need to think of a complex way of searching data in a table and also
create a scoring system and display results considering their score.
I need to keep some tokens and also need to...
|
by: wugon.net |
last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad
query performance
Env:
db2 LUW V8 + FP14
Problem :
We have history data from 2005/01/01 ~ 2007/05/xx in single big...
|
by: damhotpepper |
last post by:
How do I total 2 columns by multiplying their values together.
Table Products, UnitQty x Unit Price ? Any help would be great.
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |