morinia
asked on
Combining results from multiple queries in ACCESS
Experts,
I have to tables in Access one is Boys and the other Girls.
I would like to get the total number from each table.
i.e.
Select count(*) as Total_Girls from Girls;
Select count(*) as Total_Boys from Boys;
I would like the results to be in the same table.
Is there a way to do this in Access?
I have to tables in Access one is Boys and the other Girls.
I would like to get the total number from each table.
i.e.
Select count(*) as Total_Girls from Girls;
Select count(*) as Total_Boys from Boys;
I would like the results to be in the same table.
Is there a way to do this in Access?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Experts,
I want them on separate rows. The problem I am having now is I only want the using distinct in count in Access.
I want them on separate rows. The problem I am having now is I only want the using distinct in count in Access.
That's what the union query does.
/gustav
/gustav
Go back to the original table.
Select SexCD, Count(*) as RecCount from YourTable;
You'll end up with
Girls 12343
Boys 12344
Select SexCD, Count(*) as RecCount from YourTable;
You'll end up with
Girls 12343
Boys 12344
ASKER
Experts,
I am sorry for the confusion. My problem is not concatenating the totals into one table now. The problem I am having is counting unique values for the respective tables. It is possible to have multiple records with the same key value. I want to count a person only once.
How would I get distinct values in an access query?
I am sorry for the confusion. My problem is not concatenating the totals into one table now. The problem I am having is counting unique values for the respective tables. It is possible to have multiple records with the same key value. I want to count a person only once.
How would I get distinct values in an access query?
> Is it possible to have multiple records with the same key value.
You keep asking this, but yes, that's what the union query does.
of course, if a person exists in more than one row of the source table, you'll have use two distinct queries as source for the union query rather than the original tables.
/gustav
You keep asking this, but yes, that's what the union query does.
of course, if a person exists in more than one row of the source table, you'll have use two distinct queries as source for the union query rather than the original tables.
/gustav
Unique values of what? For the Girls, try something like:
Select
"Girl" As Gender,
Count(DistinctGirls.*) As Total
From (
SELECT DISTINCT [LastName], [FirstName], [DOB]
FROM Girls) as DistinctGirls
If that gets you what you are looking for, then do the same for the boys and UNION them together.
Select
"Girl" As Gender,
Count(DistinctGirls.*) As Total
From (
SELECT DISTINCT [LastName], [FirstName], [DOB]
FROM Girls) as DistinctGirls
If that gets you what you are looking for, then do the same for the boys and UNION them together.
We are flying blind here. Sounds like you have a table of people and a table of transactions and you want to count by gender the number of people who have had a transaction.
Start with the transaction table
Select Distinct PersonID, Gender From transaction table.
Then create a second query to count the records from the first one.
Select GenderCD, Count(*) as RecCount from firstquery;
You can do this as a query with a sub query but two separate queries are usually easier to test.
If Gender isn't in the transaction table you will need to join to the persons table in one of the queries to pick it up.
Start with the transaction table
Select Distinct PersonID, Gender From transaction table.
Then create a second query to count the records from the first one.
Select GenderCD, Count(*) as RecCount from firstquery;
You can do this as a query with a sub query but two separate queries are usually easier to test.
If Gender isn't in the transaction table you will need to join to the persons table in one of the queries to pick it up.
You might start with the original data and do a query on that.
Select Sum(IIf(SexCD = "F", 1, 0)) As GirlsCount, Select Sum(IIf(SexCD = "M", 1, 0)) As BoysCount
From yourOriginalTable;