Comparing and Combining Information from One Table

[Guest]

Hello,
I've had a rough time trying to figure out how to write a query that can
analyze and aggregate this information. I'me hopeful one of you smart folks
can help me.

The table is essentially this:

Group_1
Num_1
Group_2
Num_2
Group_3
Num_3

I essentially need to combine all this information, based on Group Names.
So at any given time, Group_1 could be "Dogs", or Group_2, or Group_3, could
be "Dogs".
The number following the Group is associated with the preceding Group, which
I will need to sum up based on the Group_Name.

Here's an example:

Dogs
1
Cats
3
Horse
5

Next Row:

Horse
10
Dogs
20
Cats
30

I would like to query this information and get the following results:
Group_Name Count Sum
Dogs 2 21
Cats 2 33
Horse 2 15

The one main limitation is, I can't break up the original database into
multiple tables, it has to stay as one.

Is this sort of thing possible in a query? (There are actually 7 repeating
groups in the table)

Any help or a resource where I could learn about how to do this would be
greatly appreciated, thanks in advance!

-Dan

 

[Tom Ellison]

Dear Dan:

As a database, this would seem to be considerably malformed. But there is
good news. You can work with it anyway by first giving it the appearance of
being properly formed

Start with this UNION query:

SELECT Group_1 AS XGroup, 1 as Position, Num_1 AS Num
FROM YourTable
WHERE Group_1 IS NOT NULL
UNION ALL
SELECT Group_2 AS XGroup, 2 as Position, Num_2 AS Num
FROM YourTable
WHERE Group_2 IS NOT NULL
UNION ALL
SELECT Group_3 AS XGroup, 3 as Position, Num_3 AS Num
FROM YourTable
WHERE Group_3 IS NOT NULL

Save this query as GroupUnion (or use another name and substitute it below).

SELECT XGroup, COUNT(*) AS Ct, SUM(Num) AS MySum
FROM GroupUnion
GROUP BY XGroup

The above UNION query gives the appearance that this data would have were it
properly normalized. Database operations are carefully designed to work on
properly normalized data. Or, you could say, that proper normalization is
designed to make database operations possible. Two ways of saying the same
thing.

I added the Position column to this table. You started with there being 3
positions in your table. I'm preserving that information just in case there
is any possible significance to it. It is ignored in the last query and is
not needed for that. But it does keep them in order and identify the
position in your original table from which they come.

Tom Ellison

 

[John Vinson]

On Tue, 11 Apr 2006 14:40:02 -0700, Dan von InfoPath <Dan von

Hello,
I've had a rough time trying to figure out how to write a query that can
analyze and aggregate this information. I'me hopeful one of you smart folks
can help me.

The table is essentially this:

Group_1
Num_1
Group_2
Num_2
Group_3
Num_3

Then it is incorrectly designed. You have repeating groups. That's why
you're having trouble!

I essentially need to combine all this information, based on Group Names.
So at any given time, Group_1 could be "Dogs", or Group_2, or Group_3, could
be "Dogs".
The number following the Group is associated with the preceding Group, which
I will need to sum up based on the Group_Name.

Here's an example:

Dogs
1
Cats
3
Horse
5

Next Row:

Horse
10
Dogs
20
Cats
30

I would like to query this information and get the following results:
Group_Name Count Sum
Dogs 2 21
Cats 2 33
Horse 2 15

The one main limitation is, I can't break up the original database into
multiple tables, it has to stay as one.

Could you explain WHY your table MUST be stored incorrectly, in a
badly non-normalized design!?

Is this sort of thing possible in a query? (There are actually 7 repeating
groups in the table)

A "Normalizing Union Query" might help. You'll need a query based on
another query. Try

SELECT [Group_1] AS Group_Name, [Num_1] AS Num
WHERE [Group_1] IS NOT NULL
UNION ALL
SELECT [Group_2] AS Group_Name, [Num_2] AS Num
WHERE [Group_2] IS NOT NULL
UNION ALL
SELECT [Group_3] AS Group_Name, [Num_3] AS Num
WHERE [Group_3] IS NOT NULL
UNION ALL
SELECT [Group_4] AS Group_Name, [Num_41] AS Num
WHERE [Group_4] IS NOT NULL
UNION ALL
SELECT [Group_5] AS Group_Name, [Num_5] AS Num
WHERE [Group_5] IS NOT NULL
UNION ALL
SELECT [Group_6] AS Group_Name, [Num_6] AS Num
WHERE [Group_6] IS NOT NULL
UNION ALL
SELECT [Group_7] AS Group_Name, [Num_7] AS Num
WHERE [Group_7] IS NOT NULL;

Save this as uniAllGroups. Then base a Totals query on uniAllGroups,
grouping by Group_Name and summing Num.

John W. Vinson[MVP]