Results 1 to 3 of 3

Thread: Union Queries

  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Question Unanswered: Union Queries

    I'm setting up a database to track calibration of laboratory pipettes. Calibration requires taking five replicate weights of water per pipette and calculating average and Standard deviation. I'm ok with calculating the average in a query...however the problem is calculating Standard Deviation! I've tried using StDEV in "tools" with no luck! I believe the problem is how I'm setting up these five replicates? A co-worker suggested making each replicate a separate query and then using a Union query. Any help will be appreciated. Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    If you are graphing the data then a Union query would be okay. A union query has to be done in SQL;
    example Select Field1, Field2 From tablename
    UNION
    Select Field1, Field2 from anothertableName

    Note that the union requires the same number of fields and datatypes in order.

    However if you are trying to rejoin all the calcs to the proper pipettes in a single row, then what you need is to do several queries that do each calc along with that pipettes' identifier in the result set. Then do another query that rejoins the results to each other on the identifier field to get all results for that pipette in a single row.
    KC

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Not sure what the problem is.

    If you have a simple table with test results, as below:

    TABLE_RESULTS

    TEST_RESULTS_FIELD

    10
    11
    10
    14
    9

    Then you run a query against this data:

    SELECT StDev(TABLE_RESULTS.TEST_RESULTS_FIELD) AS StDevOfTEST_RESULTS_FIELD, Avg(TABLE_RESULTS.TEST_RESULTS_FIELD) AS AvgOfTEST_RESULTS_FIELD
    FROM TABLE_RESULTS;

    You get the Average and the StandardDeviation.

Posting Permissions

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