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

    Summarized Query (Access 2000)

    I am trying to put a report together to show all the product in a specific invoice for a report. In the table design, I have to a column which states if the product is in the order or on backorder. Currently, I have 2 querys, One that shows all products on backorder for an invoice and one that shows all products in the order for an invoice. I am trying to make another query which groups these two querys by product name and shows all the products in order. The problem is that sometimes a product requested in the invoice can be all be shipped, all be on backorder, or some shipped and some on backorder.

    I am not sure how to get each product listed only ones. I have tried Left and Right Joins, but since a product can either be all shipped or all on backorder, this does not work. I need to select all from each table, but when I do, I get alot of duplicates and the keyword distinct does not work. I need to know how I can each record to only show up once from each query (the two querys that make up this query), so I can group them properly. In this query, I will also need to know the total amount of product ordereed.

    The layout would look something like this:

    QtyOrd. | QtyShip. | QtyB/O | Name | Category | UnitPrice | ExtendedPrice

    Thanks

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

    Re: Summarized Query (Access 2000)

    The only workaround that I can think of is when the qty of a product is ordered, if all of it is on backorder, then I created 2 records. One to record the qty is on backorder and one to record that zero qty was shipped. That way, I can get have a query (call query 1) which uses a join of showing all records (products) shipped from one query (for an invoice) and from the other query, show all records (products) on backorder which have an record with the same product ID in query . The two query's making this query up would be joined by ProductID.

    Any more efficent methods would be helpful.

    Thanks

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

    Re: Summarized Query (Access 2000)

    You have to do this in two stages.

    1. First, create a union query that selects all products involved in an invoice. Since you haven't told us what the names of the queries and fields are, I'll have to make them up:

    SELECT InvoiceID, ProductID FROM qryProductsInOrder
    UNION
    SELECT InVoiceID, ProductID FROM qryProductsOnBackOrder

    Save this query as, say, qryAllProducts.

    2. Next, create a query based on qryAllProducts and on qryProductsInOrder and qryProductsOnBackOrder; link the latter two to the first one by outer joins (return all records from qryAllProducts.) Tou can use this query as basis for the report.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Summarized Query (Access 2000)

    As an abstract approach, why not show the same product twice, once with the qty shipped, and once with qty back-ordered, but only show the records if the quantity is other than zero. Then you could use a union query that combines the results of the two queries you are currently working with.
    Wendell

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

    Re: Summarized Query (Access 2000)

    Thanks yet again [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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