Help with a SQL stmt for a union Query

[TDC21]

I have some data that I am trying to summarize and I am thinking I will need a union query to achieve it but I am not sure. I am not that strong with union Queries so any suggestions are appreciated. My data set is as follows

From dbo_SHIPMENT_DETAIL

ORDER LINE PART_ID TRANSACTION_ID DIST_NO MATERIAL LABOR BURDEN SERVICE
100000 1 VC2-090000-1000 279935 1 18,544.01 1.83 - 881.85
100000 1 VC2-090000-1000 279935 2 18,544.01 19.05 - 1,648.51
100000 1 VC2-090000-1000 279935 3 18,544.01 19.05 - 1,758.51
100000 1 VC2-090000-1000 281074 2 37,088.01 38.10 - 3,297.03
100000 1 VC2-090000-1000 281074 3 37,088.01 38.10 - 3,517.03



What I am trying to do is group by [ORDER],[LINE],[PART_ID], AND [TRANSACTION_ID] then MAX on [DIST_NO] and chose records from [MATERIAL], [LABOR], [BURDEN], AND [SERVICE] where the above grouping and MAX applies, and lastly to create a summary column for totals. The end result would be.

ORDER LINE PART_ID TRANSACTION_ID DIST_NO MATERIAL LABOR BURDEN SERVICE TOTAL
100000 1 VC2-090000-1000 279935 3 18,544.01 19.05 - 1,758.51 20,321.57
100000 1 VC2-090000-1000 281074 3 37,088.01 38.10 - 3,517.03 40,643.14


I can get this to work by creating a queries with the needed groups/filter then creating another query that joins the previous query back to the original data but this is one of about 4 related queries that I am trying to make, all of which will have a similar situation, so I am hoping to find a way to do this in a single query to help keep things simple.

Thanks again all

 

[xenou]

I can get this to work by creating a queries with the needed groups/filter then creating another query that joins the previous query back to the original data

