[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2010-11-18
16
Medium Priority
?
317 Views
Last Modified: 2012-08-14
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
Comment
Question by:Tom Winslow
  • 8
  • 7
16 Comments
 
LVL 5

Expert Comment

by:bboswell
ID: 34168306
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34168379

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
 

Author Comment

by:Tom Winslow
ID: 34170048
Uploading top 5 records.
F41021Sample.xls
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34170054
TomWinslow,

did you try the query i posted?
0
 

Author Comment

by:Tom Winslow
ID: 34170074
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34170087
the sample file you uploaded will not return any record because there is no record with LiPBIN  = 'S'
0
 

Author Comment

by:Tom Winslow
ID: 34170144
Hmmm...

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

tw

F41021Sample2.xls
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34170152
can you post the query you are using.
0
 

Author Comment

by:Tom Winslow
ID: 34170183
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34170185
that is not the query i posted.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34170194
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
 

Author Comment

by:Tom Winslow
ID: 34170195
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34170205
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
 

Author Comment

by:Tom Winslow
ID: 34170238
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34170334


see this sample db, run query1
db1.mdb
0
 

Author Closing Comment

by:Tom Winslow
ID: 34174889
Thanks.

tw
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question