Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Warehouse query (Access 2000)

    I want to make a query with two conditions : to include all the orders having orders.Audit = > True and also all the orders where orders.Suborder = true. I want to exclude the orders that have no criteria as Audit = True or Suborder = True.I cannot do it, since in the query I have built there are also orders which have neither Audit = True or Suborder = True. How can I exclude those last order from the query? I did try to add in the where clause orders.suborder = True but the query does not give the right results
    SELECT orders.orderid, orders.audit, orders.SubOrder
    FROM orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID
    GROUP BY orders.orderid, orders.audit, orders.SubOrder
    HAVING (((orders.orderid)>=(SELECT Max(orders.orderid) FROM orders WHERE orders.Audit =True)));

    In our database we begin calculating from the orderid that has Audit = true. There is only one order with Audit = True, but after this order there are many orders that have Suborder = True and a lot of orders that have no restrictions.My first task is to calclate the quantities from all the orders where Audit => true or Suborder = true. I will be very grateful for any help in this difficult assignment.
    Attached Files Attached Files

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

    Re: Warehouse query (Access 2000)

    There are no records in your table for which Audit = True and Suborder = True, but if there were, you could find them using this simple query:

    SELECT *
    FROM orders
    WHERE audit=True AND orders.SubOrder=True

    No need to involve the order details table in the query, or to use a subquery.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse query (Access 2000)

    Yes there are no records for which audit = true and suborder= true.Only one record must exist with audit = true since the calculations begin from that point.But there exist also records where Suborder = True and i must add them together. I do not know how to do it.
    The point is that i want to combine the records where Audit = Tue with the records where Suborder = True. I need to add them together. And i want to exclude the records where neither audit or suborder = true.But these records should be after the order where audit = true therefore i must use DMax.
    In my example order 142917 should be excluded from the query since it has neither audit ot subform set to true.
    I thank you for the attention you have given me.Please do apologize me if i am not quite clear.I really need a solution to this problem.

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

    Re: Warehouse query (Access 2000)

    To select records where Audit = True or Suborder = True, use

    SELECT *
    FROM orders
    WHERE audit=True OR orders.SubOrder=True

    I have no idea what DMax has to do with this.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse query (Access 2000)

    Yes, thank you, it shows only those records selected.As to your question,I want to begin the calculations from the order which has audit = True.In my example this is order number 142914. There is only one order with audit = True. I have also to add to this order records where Suborder = True. For example 142915. I have to add them together. But how should Access know from where to begin ? There are also records before order 142914. For example this is order 142912, but i must not count it since it is before order 142914 which is set to audit = true.
    Can you give me an idea could i do all my actions in one query ? I wil describe the actions i have to do with an example from my attachement:
    1. to calculate the quantity of all each product from each order having the criteria Audit => True and Suborder = True
    I want all the calculations to begin after the order which has Audit =>True
    2. To calculate the quantity of each product from each order where Audit = False and Suborder = False, but after the order which has Audit = True
    3. To substract the quantities thus received for each product
    Example with Product 73 :
    1. We have made the auditing with order 14214, where Audit = True. The quantity is 2
    We have also delivered aditional quantity of 2 pieces from this product with order 14215 where Suborder = True
    It should mean that we have delivered in total 2 + 2 = 4 pieces of Product 73

    2. We have sold 2 pieces of this product with order 142917 where Audit = False and Suborder = False
    3. if we subract from the quantities audited and delivered and the quantities sold we will receive 4 2 = 2

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

    Re: Warehouse query (Access 2000)

    The condition "Audit => True" makes no sense. Audit is True or False.

    You cannot perform the calculations you mention in a single query, because you need to use different selection criteria for the quantities you want to subtract. You have 3 steps, each is one query.

    1) You need one query to sum the quantities for orders as with audit = True and also those with a higher orderid for which suborder = True.
    2) You need a second query to sum the quantities for orders with audit = false and suborder = false.
    3) You need a third query to combine the first two queries and subtract the sums of quantities.

    See attached version.
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse query (Access 2000)

    Of course i will begin studying your help but before that i want to thank you really for your prompt and professional attention you have given to me and to everybody else in this site.Just to mention that this problem was very important for me and i am so grateful for your prompt reply

    Markus

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse query (Access 2000)

    Thank you so much Hans. That is us exactly what i wanted

Posting Permissions

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