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
Tom WinslowAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:


see this sample db, run query1
db1.mdb
0
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Tom WinslowAuthor Commented:
Uploading top 5 records.
F41021Sample.xls
0
 
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
 
Tom WinslowAuthor Commented:
Thanks.

tw
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.