Results from a Union Query (Access 2000)

Topic

New Reply

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]);

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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:)

Reply | Quote

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)=False) AND ((qry_FindProductsforPickingSlipForm.Name)’Blocks’))
GROUP BY qry_FindProductsforPickingSlipForm.Name, qry_FindProductsforPickingSlipForm.Color, qry_FindProductsforPickingSlipForm.Barcode, qry_FindProductsforPickingSlipForm.Scanned, qry_FindProductsforPickingSlipForm.ProductID;

Thanks

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Post your query here.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

I don’t understand what you want

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

OK, this is how I would break it down. A picture of the two tables is attached and hopefully will help. You might want to grab a coffee

In this application, there are two types of products, Regular products and Mixed Products. A mixed product is a product that is a combination of other products. I have two tables that record the products ordered. Once an order is placed, a picking slip is produced, give the products that need to get picked up from the warehouse. The picking slip would contain all the regular products and the COMPONENTS of the mixed product.

The First table OrderedProducts, contains all the regular products purchased, with details such as invoiceID, current price, qtyordered, qtyscanned, isMixed (to tell the product is mixed) and ProductID.

Now, if the product were a mixedproduct, an entry would still go in the OrderedProduct table, giving the details such as price and qty ordered. The components that make up the mixed product would go inside the OrderedProductMixedComponents table. This table includes qtyneeded, qtyscanned, invoiceID, productID). The big difference between Regular Products and Mixed Products is Mixed Products do NOT have a barcode, more on that later

For example, lets say I have 4 Products, A, B and C and 1 mixed product, D. The MixedProduct D has 2 components; A & C. Product D has 2 components of Product A and 1 component of Product C.

Now let’s say I purchase the 3 qty of product A, then in the OrderedProducts table, the qtyneeded would be set to 3, along with the productID, invoiceID and pricing would all be recorded. The QtyScanned would be set to 0 and IsMixed would be set too false.

Now lets say I purchase 2 qty of Product D (The Mixed product). An entry would by made in the OrderedProducts table for qtyneeded as 2, plus the current InvoiceID, Pricing, and ProductID valued. The IsMixed Boolean value is set to true.

Since I know this product is a mixedproduct, I have to put the components that make up this mixed product in the orderedproductsMixedComponents table. In this case, I would have 2 records that relate to this product. Each record contains the current invoiceID, the ProductID of the MixedProduct (Which now is get referred to as MixedProductID in the OrderProductMixedComponents table only), the productID (the productID of the component) the qtyneeded, qty scanned = 0). Therefore an entry for component A would have the qtyneeded set to 4(2*2), the productID of the component, the MixedProductID that represents the productID of the MixedProduct. Component C would be set be similiar, but qtyneed is set to 2.

At the end of the order, I have purchased 2 of product A and 3 of Product D, Therefore, my picking slip would be.

ProductName: A Qty: 7 (3+(2*2)) QtyScanned: 0
ProductName: C Qty: 2 QtyScanned: 0

Now I can record invoices and shipments no problem. A form is used and is based on a 2 queries. The first query is union query that gets the results from the OrderedProducts and OrderedProductsMixedComponents. The second query is based on the first query and is used to Sum the results. The results of this works nicely and Product A is Summed in one line, and so is Product C

When a barcode is scanned, the system can find which product was scanned, and return the ProductID. Knowing this, I can adjust qtyScanned = qtyscanned + 1 in the OrderedProducts, or OrderProductsMixedComponents table. I just find the first instance of that record in the table where QtyScanned < QtyNeeded. This works great

The problem that I have is when I scan a product, and adjust the qtyScanned for that particular product, in this case, Product A, I requery the form. Instead of the product still showing on one line, it is shown on two lines (basically each record shows on its own line). Example

Before:
ProductName: A Qty: 7 QtyScanned: 0
ProductName: C Qty: 2 QtyScanned: 0

After

ProductName: A Qty: 3 QtyScanned: 1
ProductName: A Qty: 4 QtyScanned: 0
ProductName: C Qty: 2 QtyScanned: 0

What I need is this:
ProductName: A Qty: 7 QtyScanned: 1
ProductName: C Qty: 2 QtyScanned: 0

I can record everything fine; it is just displaying the updated picking slip each time a product is scanned which is the issue.

I hope this is not to confusing. A picture of the 2 tables in included as well. My queries are listed below

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, Sum([qry_FindProductsforPickingSlipForm].[Scanned]) AS Total_Scanned, [qry_FindProductsforPickingSlipForm].[ProductID]
FROM qry_FindProductsforPickingSlipForm
WHERE ((([qry_FindProductsforPickingSlipForm].[IsMixed])=False) And (([qry_FindProductsforPickingSlipForm].[Name])’Blocks’))
GROUP BY [qry_FindProductsforPickingSlipForm].[Name], [qry_FindProductsforPickingSlipForm].[Color], [qry_FindProductsforPickingSlipForm].[Barcode], [qry_FindProductsforPickingSlipForm].[Scanned], [qry_FindProductsforPickingSlipForm].[ProductID];

Reply | Quote

OK Here is a before and after shot of a barcode getting scanned on a product.

Before

Reply | Quote

After

Reply | Quote

Take a good look at the bolded parts of your second query:

SELECT DISTINCT [qry_FindProductsforPickingSlipForm].[Name], [qry_FindProductsforPickingSlipForm].[Color], [qry_FindProductsforPickingSlipForm].[Barcode], Sum([qry_FindProductsforPickingSlipForm].[Qty]) AS SumOfQty, Sum([qry_FindProductsforPickingSlipForm].[Scanned]) AS Total_Scanned, [qry_FindProductsforPickingSlipForm].[ProductID]
FROM qry_FindProductsforPickingSlipForm
WHERE ((([qry_FindProductsforPickingSlipForm].[IsMixed])=False) And (([qry_FindProductsforPickingSlipForm].[Name])’Blocks’))
GROUP BY [qry_FindProductsforPickingSlipForm].[Name], [qry_FindProductsforPickingSlipForm].[Color], [qry_FindProductsforPickingSlipForm].[Barcode], [qry_FindProductsforPickingSlipForm].[Scanned], [qry_FindProductsforPickingSlipForm].[ProductID];

You sum [qry_FindProductsforPickingSlipForm].[Scanned] but you also group on the same field. Grouping on it means that you get each individual value of [qry_FindProductsforPickingSlipForm].[Scanned] in a separate record. You don’t want that, so you shouldn’t group on this field, just sum it. In other words, remove [qry_FindProductsforPickingSlipForm].[Scanned] from the Group By clause of the query.

Reply | Quote

Hi Hans

Oh man, I thought I did that and I kept getting an error, so I put it back. Appearantly I never did remove the qtyscanned column from the group section, it was the other columns

Thanks so much, yet again you helped me out Now I have to fix my code so it work in this way now, I put a slapstick workaround for the current situation that I was in. Also thanks for clearing up the Group By Clause for me as well. That makes it alot clearer now.

Thanks everybody for your input

Reply | Quote

A picture is worth a thousand words

Reply | Quote

I wish I would have used the pictures before I typed 1000 words

Reply | Quote

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.

Reply | Quote