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

 

[Jim Bunton]

Hi Kim,
No you're not alone!
This and the other Public.access group usually produce great responses.
I'm struggling a bit with your problem as I can't really understand what it
is you're doing and want to do.
I think the problem is that it's not really clear exactly how your basic
data is structured.
Maybe it would help if you began with the essentials from your original data
(the tables) - this would include the definition of the base table keys for
example but avoid the unnecessary (maybe length of tails etc in this case)

You might also like to know that it seems rather as if Microsoft are phasing
this service out [which is a great shame - it has served me well in the
past]

The replacement seems to me to be:-
http://social.msdn.microsoft.com/Fo...projectserver2010,projectprofessional2010,uc/

I've had a quick look at it and it doesn't look so clear and simple to use
as this news group system - maybe it's just that I'm a bit of a dinosaur!

Hope you find this of some help

Jim Bunton

 

[Adrian C]

On 11/04/2011 07:20, Kim wrote:

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?

This?

SELECT qryDatasetB.PET, (SELECT Sum(DatasetA.QTY)
FROM DatasetA WHERE DatasetA.PET = qryDatasetB.PET
GROUP BY DatasetA.PET) AS QTY, qryDatasetB.[QTY OF MALES]
FROM qryDatasetB;