Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Results from a Union Query (Access 2000)

    I have a union query which returns all the products in an order (for picking). It is a union query becuase some products are made up of other products. Some products exist in both tables therefore, also show up as two seperate rows in the results of the query. Is there a way that I can have the query with a record that exists in both tables(which shows in two rows) to get put into one row?

    Thanks.

    Sample of Code

    SELECT Products.ProductID, Products.Name, Products.Color, Products.IsMixed, Products.Barcode, OrderedProducts.QtyOrdered As Qty
    FROM (Products INNER JOIN OrderedProducts ON Products.ProductID = OrderedProducts.ProductID)
    WHERE (((OrderedProducts.InvoiceID)=[Forms]![FRM_FaxInCustomer].[InvoiceID]))
    UNION SELECT Products.ProductID, Products.Name, Products.Color, Products.IsMixed, Products.Barcode, OrderedProductsMixedComponents.QtyNeeded As Qty
    FROM Products INNER JOIN OrderedProductsMixedComponents ON Products.ProductID = OrderedProductsMixedComponents.ProductID
    WHERE ((OrderedProductsMixedComponents.InvoiceID)=[Forms]![FRM_FaxInCustomer].[InvoiceID]);

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Results from a Union Query (Access 2000)

    Just make another query based upon your union query and use the DISTINCT word, or GROUP BY clause.

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

    Re: Results from a Union Query (Access 2000)

    A union query will weed out double records, i.e. record wth identical values in all selected fields, unless you specify UNION ALL instead of just UNION. So if your query returns the same product twice, the Qty values must be different (this is the only field that can be different). You must decide what you want to do with the Qty fields: calculate the sum, or the minimum, maximum, average... Then create a new query based on the union query, make it into a totals query (Query | Totals...), and set the Totals option for Qty to the desired aggregation function.

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

    Re: Results from a Union Query (Access 2000)

    Hi guys. Thanks for the input

    I did like what was mentioned, by using another query, which is based on the first query. The sum is calculated on that second query

    Thanks yet again for the help

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

    Re: Results from a Union Query (Access 2000)

    Actually, my problem is not solved. If I have 1 record in one table a dn two records in the other table, when I based my second query on the first, I get two rows returned of a product instead of one. Each row represents a Sum of that product in the specific table.

    I guess in a nutshell is I can Sum the rows in each table correctly, but I cannot Sum the rows of a product from 2 different tables

    Any Ideas?

    First Query:
    SELECT Products.ProductID, Products.Name, Products.Color, Products.IsMixed, Products.Barcode, OrderedProducts.QtyOrdered As Qty, OrderedProducts.Scanned, OrderedProducts.ProductStatus
    FROM (Products INNER JOIN OrderedProducts ON Products.ProductID = OrderedProducts.ProductID)
    WHERE (((OrderedProducts.InvoiceID)=[Forms]![FRM_PickingSlipForm].[InvoiceID]) AND Products.Name <> 'Blocks')
    UNION ALL SELECT Products.ProductID, Products.Name, Products.Color, Products.IsMixed, Products.Barcode, OrderedProductsMixedComponents.QtyNeeded As Qty, OrderedProductsMixedComponents.Scanned, OrderedProductsMixedComponents.ProductStatus
    FROM Products INNER JOIN OrderedProductsMixedComponents ON Products.ProductID = OrderedProductsMixedComponents.ProductID
    WHERE ((OrderedProductsMixedComponents.InvoiceID)=[Forms]![FRM_PickingSlipForm].[InvoiceID]) AND Products.Name <> 'Blocks';

    Second Query
    SELECT DISTINCT qry_FindProductsforPickingSlipForm.Name, qry_FindProductsforPickingSlipForm.Color, qry_FindProductsforPickingSlipForm.Barcode, Sum(qry_FindProductsforPickingSlipForm.Qty) AS SumOfQty, qry_FindProductsforPickingSlipForm.Scanned AS Total_Scanned, Sum(qry_FindProductsforPickingSlipForm.ProductID) AS SumOfProductID
    FROM qry_FindProductsforPickingSlipForm
    WHERE (((qry_FindProductsforPickingSlipForm.IsMixed)=Fal se) AND ((qry_FindProductsforPickingSlipForm.Name)<>'Block s'))
    GROUP BY qry_FindProductsforPickingSlipForm.Name, qry_FindProductsforPickingSlipForm.Color, qry_FindProductsforPickingSlipForm.Barcode, qry_FindProductsforPickingSlipForm.Scanned, qry_FindProductsforPickingSlipForm.ProductID;

    Thanks

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

    Re: Results from a Union Query (Access 2000)

    OK, I really need to results of the product to show up on one line. The QtyScanned, a value in both tables, gets updated everytime a barcode scan is done. In that case, when the form is requeried, sometiem 3 records show up. Bascally, how can have a query which TRULY sums up a product with information from both tables, to be in one line, all the time. (If that makes any sense)

    Thanks

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

    Re: Results from a Union Query (Access 2000)

    I don't understand why you have Sum(qry_FindProductsforPickingSlipForm.ProductID) in the second query (why would you add ProductID's?) and why you group by ProductID if you already group by Name. I don't know enough about your data to know if you should group by qry_FindProductsforPickingSlipForm.Scanned.

    A group by query keeps items on different lines if they differ on at least one of the Group By fields, so look carefully to decide if you have too many fields you group on.

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

    Re: Results from a Union Query (Access 2000)

    Hi Hans

    The Sum on the ProductID should not be there. That was a mistake. Basically, there are 2 columns in two tables that I need to worry about, QtyOrdered and Scanned.(both integers fields) What happens is that when I scan a barcode, the value of the Scanned text field will go up by one. The form is requeried to show that it has been scanned. Once the amount scanned equals the QtyOrdered, then everything is good. What is happening is when I first run the query, the product only shows up in 1 row. The scanned value = 0. That is how I need it. Once I scan the product, the scanned value goes up, but now there are two rows of that product in the table. It is really hard to explain this. Basically, I need to somehow have the name of the product to always show up in 1 row, regardless of how many records are of that product in the two tables. (techically, in the orderproducts table, only 1 record can show up for a product per invoice, but in the orderproductsmixedcomponets table, that record can show up many times per invoice.)

    If that helps at all, let me kow, if not, I will still try to hack away at it for a while and see if I can get the results that I need.

    Thanks

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Results from a Union Query (Access 2000)

    Post your query here.

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

    Re: Results from a Union Query (Access 2000)

    If you group by Scanned, you will get a separate record for each value of Scanned. You must determine what you want to return for Scanned. For instance, if you want the most recent (=highest) value, set the Totals option for Scanned to Max; if you do that, only one record for each product will be returned.

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

    Re: Results from a Union Query (Access 2000)

    Bascally, for each record that exist for the product, if the field Scanned is different for each record, then each record will show up on a seperate line. If they all contain the same value in the Scanned field, then they appear on the same line.

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

    Re: Results from a Union Query (Access 2000)

    Hi guys

    I think I am going to have to this another way. I was hoping to always have each record only be displayed once, getting grouped by Total_scanned and QtyOrdered. The query is on an earlier post.

    Thanks for the help [img]/forums/images/smilies/smile.gif[/img]

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

    Re: Results from a Union Query (Access 2000)

    I don't understand what you want <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Results from a Union Query (Access 2000)

    If you refer to another post, in or out of this thread, it would help if you indicate the post number. You have posted SQL in a couple of places in this thread and in other threads, so it's hard to tell which query is under discussion.
    Charlotte

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Results from a Union Query (Access 2000)

    Why don't you describe the tables concerned and provide an example of the data in these tables, and what you want to achieve (by way of an example).

Page 1 of 2 12 LastLast

Posting Permissions

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