Results 1 to 3 of 3

Thread: Query question

  1. #1
    Join Date
    Jun 2006

    Unanswered: Query question

    SELECT ConRes.Results ,VarRes.Results, Cons.VarID, Vars.VarID
    FROM (Samples AS Vars INNER JOIN (Samples AS Cons INNER JOIN Results AS ConRes ON Cons.SampIDBarcode = ConRes.SampleID) ON Vars.TrialID = Cons.TrialID) INNER JOIN Results AS VarRes ON Vars.SampIDBarcode = VarRes.SampleID
    WHERE (Vars.VarID="1-48777" Or Vars.VarID="1-48998") AND (Cons.VarID="1-1413" Or Cons.VarID="1-783") AND VarRes.TraitCode="HWT" AND ConRes.TraitCode="HWT"

    What I want to do is get an average of ConRes.Results and VarRes.Results for every combination of Vars.VarID and Cons.VarID. Is it possible to do that in one query or even two queries?

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    Since no one has answered this yet, I thought I'd send you a reply on something that you could try. I'm thinking the best way you might do this is to probably do a make-table query cobmining the Vars.VarID with the Cons.VarID into a new field (i.e. ConVars: Vars.VarID & " - " & Cons.VarID). Then make a "summation" query based off of this new table adding the new field, ConRes.Results and VarRes.Results fields. Change the ConRes.Results and VarRes.Results columns to show the average. Or 2 new separate queries based off the new table, both with the new field and one with the ConRes.Results field and the other with the VarRes.Results fields. Set the queries to "Summation" queries and set both fields to show the average. I may have misunderstood your question but it still sounds like a make-table query in some fashion/concept and then a query(s) off that table will work for you.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2006
    That is kind of what I did, I'm making a table, inserting the selected data into that then doing a crosstab query to put it into the format I need. Thank you for your advise.

Posting Permissions

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