1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Microsoft Access 2007 expression building

Discussion in 'Business Applications' started by miller08, Apr 29, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. miller08

    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!
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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".
     
  3. miller08

    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!
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  5. miller08

    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. :)
     

    Attached Files:

  6. OBP

    OBP

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

    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!
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    My pleasure.
     
  9. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/919955

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice