asked on

Aggregate value is used twice in query barcode scanning app

Hello,

I have a barcode scanning app that has a problem with some aggregates being "used" twice.

There are some nested queries that look like this:

Query to sum up the quantities of barcodes pulled/scanned-

SELECT tblFixedAssets.Staff_ID
	,Count(tblFixedAssets.Staff_ID) AS CountOfStaff_ID
FROM tblPulls
INNER JOIN tblFixedAssets ON tblPulls.ItemBarCode = tblFixedAssets.fldBarCode
WHERE (
		((tblPulls.Job_Num) = [forms] ! [frmI-O] ! [JobToPull])
		AND ((tblPulls.PulledStatus) = 'O')
		)
GROUP BY tblFixedAssets.Staff_ID;

Open in new window

Then a query with the items on the job with the scan results-

SELECT tblJobsLineItems.Job_Num
	,tblJobsLineItems.[Item Number]
	,First(tblJobsLineItems.Category) AS fldCategory
	,First(tblJobsLineItems.Item_Description) AS fldItem_Description
	,Sum(tblJobsLineItems.Quantity) AS fldQuantity
	,Count(tblJobsLineItems.[Item Number]) AS ROWCOUNT
	,First(tblJobsLineItems.fldKit) AS FirstOffldKit
	,Sum(Nz([CountOfStaff_ID])) AS fldPulled
	,tblJobsLineItems.ParentID
FROM tblJobsLineItems
LEFT JOIN [qryI-O-PulledByJob] ON tblJobsLineItems.[Item Number] = [qryI-O-PulledByJob].Staff_ID
WHERE (((tblJobsLineItems.Job_Num) = [forms] ! [frmI-O] ! [JobToPull]))
GROUP BY tblJobsLineItems.Job_Num
	,tblJobsLineItems.[Item Number]
	,tblJobsLineItems.ParentID
ORDER BY tblJobsLineItems.[Item Number]
	,First(tblJobsLineItems.Category);

Open in new window

See the results of these two below.

So, the question becomes, how can I structure this query to display the correct "items pulled/scanned"?

The posted queries are access sql, but the backend is ms sql, so fixes in either syntax could work.

Thank you

Pawan Kumar

SELECT tblFixedAssets.Staff_ID
	,Count(DISTINCT tblFixedAssets.Staff_ID) AS CountOfStaff_ID
FROM tblPulls
INNER JOIN tblFixedAssets ON tblPulls.ItemBarCode = tblFixedAssets.fldBarCode
WHERE (
		((tblPulls.Job_Num) = [forms] ! [frmI-O] ! [JobToPull])
		AND ((tblPulls.PulledStatus) = 'O')
		)
GROUP BY tblFixedAssets.Staff_ID;

Open in new window

SELECT tblJobsLineItems.Job_Num
	,tblJobsLineItems.[Item Number]
	,First(tblJobsLineItems.Category) AS fldCategory
	,First(tblJobsLineItems.Item_Description) AS fldItem_Description
	,Sum(tblJobsLineItems.Quantity) AS fldQuantity
	,Count(tblJobsLineItems.[Item Number]) AS ROWCOUNT
	,First(tblJobsLineItems.fldKit) AS FirstOffldKit
	,Sum(DISTINCT Nz([CountOfStaff_ID])) AS fldPulled
	,tblJobsLineItems.ParentID
FROM tblJobsLineItems
LEFT JOIN [qryI-O-PulledByJob] ON tblJobsLineItems.[Item Number] = [qryI-O-PulledByJob].Staff_ID
WHERE (((tblJobsLineItems.Job_Num) = [forms] ! [frmI-O] ! [JobToPull]))
GROUP BY tblJobsLineItems.Job_Num
	,tblJobsLineItems.[Item Number]
	,tblJobsLineItems.ParentID
ORDER BY tblJobsLineItems.[Item Number]
	,First(tblJobsLineItems.Category);

Open in new window

pressMac

ASKER

one more note. There is one more level of query on top to get this back in the right category and have the correct parent id.

SELECT [qryI-O_rowsNEW].fldCategory
	,[qryI-O_rowsNEW].[Item Number]
	,[qryI-O_rowsNEW].Job_Num
	,[qryI-O_rowsNEW].fldItem_Description
	,[qryI-O_rowsNEW].fldQuantity
	,[qryI-O_rowsNEW].fldPulled
	,[qryI-O_rowsNEW].ROWCOUNT
	,[qryI-O_rowsNEW].FirstOffldKit
FROM [qryI-O_rowsNEW]
WHERE ((([qryI-O_rowsNEW].fldCategory) <> "9-Discount"))
ORDER BY [qryI-O_rowsNEW].fldCategory
	,[parentid] & [item number];

Open in new window

Pawan Kumar

Do you want to change the above query ? any issue with above ?

pressMac

ASKER

Hello Pawan,

The "Sum(DISTINCT Nz([CountOfStaff_ID])) AS fldPulled' causes a syntax error that I have not been able to work around.

pressMac

ASKER

Hello Pawan,

