Solved: Microsoft Access 2007 expression building

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

miller08

Thread Starter
Joined
Mar 28, 2010
Messages
26
Hello all!

I am working with a database on fisheries catch data. I want to make a sum of fish by species and habitat (so i have for example the number of species A caught in sandy habitat for all 4 years of the study). The data is organized (in decending order of category size) by habitat (6 categories), species name (100 categories), number caught in each net each day the study was conducted (giving nearly 5000 lines of data in the query).

This is probably confusing so i will include a screenshot of the query I am starting with. Also, my software is in French so it is a bit difficult to use some help resources. If you could include screenshots that would help, since i am not familiar enough with Access to know where to find buttons and such just by the name, translated from English to French!

thanks in advance for your help!
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,896
Ideally it would be best to use a "Crosstab Query". This would give you Habitat going across the top of the Columns and Species, or Species & Size going down the left hand side with the count for each in the Matrix it creates.
You could also use a "Pivot Table Form" to do the same thing, although with the query you can create a Report for further "grouping totals".
 

miller08

Thread Starter
Joined
Mar 28, 2010
Messages
26
wow! thanks, that did exactly what i needed!

ok, well there's one other problem that perhaps you (or someone) can help with. I've now got the number per habitat per species. however, now all the species show up as the number code assigned to them. it would be much easier to work with using the name of the species. The name is shown in every step up until the crosstab query (i have created a new table -using a query- to use for this cross-tab query. in the table, the species name is shown, not the number code).

perhaps a crosstab query can only show numbers? im not sure why this is. if anyone can give some suggestions, i'd really appreciate it!

thanks again!
 

OBP

Joined
Mar 8, 2005
Messages
19,896
You can specify to use the name instead of the ID when you use the Crosstab Wizard, or you can go in to the Design View of the Crosstab query and replace the ID with the name.
 

miller08

Thread Starter
Joined
Mar 28, 2010
Messages
26
whoops, ignore that last message. I fixed the species name problem, but realized that this table is just a prettier version of what i had before, and not what i needed.

the table i'm looking at now has species, number of individuals and weight per day as columns, and then separates the number of individuals into which habitat they were found in on each day. there is no totalling going on, since on each line, all individuals of a species was found in only one habitat. so all habitat columns are empty except for one, which then has the same number as the column with the total # individuals for that line. does this make sense? i'll include a screenshot.

what i want is to total the number individuals of each species found in each habitat. any suggestions for that?

thanks again, i'm learning more about access with your last suggestion. :)
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,896
Same answer, but remove the Nbre ind Column, as it is adding a grouping level you don't want.
 

miller08

Thread Starter
Joined
Mar 28, 2010
Messages
26
perfect! thanks so much, this is exactly what i needed. you've made my day (or possibly week!) i'll mark the thread solved

thanks again!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top