Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Posts
    70

    Unanswered: Adding all results of each type

    Hi,

    I have a query that brings back various data in row, which works fine. One of the columns is species with another column giving the number of that species. There is another column that has type of species. Is there a way in the design view of the query to write formula in the criteria section or something so that for each sample all the species that are of the same type just displays as the species type and adds the numbers of each of those species together so that for each sample you just get the total for the species type?

    Hope this makes sense,

    Thanks,
    Owain

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's hard to provide a precise answer without knowing the structure of the bable and the query you deal with. If I understand what you want to achieve, you should use a GROUP BY clause in the query.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the group by verb

    my guess is its going to be something like
    select count(species) as NoSpecies, Species, SpeciesType from mytable
    group by speciestype,species
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2007
    Posts
    70

    I've tried this

    Hi I've tried the below expression but it doesnt work?

    Code:
    SELECT chqry_rpt_AL_simple.SampleID, SUM(chqry_rpt_AL_simple.BugName) AS ALResult, chqry_rpt_AL_simple.BugName, chqry_rpt_AL_simple.BugTypeName
    FROM chqry_rpt_AL_simple
    GROUP BY chqry_rpt_AL_simple.SampleID, chqry_rpt_AL_simple.BugName, chqry_rpt_AL_simple.BugTypeName;
    I think I need the SUM rather than COUNT as I want the total figure for all the bugname of the same bugtypename in each sampleID?

    Also I'm running the query from another query not directly from a table, not know if this makes any difference?

    Hope you can help more, thanks so much for your time so far


    Owain

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as we don't know your tables, your data
    Hi I've tried the below expression but it doesnt work?
    isn't going to do you any good
    why doesn't it work
    what is failing?
    Code:
    SELECT SampleID, SUM(BugName) AS ALResult, BugName, BugTypeName
    FROM chqry_rpt_AL_simple
    GROUP BY SampleID, BugName, BugTypeName;
    what is the purpose of the group by Sample_ID? Im not convinced it should eb included in this query.
    what results are you getting, as opposed to what results you expect?
    does your data support what you are getting or what you are expecting?

    Id be very surprised if you need sum (bugname).. unless bugname is a numeric column, and, say representts the number of a type of bug foundin this sample.
    count(bugname) will returen the number of times each type of bugname is found.

    it shouldnt' amke any difference if the record source is a table or a query, providing you understand what is the difference. so you need toinvestigate if the problems you are experiencing are down to the query design, the query that is the data source

    frankly I'd be amazed if anyone can concisely help you ojn the amount and type of information you have provided so far
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Dec 2007
    Posts
    70
    Hi Healdem,

    Sorry I've been a bit vague, I'll start from the start and try to be a bit clearer!

    I have a query (chqry_rpt_AL_simple) that take data from various tables and displays them as follows:

    SampleID--------BugTypeName--------BugName--------ALResult

    SampleID = The number ref number
    BugTypeName = The type of Algae ie Green, Brown, Blue-Green etc
    BugName = Species of Algae
    ALResult = Number of algae counted in sample

    This query is used for reports so I dont want it to change, that is why I want to just base a new query on this rather than alter it. The sampleID is always grouped together as it is important the we are actually looking at the results for each sample!
    So at the moment the the query (chqry_rpt_AL_simple) produces a table that shows each algae species in column BugName, how many of that species counted in ALResult, the type of algae it is in BugTypeName, and its sample number in SampleID. So there will be lots of the same SampleID number together with all the algae and numbers found in that sample.

    Now for the new query! I want the query to produce a table the same as above but without the column BugName, but if in one of the samples there were multiple species of the same type ie. 3 species of Blue-Green algae, I would like in the ALResults column to add the 3 numbers counted of the 3 species so their total is displayed next to the BugTypeName?

    So you are right, the COUNT does need to be on BugName and the SUM would maybe be on the counted BugName's ALResult?

    An example of the original query:

    SampleID-----BugTypeName-----BugName---ALResult
    11----------------Brown------------Species1------20
    11----------------Green------------Species2------11
    11----------------Brown------------Species3------30
    24----------------Bluegreen-------species4-------20
    24----------------bluegreen-------species5-------20
    24----------------brown------------species3------10

    So the above with the query I'm trying to do would look like this:
    SampleID-----BugTypeName------ALResult
    11---------------brown-----------------50
    11---------------green-----------------11
    24---------------bluegreen------------40
    24---------------brown-----------------10


    I hope this helps more than my other entries, and sorry for the confusion!

    I really hope you can help, I'm very new to all of this,

    Thanks, Owain.
    Last edited by OB1; 11-12-11 at 12:18. Reason: format

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so it looks to me as if you want to
    select sampleid,bugtypename, sum(ALResult)
    from chqry_rpt_AL_simple
    GROUP by sampleid, bugtypename
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2007
    Posts
    70
    Ok thanks healdem, I'll give that a try and let you know the results,

    Thanks for your patience,

    Owain

  9. #9
    Join Date
    Dec 2007
    Posts
    70
    Hi Healdem,

    Quote Originally Posted by healdem View Post
    ok so it looks to me as if you want to
    select sampleid,bugtypename, sum(ALResult)
    from chqry_rpt_AL_simple
    GROUP by sampleid, bugtypename
    This worked exactly as I wanted, thanks so much for sticking with me,

    Owain.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •