Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open and close reports (Access 2000)

    Spelling error in subject corrected by HansV to help in future searches

    I want to open and close reports for each option in my option box in order to check whether the stock is ok. I have a built n function in the report that opens a mesage box when something is wrong.However i cannot build the opening and closing the reports since the query is based on the option box requiring a choice of the option, and i want to carry it out successively from the first till the last option.Which is the best way to do it ? In my example the code should be under the control "Quick check "but i havent done anything there since a cannot refer to the options
    Attached Files Attached Files

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

    Re: Open and close reports (Access 2000)

    It seems rather superfluous to open and close a report repeatedly - you just want to look at the data. So you should create a new query that groups by afid, and that doesn't refer to the form. Once you have the query working, you can create a (single) new report based on the new query. This will be a useful excercise for you.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open and close reports (Access 2000)

    Yes, i know but it was impossible for me to redo the query.It is so awfully complicated.
    I tried to remove the where cndition by writing customers.afid = 7. i deleted the parameters and the sql, but i am geting a syntax error :

    TRANSFORM Sum([order details].Quantity) AS Sum1
    SELECT [order details].ProductID, orders.orderid
    FROM products INNER JOIN (Customers INNER JOIN (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) ON Customers.Customerid = orders.customerid) ON products.Productid = [order details].ProductID
    WHERE (((orders.orderid)>=(SELECT Max(orders.orderid) FROM Customers INNER JOIN orders ON Customers.Customerid = orders.customerid WHERE orders.Audit=True AND ((Customers.afid)=7))
    GROUP BY [order details].ProductID, orders.orderid, orders.orderid, orders.[required date]
    PIVOT orders.SubOrder In (-1,0);

    If i succeed with afid = 7 then it will be easier for me to build the single query

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

    Re: Open and close reports (Access 2000)

    Try this

    TRANSFORM Sum([order details].Quantity) AS Sum1
    SELECT Customers.afid, [order details].ProductID, orders.orderid
    FROM products INNER JOIN (Customers INNER JOIN (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) ON Customers.Customerid = orders.customerid) ON products.Productid = [order details].ProductID
    WHERE (((orders.orderid)>=(SELECT Max(orders.orderid) FROM Customers AS t INNER JOIN orders ON t.Customerid = orders.customerid WHERE orders.Audit=True AND t.afid=Customers.afid)))
    GROUP BY Customers.afid, [order details].ProductID, orders.orderid
    PIVOT orders.SubOrder In (-1,0);

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

    Re: Open and close reports (Access 2000)

    The attached database demonstrates another approach using a second query instead of a subquery. The second query qryMaxID returns the highest OrderID with Audit = True for each afid. The crosstab query uses this query to limit the OrderIDs.
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open and close reports (Access 2000)

    Thank you so much ! I think your second suggestion is simply genial. I will apply it and build the query now. I have some questions regarding the query but i think it is proper to open a separate thread.

Posting Permissions

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