[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

MS Access Query to find items that are S but not P

I have an Item Master Location File (F41021).
Items in the table can have a Primary Location indicated by LPBIN = ‘P’ and
The items can have a Secondary Location indicated by LPBIN  = ‘S’.

There are some Items (LIITM) that have a Secondary Location (LPBIN  = ‘S’) but have no Primary Location (LPBIN = ‘P’).

I am having a ‘Brain Cloud’ and cannot remember how I created this MS Access Query once before.

Basically, I am trying to Query the F41021 table and create a list of item numbers (LIITM) that have a Secondary Location (S) but have no Primary Location (P).

Can you help me jog my memory?

SELECT PRODDTA_F41021.LIITM, PRODDTA_F41021.LIMCU, PRODDTA_F41021.LIPBIN
FROM PRODDTA_F41021
GROUP BY PRODDTA_F41021.LIITM, PRODDTA_F41021.LIMCU, PRODDTA_F41021.LIPBIN
HAVING (((PRODDTA_F41021.LIMCU)="       31101"));

Tw
0
Tom Winslow
Asked:
Tom Winslow
  • 8
  • 7
1 Solution
 
bboswellCommented:
Can you possibly give us an extract or example of the table to be checked... say 5-10 rows this will help to understand better.
0
 
Rey Obrero (Capricorn1)Commented:

try this query


select *
from PRODDTA_F41021
where LPBIN  = 'S' and LIITM not in (select LIITM from PRODDTA_F41021 as P where  LPBIN  = 'P')
0
 
Tom WinslowAuthor Commented:
Uploading top 5 records.
F41021Sample.xls
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Rey Obrero (Capricorn1)Commented:
TomWinslow,

did you try the query i posted?
0
 
Tom WinslowAuthor Commented:
Yes. Could not make it work.

It runs but finds no data.

select *
from PRODDTA_F41021
where LiPBIN  = 'S' and LIITM not in (select LIITM from PRODDTA_F41021 as P where  LiPBIN  = 'P')

tw
0
 
Rey Obrero (Capricorn1)Commented:
the sample file you uploaded will not return any record because there is no record with LiPBIN  = 'S'
0
 
Tom WinslowAuthor Commented:
Hmmm...

I messed up with only 5 records. This file contains 1370 records and some examples.

tw

F41021Sample2.xls
0
 
Rey Obrero (Capricorn1)Commented:
can you post the query you are using.
0
 
Tom WinslowAuthor Commented:
SELECT PRODDTA_F41021.LIITM, PRODDTA_F41021.LIMCU, PRODDTA_F41021.LIPBIN
FROM PRODDTA_F41021
WHERE (((PRODDTA_F41021.LIMCU)="       31101") AND ((PRODDTA_F41021.LIPBIN)="P" And Not (PRODDTA_F41021.LIPBIN)="S"));
0
 
Rey Obrero (Capricorn1)Commented:
that is not the query i posted.
0
 
Rey Obrero (Capricorn1)Commented:
copy and paste this query


SELECT *
FROM PRODDTA_F41021
WHERE LIPBIN  = 'S' and LIITM not in (select LIITM from PRODDTA_F41021 as P where  LIPBIN  = 'P');
0
 
Tom WinslowAuthor Commented:
Oh. This is the one you posted.

SELECT *
FROM PRODDTA_F41021
WHERE (((PRODDTA_F41021.LIPBIN)='S') AND ((PRODDTA_F41021.LIITM) Not In (select LIITM from PRODDTA_F41021 as P where  LiPBIN  = 'P')));

I added one more filter:

SELECT *
FROM PRODDTA_F41021
WHERE (((PRODDTA_F41021.LIPBIN)='S') AND ((PRODDTA_F41021.LIITM) Not In (select LIITM from PRODDTA_F41021 as P where  LiPBIN  = 'P')))
GROUP BY PRODDTA_F41021.LIMCU
HAVING (((PRODDTA_F41021.LIMCU) Like "*31101"));

tw

0
 
Rey Obrero (Capricorn1)Commented:
this query return 3 records


SELECT *
FROM PRODDTA_F41021
WHERE LIPBIN  = 'S' and LIMCU Like '*31101' and LIITM not in (select LIITM from PRODDTA_F41021 as P where  LIPBIN  = 'P');
0
 
Tom WinslowAuthor Commented:
I pasted the SQL into the Access  query but it still returns no records. I do not understand. The Query looks good.

tw

ScreenShot.bmp
ScreenShot1.bmp
0
 
Rey Obrero (Capricorn1)Commented:


see this sample db, run query1
db1.mdb
0
 
Tom WinslowAuthor Commented:
Thanks.

tw
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now