Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping in a report (Access 2000)

    I have problems with the grouping in a report.My report is based on a union query . I owe this query to the present forum.
    .I need to do the following things:
    1. to group and enumerate all the products from affiliate 1 and customer number 118
    We call it input
    2. to group and enumerate all the products from affiliate 2. We call them output

    3. to deduct the input from the output

    I want to do it in the Sorting and Grouping window of the report.Can somebody
    help me writing the conditions?

    SELECT orders.orderid, orders.orderdate, Customers.Customerid, [order details].ProductID, Customers.afid, [order details].cartons, [order details].Quantity
    FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
    WHERE (((Customers.Customerid)=118) AND ((Customers.afid)=1))
    UNION SELECT orders.orderid, orders.orderdate, Customers.Customerid, [order details].ProductID, Customers.afid, [order details].cartons, [order details].Quantity
    FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
    WHERE (((Customers.afid)=2));

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

    Re: Grouping in a report (Access 2000)

    I'm sorry to disappoint you, but I wouldn't use the union query for this purpose. Grouping would be possible, but it's hard to subtract the subtotals by group. Instead, I would create two separate reports based on the individual queries, one for the input and one for the output, and place these as subreports on a main report. Name these reports sbrInput and sbrOutput, respectively. You can put a text box named txtSum on the report footer of each of these to sum the item you want, for example with Control Source

    =Sum([Quantity])

    If you prefer, these text boxes can be hidden by setting their Visible property to No (you could even hide the report footer section.) On the main report, you can put a text box with Control Source

    =[sbrOutput]![txtSum]-[sbrInput]![txtSum]

Posting Permissions

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