Results 1 to 2 of 2
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Alzano Lombardo, Italy
    Thanked 0 Times in 0 Posts

    Summing field values from joined tables in a query (2000)

    How come the query shown below(Query6 in the attached mdb) gives me correct results if I leave out the green part while all the values are multiplied by 4 if I leave the green part in?

    SELECT Gorlago.Data, Sum(Gorlago.Field4) AS SumOfField4, Sum(Gorlago.Field6) AS SumOfField6, <font color=448800>Sum(Songavazzo.Field4) AS SumOfField41, Sum(Songavazzo.Field6) AS SumOfField61,</font color=448800> Gorlago.Hour
    FROM Gorlago <font color=448800>INNER JOIN Songavazzo ON (Gorlago.Data = Songavazzo.Data) AND (Gorlago.Hour = Songavazzo.Hour)</font color=448800>
    GROUP BY Gorlago.Data, Gorlago.Hour;
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Summing field values from joined tables in a query (2000)

    There are 4 records for each hour in both Gorlago and Songavazzo. In Query 6, one record for a specific hour in Gorlago is combined with each of the 4 records for that hour in Songavazzo.
    You should create a query that sums Field4 and Field6 per Data and Hour for Gorlago, and another query that does the same for Songavazzo. Then create a third query that joins them.
    Attached Files Attached Files

Posting Permissions

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