Best way to combine two tables in a query

[Zach]

Can you suggest the best way to combine two tables in a query? Here's
the situation....

I have two tables that hold membership information about two separate
groups. About 50 members cross over, being part of both groups. The
members of the groups also take the same classes.

The piece of data I need is to see how many people have taken each of
the classes. There is a yes/no field in each table that tracks this for
each of four classes.

I need to merge data from the two tables, dealing with the duplicate
individuals, and counting the number of people logged as having taken
each class.

Can you help me with a starting SQL query for this? Would it be best
done with a UNION, or with something else?

Thanks,

Zach

 

[Marshall Barton]

Can you suggest the best way to combine two tables in a query? Here's
the situation....

I have two tables that hold membership information about two separate
groups. About 50 members cross over, being part of both groups. The
members of the groups also take the same classes.

The piece of data I need is to see how many people have taken each of
the classes. There is a yes/no field in each table that tracks this for
each of four classes.

I need to merge data from the two tables, dealing with the duplicate
individuals, and counting the number of people logged as having taken
each class.

Can you help me with a starting SQL query for this? Would it be best
done with a UNION, or with something else?

It sure sounds like you should start with a UNION query to
merge the two datasets. Then create another query based on
the union query to do the aggregations.

--------------
Important note: You should not have four class fields in
your tables. Instead you should have a separate table for
classes attended. Ask yourself what you would have to do to
add a 5th or 25th class, the answer should be add rows to
the classes table.

As another rule, you should not have a separate table for
each group. There should be one table of people and another
table for the groups they belong to. Ask youself what you
would have to change if you ever have to deal with 3 or 10
groups, the answer should be add rows to the groups table.

There's a lot more to it than that, but you really need to
think about it. Your question would not even come up in a
properly normaized table structure.

Try Googling for a comprehensible explanation of Database
Normalization.

 

[Zach]

Thanks, Marsh. I agree with you fully. Unfortunately, I don't own the
db and can't change the tables.

With that being the case, do you think this would work? Or is there a
simpler way to handle this?

SELECT
(SELECT COUNT(Class1)
FROM tblTable1
WHERE tblTable1.Class1 = true)
AS Class1Count),
(SELECT COUNT(Class2)
FROM tblTable1
WHERE tblTable1.Class2 = true)
AS Class2Count),
(SELECT COUNT(Class3)
FROM tblTable1
WHERE tblTable1.Class3 = true)
AS Class3Count),
(SELECT COUNT(Class4)
FROM tblTable1
WHERE tblTable1.Class1 = true)
AS Class4Count)
FROM tblTable1
UNION
SELECT
(SELECT COUNT(Class1)
FROM tblTable2
WHERE tblTable1.Class1 = true)
AS Class1Count),
(SELECT COUNT(Class2)
FROM tblTable2
WHERE tblTable1.Class2 = true)
AS Class2Count),
(SELECT COUNT(Class3)
FROM tblTable2
WHERE tblTable1.Class3 = true)
AS Class3Count),
(SELECT COUNT(Class4)
FROM tblTable2
WHERE tblTable1.Class1 = true)
AS Class4Count)

 

[Marshall Barton]

Thanks, Marsh. I agree with you fully. Unfortunately, I don't own the
db and can't change the tables.

With that being the case, do you think this would work? Or is there a
simpler way to handle this?

SELECT
(SELECT COUNT(Class1)
FROM tblTable1
WHERE tblTable1.Class1 = true)
AS Class1Count),
(SELECT COUNT(Class2)
FROM tblTable1
WHERE tblTable1.Class2 = true)
AS Class2Count),
(SELECT COUNT(Class3)
FROM tblTable1
WHERE tblTable1.Class3 = true)
AS Class3Count),
(SELECT COUNT(Class4)
FROM tblTable1
WHERE tblTable1.Class1 = true)
AS Class4Count)
FROM tblTable1
UNION
SELECT
(SELECT COUNT(Class1)
FROM tblTable2
WHERE tblTable1.Class1 = true)
AS Class1Count),
(SELECT COUNT(Class2)
FROM tblTable2
WHERE tblTable1.Class2 = true)
AS Class2Count),
(SELECT COUNT(Class3)
FROM tblTable2
WHERE tblTable1.Class3 = true)
AS Class3Count),
(SELECT COUNT(Class4)
FROM tblTable2
WHERE tblTable1.Class1 = true)
AS Class4Count)

I don't think that will do what I thought you wanted (even
after you fix the copy/paste name errors), because it will
return two sets of counts without regard to people that are
in both groups. You should try it yourself to see what you
get.

What I was suggesting is more like this (air code):

query - CombineGroups:
SELECT Class1, Class2, Class3, Class4
FROM tblTable1
UNION
SELECT Class1, Class2, Class3, Class4
FROM tblTable2

query - ClassCounts:
SELECT Count(Class1) As Class1Count,
Count(Class2) As Class2Count,
Count(Class3) As Class3Count,
Count(Class4) As Class4Count
FROM CombineGroups

I doubt this can be combined into a single query and can't
see a reason to even try.