Grouping based on 2 columns

[Guest]

The table looks as follows: (table - see further down)
ID - several rows have the same ID
Species - each of those rows with a specific ID have diff. species, making a
combination of ID and Species a unique ID for each row.
Number of individuals - in lake 1 there is 12 pike and 2 perch.

THE QUESTION:
How do I group so that I recieve one row for each ID with each species, and
not have e.g. 2 rows with ID 1 and perch. Additionally - how do I get the sum
of No of individuals in each group?

Thank you!!
/Johanna

ID Species Number of individuals
1 Pike 12
1 Perch 2
1 Perch 5
2 Pike 4
2 Pike 18
2 Carp 8
2 Roach 56
3 Carp 7
3 Carp 2
4 Pike 90
4 Roach 88

 

[Michel Walsh]

Hi,


A crosstab query?

TRANSFORM Nz(SUM(numberOfIndividuals),0) As x
SELECT ID, SUM(x) As LineTotal
FROM myTable
GROUP BY ID



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you!
I did manage to make a cross-tab query, but I don´t know how to convert that
back into a "regular table"... The whole thing is complicated a bit by me
using a Swedish version so I couln´t use your code as it was...

I want the query-table to look just as the original except for no "double
rows" and showing the total no fish.

/Johanna

"Michel Walsh" skrev:

 

[Michel Walsh]

Hi,



Create a new SELECT query based on the saved Crosstab query. Drag all the
desired fields in the grid (double click on the header of the field list in
the upper part of the query designer to select them all, drag the selection
in the grid). Change the SELECT query type to a Make-Table query (from the
menu, under Query...). That should do the trick.



Hoping it may help,
Vanderghast, Access MVP