Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sums in Union Query (Access 2000)

    Is it possable to get only one Sum, which is the sum of both select statements in a Union Query?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sums in Union Query (Access 2000)

    You can't do this in the union query itself, but you can create a new query based on the union query, and make it into a Totals query. Then you can create the sum, average, etc. of fields.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums in Union Query (Access 2000)

    On another note, is it not possable to get the sum of two columns and the value of one column in the same union query. I am trying to open a recordset and read though the recrodset, calculating the total of column 1 in each record. I always get the error, "you tried to execute a query that does not include the specified expression "KitType" as part of an aggregate function.

    My Code in the form is:

    Dim db As Database
    Dim rs As Recordset
    Dim Criteria As String

    Criteria = "KitType = " & Me!cmb_KitType & ""

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qry_GetProductByKitSum")
    Me.txtbox_sum = 0
    rs.FindFirst (Criteria)

    If Not rs.NoMatch Then
    Do While Not rs.EOF
    Me.txtbox_sum = Me.txtbox_sum + rs("Total")
    rs.FindNext (Criteria)
    Loop
    Else
    MsgBox "No Sum was Found!"
    End If

    rs.Close
    db.Close

    My QueryDef is As Follow:

    SELECT Sum (Products.KitAmount * Products.WholeSaleCost) As Total, Products.KitType
    FROM Products
    WHERE ( Products.Available <> "Discontinued")
    UNION SELECT Sum(MixedProducts.KitAmount * MixedProducts.Cost) As Total, MixedProducts.KitType
    FROM MixedProducts
    WHERE ((MixedProducts.Available <> "Discontinued"));

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sums in Union Query (Access 2000)

    1. If you use Sum in a query, you make it into a Totals query. In a Totals query, each field you return must either use one of the aggregate functions (Sum, Count, Max, ...) or be used in Group By.

    If you want to return KitType in your query, you must group on it: (I haven't actually tested this, obviously):

    SELECT Sum (Products.KitAmount * Products.WholeSaleCost) As Total, Products.KitType
    FROM Products
    WHERE ( Products.Available <> "Discontinued")
    GROUP BY Products.KitType
    UNION SELECT Sum(MixedProducts.KitAmount * MixedProducts.Cost) As Total, MixedProducts.KitType
    FROM MixedProducts
    WHERE ((MixedProducts.Available <> "Discontinued"))
    GROUP BY MixedProducts.KitType;

    2. The code you posted can be replaced by creating a query based on the union query, or by using DSum. See my reply in the other thread you posted recently.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums in Union Query (Access 2000)

    Wow, OK thanks, You are the man!. The solution you posted about 5 mins ago, works awesome and is much more efficent.

Posting Permissions

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