How to join 2 sumarised data sets

[Kim]

Hi,

Hope I'm not the only one on this message board because I'm getting
lonely. (


I was hoping I could get some help with this one... I have a table of
data that I'm grouping by one particular field: ie.


DATASET A
PET QTY
cat 2
dog 3
fish 1
cat 1


Grouped by PET, so it becomes:


DATASET A (grouped)
PET QTY
cat 3
dog 3
fish 1


I then want to join that to another query, but at the summarised
level
where the other table looks like this:


DATASET B
PET QTY OF MALES
cat 5
dog 1
fish 3


And the result should be:


DATASET A (grouped)
PET QTY QTY OF MALES
cat 3 5
dog 3 1
fish 1 3


Problem is that it seems to group it at a row level rather than
grouped row level.


I know I can make one query a make-table query that groups the data,
then join to that or even make that one a saved separate query that I
can join to, but I really want the whole thing in one query.


Is there a way to make the first grouped query flatten within that
query so that anything joining to it, does so at the grouped/
summarised level?


Thanks so much and I hope I have been clear enough.


Kim

 

[John Spencer]

Sub-queries are one answer. To use sub-queries in Access in the FROM clause
your field and table names cannot contain spaces and should consist of only
numbers, letters, and the underscore character.

SELECT A.Pet, A.QA, B.QM
FROM
(SELECT Pet, Sum(Qty) as QA FROM DataSetA GROUP BY Pet) as A
INNER JOIN
(SELECT Pet, Sum(Males) as QM FROM DataSetB GROUP BY Pet) as B
ON A.Pet = B.Pet

You could also do something like this using sub-queries in the select clause
or VBA aggregate functions. You really need to post the structure of the data
involved for a detailed answer.

The answer might be as simple as the following if the fields were all in the
same table and you have one row for each pet.

SELECT Pet
, Count(Pet) as CountPetType
, Abs(SUM(Gender="Male")) as CountMales
FROM SomeTable
GROUP By Pet

Often giving a simplified example table makes it more difficult to answer the
question then if you give us the real table and its fields.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Kim]

Sub-queries are one answer.  To use sub-queries in Access in the FROM clause
your field and table names cannot contain spaces and should consist of only
numbers, letters, and the underscore character.

SELECT A.Pet, A.QA, B.QM
FROM
(SELECT Pet, Sum(Qty) as QA FROM DataSetA GROUP BY Pet) as A
INNER JOIN
(SELECT Pet, Sum(Males) as QM FROM DataSetB GROUP BY Pet) as B
ON A.Pet = B.Pet

You could also do something like this using sub-queries in the select clause
or VBA aggregate functions.  You really need to post the structure of the data
involved for a detailed answer.

The answer might be as simple as the following if the fields were all in the
same table and you have one row for each pet.

SELECT Pet
, Count(Pet) as CountPetType
, Abs(SUM(Gender="Male")) as CountMales
FROM SomeTable
GROUP By Pet

Often giving a simplified example table makes it more difficult to answerthe
question then if you give us the real table and its fields.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

















- Show quoted text -

Thanks John.

I'm not too good with raw sql but will give it a go.

You've been a great help.

Regards,

Kim