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.
Code:
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