Finding records for material that needs to be consolidated

[KelliganP]

Hi Folks,



I am brand new to the forums today and am using Access 2016.

I am trying to model some data work that a colleague has developed in Excel and want to know if the following scenario is possible with a query or if I am using the wrong tool.

I have a list of products that are stored in potentially numerous warehouses by National Stock Number (NSN), lot number, etc.

I need to list just the products that are the same lot number and NSN AND in different warehouses so that they can be positioned for consolidation into a single warehouse.

The table fields are listed below:
--------------------------------------
WHSE_ID (Warehouse identification code)
DODIC (Department of Defense Identification Code)
NSN (National Stock Number)
LOT (Lot number)
NOMEN (Nomenclature)
ACCT (Account)
CC (Condition Code)
AVAIL_QTY (Available Quantity)
REC_QTY (Received Quantity)
HAZ_CLS (Hazard Classification)
CG (Compatibility Group)
NEW (Net Explosive Weight)

The fields that make a particular amount of material unique is WHSE_ID, DODIC, NSN, LOT, and CC.

Therefore, all records that have the same DODIC, NSN, LOT, and CC should show up in the query results.

In built the following Find Duplicates query with the wizard but it does not eliminate the records where material is co-located in the same warehouse.

SELECT Query2.[DODIC], Query2.[NSN], Query2.[LOT], Query2.[CC], Query2.[WHSE_ID], Query2.[NOMEN], Query2.[ACCT], Query2.[AVAIL_QTY], Query2.[REC_QTY], Query2.[HAZ_CLS], Query2.[CG], Query2.[NEW]FROM Query2
WHERE (((Query2.[DODIC]) In (SELECT [DODIC] FROM [Query2] As Tmp GROUP BY [DODIC],[NSN],[LOT],[CC] HAVING Count(*)>1  And [NSN] = [Query2].[NSN] And [LOT] = [Query2].[LOT] And [CC] = [Query2].[CC])))
ORDER BY Query2.[DODIC], Query2.[NSN], Query2.[LOT], Query2.[CC];

I have not messed with access to any degree in over a decade so I hope I have asked the questions in such a way that they are understandable. Please let me know if I can provide any more clarity. I will provide a table with some sample data below.