This sounds right to me (you can do this in one query using a subquery but it doesn't help performance or make it simpler). I don't see where Union would be of help to you.

Edit:
two examples of a single query here.

First, using a correlated subquery (which is generally less efficient so not as good if you have large tables with many thousands of rows):

select 
	a.Order,
	a.Line,
	a.Part_Id,
	a.Transaction_Id,
	a.Dist_No,
	a.Material, 
	a.Labor, 
	a.Burden, 
	a.Service, 
	(a.Material + a.Labor + a.Burden + a.Service) as Total
from 
	Sheet1 a
where
	a.Dist_No = 
		(select max(b.Dist_No) from Sheet1 b
			where 
				b.Order = a.Order 
				and b.Line = a.Line
				and b.Part_Id = a.Part_Id
				and b.Transaction_Id = a.Transaction_Id
		)

The same query can be decorrelated (and this may be what you are already doing, just with the subquery in an actual separate query, which sometimes in Access has the best performance):

select
	a.Order,
	a.Line,
	a.Part_Id,
	a.Transaction_Id,
	a.Dist_No,
	a.Material, 
	a.Labor, 
	a.Burden, 
	a.Service, 
	(a.Material + a.Labor + a.Burden + a.Service) as Total
from 
	Sheet1 a
	inner join
	(
		select b.Order, b.Line, b.Part_Id, b.Transaction_Id, max(b.Dist_No) as MaxOfDist_No
		from Sheet1 b
		group by b.Order, b.Line, b.Part_Id, b.Transaction_Id
	) c
	on a.Order = c.Order
	and a.Line = c.Line
	and a.Part_Id = c.Part_Id
	and a.Transaction_Id = c.Transaction_Id
	and a.Dist_No = c.MaxOfDist_No

*Because I copied your data into Excel then imported it to MSAccess the name of the table in the examples is the name of an Access sheet - "Sheet1".

 

[TDC21]

Thanks again xenou,

I finally got it to work in my SQL so I guess I can just create a table view there and link access to that. I am assuming there are some differences between the language/opperators between Access and My SQL. I tried to build a Access Query from the working view by copying over the Query stmt with no luck.

SELECT TOP (100) PERCENT dbo.SHIPMENT_DETAIL.CUST_ORDER_ID, MAX(dbo.SHIPMENT_DETAIL.DIST_NO) AS DIST_NO, dbo.SHIPMENT_DETAIL.TRANSACTION_ID,
dbo.SHIPMENT_DETAIL.CUST_ORDER_LINE_NO AS LINE, dbo.SHIPMENT_DETAIL.PART_ID, MAX(derivedtbl_1.ACT_MATERIAL_COST) AS MATERIAL,
MAX(derivedtbl_1.ACT_LABOR_COST) AS LABOR, MAX(derivedtbl_1.ACT_BURDEN_COST) AS BURDEN, MAX(derivedtbl_1.ACT_SERVICE_COST) AS SERVICE

FROM dbo.SHIPMENT_DETAIL INNER JOIN
(SELECT CUST_ORDER_ID, CUST_ORDER_LINE_NO, DIST_NO, TRANSACTION_ID, PART_ID, PRODUCT_CODE, ACT_MATERIAL_COST, ACT_LABOR_COST,
ACT_BURDEN_COST, ACT_SERVICE_COST
FROM dbo.SHIPMENT_DETAIL AS SHIPMENT_DETAIL_1) AS derivedtbl_1 ON dbo.SHIPMENT_DETAIL.TRANSACTION_ID = derivedtbl_1.TRANSACTION_ID AND
dbo.SHIPMENT_DETAIL.DIST_NO = derivedtbl_1.DIST_NO


GROUP BY dbo.SHIPMENT_DETAIL.CUST_ORDER_ID, dbo.SHIPMENT_DETAIL.TRANSACTION_ID, dbo.SHIPMENT_DETAIL.CUST_ORDER_LINE_NO,
dbo.SHIPMENT_DETAIL.PART_ID


ORDER BY dbo.SHIPMENT_DETAIL.CUST_ORDER_ID, dbo.SHIPMENT_DETAIL.TRANSACTION_ID, DIST_NO

 

[TDC21]

I completed missed the 2nd part of your post but the just the first portion was very helpful by itself, I will take another look.

Thanks again

 

[TDC21]

Well, not all problems are solved the way we first anticipate. Still having some issues converting it back to access but the sub query approach in SQL is working great. Many thanks on the suggestion and the example so I could figure out how it works. One single Query for what I was expecting to be several. Apparently I love sub-queries lol

Thanks again Xenou, your the Man



SELECT TOP (100) PERCENT derivedtbl_5.NAME AS CUSTOMER, derivedtbl_4.CUSTOMER_ID, dbo.SHIPMENT_DETAIL.CUST_ORDER_ID AS [ORDER],
MAX(derivedtbl_2.PACKLIST_ID) AS PACKLIST, MAX(derivedtbl_3.INVOICE_ID) AS INVOICE, dbo.SHIPMENT_DETAIL.PART_ID, MAX(derivedtbl_2.SHIPPED_QTY)
AS SHIPPED_QTY, MAX(derivedtbl_1.ACT_MATERIAL_COST) AS MATERIAL, MAX(derivedtbl_1.ACT_LABOR_COST) AS LABOR,
MAX(derivedtbl_1.ACT_BURDEN_COST) AS BURDEN, MAX(derivedtbl_1.ACT_SERVICE_COST) AS SERVICE, MAX(derivedtbl_1.ACT_MATERIAL_COST)
+ MAX(derivedtbl_1.ACT_LABOR_COST) + MAX(derivedtbl_1.ACT_BURDEN_COST) + MAX(derivedtbl_1.ACT_SERVICE_COST) AS TOTAL_COSTS,
ROUND(MAX(derivedtbl_2.SHIPPED_QTY) * MAX(derivedtbl_2.UNIT_PRICE), 2) AS SALES, MAX(derivedtbl_3.SHIPPED_DATE) AS SHIPPED_DATE,
MAX(derivedtbl_3.INVOICED_DATE) AS INVOICE_DATE
FROM dbo.SHIPMENT_DETAIL INNER JOIN
(SELECT CUST_ORDER_ID, CUST_ORDER_LINE_NO, DIST_NO, TRANSACTION_ID, PART_ID, PRODUCT_CODE, ACT_MATERIAL_COST, ACT_LABOR_COST,
ACT_BURDEN_COST, ACT_SERVICE_COST
FROM dbo.SHIPMENT_DETAIL AS SHIPMENT_DETAIL_1) AS derivedtbl_1 ON dbo.SHIPMENT_DETAIL.TRANSACTION_ID = derivedtbl_1.TRANSACTION_ID AND
dbo.SHIPMENT_DETAIL.DIST_NO = derivedtbl_1.DIST_NO INNER JOIN
(SELECT CUST_ORDER_ID, CUST_ORDER_LINE_NO, TRANSACTION_ID, PACKLIST_ID, SHIPPED_QTY, UNIT_PRICE
FROM dbo.SHIPPER_LINE) AS derivedtbl_2 ON derivedtbl_1.CUST_ORDER_ID = derivedtbl_2.CUST_ORDER_ID AND
derivedtbl_1.CUST_ORDER_LINE_NO = derivedtbl_2.CUST_ORDER_LINE_NO AND derivedtbl_1.TRANSACTION_ID = derivedtbl_2.TRANSACTION_ID INNER JOIN
(SELECT PACKLIST_ID, SHIPPED_DATE, INVOICE_ID, INVOICED_DATE
FROM dbo.SHIPPER) AS derivedtbl_3 ON derivedtbl_2.PACKLIST_ID = derivedtbl_3.PACKLIST_ID INNER JOIN
(SELECT ID, CUSTOMER_ID
FROM dbo.CUSTOMER_ORDER) AS derivedtbl_4 ON derivedtbl_1.CUST_ORDER_ID = derivedtbl_4.ID INNER JOIN
(SELECT ID, NAME
FROM dbo.CUSTOMER) AS derivedtbl_5 ON derivedtbl_4.CUSTOMER_ID = derivedtbl_5.ID
GROUP BY dbo.SHIPMENT_DETAIL.CUST_ORDER_ID, dbo.SHIPMENT_DETAIL.TRANSACTION_ID, dbo.SHIPMENT_DETAIL.CUST_ORDER_LINE_NO,
dbo.SHIPMENT_DETAIL.PART_ID, derivedtbl_5.NAME, derivedtbl_4.CUSTOMER_ID
ORDER BY SHIPPED_DATE DESC, CUSTOMER, [ORDER]