I re-wrote in ms sql and nested the queries and see that DISTINCT solves the problem of the re-use of that value. But, my sorting and grouping is not working now.

With qryPulledByJob as (

SELECT tblFixedAssets.Staff_ID
	,Count(tblFixedAssets.Staff_ID) AS CountOfStaff_ID

FROM tblPulls
INNER JOIN tblFixedAssets ON tblPulls.ItemBarCode = tblFixedAssets.fldBarCode
WHERE (
		((tblPulls.Job_Num) = 366973)
		AND ((tblPulls.PulledStatus) = 'O')
		)
GROUP BY tblFixedAssets.Staff_ID
)

SELECT tblJobsLineItems.[Item Number]
	,Sum(tblJobsLineItems.Quantity) fldQuantity
	,Count(tblJobsLineItems.[Item Number]) totQty
	,Sum(DISTINCT [CountOfStaff_ID]) AS fldPulled
FROM tblJobsLineItems
LEFT JOIN [qryPulledByJob] ON tblJobsLineItems.[Item Number] = [qryPulledByJob].Staff_ID
WHERE (((tblJobsLineItems.Job_Num) = 366973))
GROUP BY tblJobsLineItems.[Item Number]

Open in new window

pressMac

ASKER

So, in attempting to get the categories back together, adding another group by spoils the count.

With qryPulledByJob as (

SELECT tblFixedAssets.Staff_ID
	,Count(tblFixedAssets.Staff_ID) AS CountOfStaff_ID

FROM tblPulls
INNER JOIN tblFixedAssets ON tblPulls.ItemBarCode = tblFixedAssets.fldBarCode
WHERE (
		((tblPulls.Job_Num) = 366973)
		AND ((tblPulls.PulledStatus) = 'O')
		)
GROUP BY tblFixedAssets.Staff_ID
)

SELECT tblJobsLineItems.[Item Number]
	,Sum(tblJobsLineItems.Quantity) fldQuantity
	,Count(tblJobsLineItems.[Item Number]) totQty
	,Sum(DISTINCT [CountOfStaff_ID]) AS fldPulled
FROM tblJobsLineItems
LEFT JOIN [qryPulledByJob] ON tblJobsLineItems.[Item Number] = [qryPulledByJob].Staff_ID
WHERE (((tblJobsLineItems.Job_Num) = 366973))
GROUP BY tblJobsLineItems.[Item Number]
,tblJobsLineItems.Category

Open in new window

Here are results.

Stand-Speaker	2	1	NULL
Stand-Speaker Cover Black	2	1	NULL
IEC-C13-6ft	1	1	2
Sam-TM300	1	1	1
Batt-9V	4	1	NULL
Shu-UA400B	4	1	NULL
Shu-UHF	2	1	NULL
Shu-ULX1-BP	2	1	NULL
Shu-ULX2-58	2	1	NULL
Shu-ULX-S-PS	2	1	NULL
Shu-WL184	2	1	NULL
XLR-10	2	1	NULL
Laptop	1	1	NULL
Laptop-PS-L90w	1	1	NULL
Mouse-Wired	1	1	NULL
Cue-IR	1	1	NULL
DVIm-HDMIf	1	1	NULL
Evision-4500-rm	1	1	NULL
IEC-C13-6ft	1	1	2
PJ-5000-WUXGA	1	1	1

Open in new window

PatHartman

Create a query that groups the distinct Staff_ID values. Then count that query. OR use a pass-through query that uses the SQL Server syntax which does support the count distinct.

Pawan Kumar

What different result you want from the last one you commented?

pressMac

ASKER

The problem is the displaying the data. So the query works, but when it is displayed with top level query with the correct sorting and distinct job line items it does not work. The top level basically undoes the subquery.

Pawan Kumar

You need to do like below--

SELECT * FROM
(
SELECT DISTINCT .....
FROM
.........
)y
ORDER BY....

pressMac

ASKER

I have tried many variations, but I either get the correct counts or the desired formatting.

 SELECT DISTINCT
 tbljobslineitems.category
,tblJobsLineItems.parentid
,tblJobsLineItems.[Item Number]
,Quantity
,qryPulledByJob.SumPull
,concat(ParentID, [Item Number]) Sorting
FROM tblJobsLineItems

	LEFT JOIN (
			SELECT tblFixedAssets.Staff_ID
				,Count(tblFixedAssets.Staff_ID) AS SumPull
				,Max(Job_Num) AS Job_Num
			FROM tblPulls
			INNER JOIN tblFixedAssets ON tblPulls.ItemBarCode = tblFixedAssets.fldBarCode
			WHERE (
					((tblPulls.Job_Num) = 366973)
					AND ((tblPulls.PulledStatus) = 'O')
					)
			GROUP BY tblFixedAssets.Staff_ID	
	
			) AS qryPulledByJob ON tblJobsLineItems.[Item Number] = qryPulledByJob.Staff_ID

	
	WHERE tblJobsLineItems.Job_Num=366973

	ORDER BY Category, concat(ParentID,[Item Number])

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.