Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Combining results from multiple queries in ACCESS

Posted on 2014-03-31
9
1,183 Views
Last Modified: 2014-04-01
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?
0
Comment
Question by:morinia
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 39967003
I would like the results to be in the same table
Do you mean query?  Do you want them to be in the same row or separate rows?

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;
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 39967041
Use a union query:

Select
    "Girl" As Gender,
    Count(*) As Total
From
    Girls
Union
Select
    "Boy" As Gender,
    Count(*) As Total
From
    Boys;

/gustav
0
 

Author Comment

by:morinia
ID: 39967081
Experts,

I want them on separate rows.  The problem I am having now is I only want the using distinct in count in Access.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39967084
That's what the union query does.

/gustav
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39967096
Go back to the original table.

Select SexCD, Count(*) as RecCount from YourTable;

You'll end up with
Girls 12343
Boys 12344
0
 

Author Comment

by:morinia
ID: 39967187
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?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39967256
> 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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39967361
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.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39967364
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question