DODIC	NSN	LOT	CC	WHSE_ID	NOMEN	ACCT	AVAIL_QTY	REC_QTY	HAZ_CLS	CG	NEW
A011	1305012328338	FC-00F001-004	B	N3834	CART 12G SHOTGUN	SOF	254	254	14	S	0.0000000000
A011	1305012328338	FC-00F001-004	B	N3834	CART 12G SHOTGUN	MAA	50	50	14	S	0.0000000000
A011	1305012328338	WCC05F072-002	E	1082D	CART 12G SHOTGUN	AEP	640	640	14	S	0.0000000000
A011	1305012328338	WCC05F072-002	E	5023J	CART 12G SHOTGUN	MAR	320	320	14	S	0.0000000000
A011	1305012328338	WCC08A078-005	A	N3834	CART 12G SHOTGUN	RET	2760	2760	14	S	0.0000000000
A011	1305012328338	WCC08A078-005	A	N3834	CART 12G SHOTGUN	WAR	25	25	14	S	0.0000000000
A011	1305012328338	WCC08H080-005	A	N3834	CART 12G SHOTGUN	RET	10	10	14	S	0.0000000000
A011	1305012328338	WCC08H080-005	A	N3834	CART 12G SHOTGUN	NSW	4590	4590	14	S	0.0000000000
A011	1305012328338	WCC08M080-013	A	N3834	CART 12G SHOTGUN	RET	320	320	14	S	0.0000000000
A011	1305012328338	WCC08M080-013	A	N3834	CART 12G SHOTGUN	SCA	640	640	14	S	0.0000000000
A011	1305012328338	WCC08M080-013	A	N3834	CART 12G SHOTGUN	SFI	30	30	14	S	0.0000000000
A011	1305012328338	WCC08M080-013	A	N3834	CART 12G SHOTGUN	SOF	1760	1760	14	S	0.0000000000
A011	1305012328338	WCC08M080-013	A	SP-04	CART 12G SHOTGUN	AEP	0	320	14	S	0.0000000000
A011	1305012328338	WCC09C080-018	A	N3834	CART 12G SHOTGUN	MAR	6560	6560	14	S	0.0000000000
A011	1305012328338	WCC09C080-018	A	N3834	CART 12G SHOTGUN	RET	320	320	14	S	0.0000000000
A011	1305012328338	WCC09J081-004	A	N1358	CART 12G SHOTGUN	MAR	184320	184320	14	S	0.0000000000
A011	1305012328338	WCC09J081-004	A	N3448	CART 12G SHOTGUN	MAR	30720	30720	14	S	0.0000000000
A011	1305012328338	WCC15C091-001	A	N3448	CART 12G SHOTGUN	SOF	4	4	14	S	0.0000000000
A011	1305012328338	WCC15C091-001	A	N3448	CART 12G SHOTGUN	TWR	2436	2436	14	S	0.0000000000
A011	1305012328338	WCC15C091-001	A	N3448	CART 12G SHOTGUN	LEV	320	320	14	S	0.0000000000
A011	1305012328338	WCC15C091-001	A	N3448	CART 12G SHOTGUN	TRA	1560	1560	14	S	0.0000000000
A011	1305012328338	WCC15C091-001	A	N3448	CART 12G SHOTGUN	OPL	22340	22340	14	S	0.0000000000
A017	1305012327415	WCC13J029-001	A	N7357	CTG 12GA NO 9 SHOT	OPL	4000	4000	14	S	0.0000000000
A017	1305012327415	WCC13J029-001	A	SP-04	CTG 12GA NO 9 SHOT	SFA	0	1590	14	S	0.0000000000
A023	1305013865604	RAC03K001-003	A	N7702	CARTRIDGE,12 GAGE SHOTGUN	RAI	250	250	14	S	0.0000000000
A023	1305013865604	RAC03K001-003	A	SP-04	CARTRIDGE,12 GAGE SHOTGUN	AEP	0	240	14	S	0.0000000000
A023	1305013865604	RAC14G010-001	A	N7702	CARTRIDGE,12 GAGE SHOTGUN	MAR	1200	1200	14	S	0.0000000000
A023	1305013865604	RAC14G010-001	A	SURV	CARTRIDGE,12 GAGE SHOTGUN	MAR	352	352	14	S	0.0000000000
A024	1305014315624	FC-06L100-001	A	N9988	CARTRIDGE,12 GAGE SHOTGUN	SOF	50	50	14	S	0.0000000000
A024	1305014315624	FC-06L100-001	A	N9988	CARTRIDGE,12 GAGE SHOTGUN	LEV	160	160	14	S	0.0000000000
A059	1305011555459	LC-08A381-977	A	Y7946	CTG 5.56MM BALL 10CLP	RAI	136400	136400	14	S	0.0000000000
A059	1305011555459	LC-08A381-977	A	Y7946	CTG 5.56MM BALL 10CLP	TRP	11520	11520	14	S	0.0000000000
A059	1305011555459	LC-09M387-377	A	K2620	CTG 5.56MM BALL 10CLP	RET	690	690	14	S	0.0000000000

Thanks and Regards,
Patrick

[ssanfu]

Hmmmm no response so I'll try - I'm not real strong with queries, but here goes.

Given the data set above, what do expect the result set to look like?
Out of the 33 records, I get 20. Is this close to what you want/expect?

[June7]

What is the output you want? Show a sample of desired output based on given example data.

[KelliganP]

Hi ssanfu and June7! Thanks for the replies. Of course it makes sense that I should have offered desired output! Here is a screenshot with some highlighting for clarity. You will notice that the critical fields are shaded and the groupings are separated by color. Also, the delta between grouped records is in the Warehouse field (bold grid). So, a grouping shows up because there is a set of like DoDICs, NSNs, LOTs, CCs, with greater than 1 WHSE_ID across the grouping. All of the other fields are necessary to display but are irrelevant for filtering purposes.

I hope this is more clear. Thank you for your patience.

[June7]

I don't see "d" group.

If you want to disregard duplicate WHSE_ID for each group when counting, this can get a bit complicated.

Build Query1 that returns distinct records for the 5 fields - don't include other fields in this query.

Use that query to build aggregate Query2 grouping on 4 fields that counts the WHSE_ID.

Build another query that joins Query2 to table on compound join of 4 fields with filter criteria under WhseCount of >1.

[KelliganP]

Sorry June7, that was supposed to be "a grouping".

I will try what you describe and let you know how it turns out.

Thanks!

[KelliganP]

Hi June7!

I am looking at the data and it seems that those queries did the job!

Thanks!