Source
r/SQL•
errorcontactinghost

[MS Access] Best way to combine these two queries

I am relatively new to SQL and I am looking for a way to combine two simple queries that I wrote. I work in a distribution center and I am trying to pull from our WMS database the number of pick lists that maxed out based on certain parameters.

My first query selects a wave number, the number of pick lists for each wave, and then sums the number of units and locations on each pick list, and then groups by the wave:

SELECT [XHWVE#] AS [Wave Num], count([XHPKL#]) AS [Total PLs], SUM(XHTLUN) AS  [Total Units], SUM(XHTLPK) AS [Total Locs]
FROM TABLE_NAME
WHERE XHORG = '330'
and [XHWVE#] between [Enter low wave value] and [Enter high wave value]
GROUP BY [XHWVE#]
ORDER BY [XHWVE#];

My other query just counts the number of lists that exceed a threshold (either number of units or locations) based on a wave range.

SELECT count([XHPKL#]) AS [Lists Over Max]
FROM TABLE_NAME
WHERE [XHPKL#] in
(select [XHPKL#]
from TABLE_NAME
where XHORG = '330'
and [XHWVE#] between [Enter low wave value] and [Enter high wave value]
and (XHTLPK >= [Max Pick Parameter] or XHTLUN >= [Max Unit Parameter]));

What is the best way to combine these? I'd like to have all of the results from the first query (wave number, count of lists, sum of units, sum of locations) and in another field show how many lists hit the thresholds. Any input is much appreciated.

Posted May 4, 2016, 7:19 PM UTC

14
Alaendil•

This is based off of the thought that each Wave has its own lists that could be over max, but that may not be true.

SELECT 
	TN.[XHWVE#] AS [Wave Num]
	, LM.[Lists Over Max]
	, count(TN.[XHPKL#]) AS [Total PLs]
	, SUM(TN.XHTLUN) AS  [Total Units]
	, SUM(TN.XHTLPK) AS [Total Locs]

FROM TABLE_NAME TN

Left Join (SELECT 
				[XHWVE#]
				, count([XHPKL#]) AS [Lists Over Max]
				FROM TABLE_NAME
				WHERE [XHPKL#] in (select 
										[XHWVE#]
										, [XHPKL#]
									from TABLE_NAME
									where XHORG = '330'
										and (XHTLPK >= [Max Pick Parameter] 
											or XHTLUN >= [Max Unit Parameter])
									)
				group by [XHWVE#] 
			) LM
on LM.[XHWVE#] = TN.[XHWVE#]

WHERE TN.XHORG = '330'
and TN.[XHWVE#] between [Enter low wave value] and [Enter high wave value]

GROUP BY TN.[XHWVE#]
	,LM.[Lists Over Max]

ORDER BY TN.[XHWVE#];
1
errorcontactinghostOP•

Thanks for your reply! I know it can be tough to help with queries for a database you've never seen before. If I try to run that (adjusting for the table names of course) Access gives me this error:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

How can I get around this? Also I don't see where you named the table LM, so which subquery do I use that alias?

1
Alaendil•

Oh, that was my fault .. I messed up some of it before I formatted it and saw the subselect in the where I also put as next to my aliases this time around

SELECT 
	TN.[XHWVE#] AS [Wave Num]
	, LM.[Lists Over Max]
	, count(TN.[XHPKL#]) AS [Total PLs]
	, SUM(TN.XHTLUN) AS  [Total Units]
	, SUM(TN.XHTLPK) AS [Total Locs]

 FROM TABLE_NAME as TN 

Left Join (SELECT 
				TN.[XHWVE#]
				, count(TN.[XHPKL#]) AS [Lists Over Max]
				 FROM TABLE_NAME as TN 
				WHERE TN.[XHPKL#] in (select 
									TN2.[XHPKL#]
									from TABLE_NAME as TN2 
									where TN.[XHWVE#] = TN2.[XHWVE#] 
									        and TN2.XHORG = '330'
										and (TN2.XHTLPK >= [Max Pick Parameter] 
											or TN2.XHTLUN >= [Max Unit Parameter])
									)
				group by TN.[XHWVE#] 
			) as LM
on LM.[XHWVE#] = TN.[XHWVE#]

WHERE TN.XHORG = '330'
and TN.[XHWVE#] between [Enter low wave value] and [Enter high wave value]

GROUP BY TN.[XHWVE#]
	,LM.[Lists Over Max]

ORDER BY TN.[XHWVE#];
1
errorcontactinghostOP•

You rock!! Thanks!

1