• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Query to filter items listed multiple times

MSACCESS 2003 Question:

I have q how too question about a query I am working on.

I have an Item Number (LIITM) that may be stored in more than one location (LILOCN)  in the warehouse. I will add a filter where LIPBIN = P.

Example:

LIITM      LILOCN      LIPBIN
1276105      C1PACK       P
1276210      C1PACK       P
1276339      C1PACK       P
1276339      S1DEIC       P
1276543      S1DEIC       P
1276666      S1DEIC       P
1276771      S1DEIC       P
1276885      S1DEIC       P

I only want to list the LITMs where there is more than one location (LILOCN). Example above is 1276339. This is the only LIITM I want to see and also listed only once, not twice.

Any help appreciated.

tw


SELECT PRODDTA_F41021.LIITM, PRODDTA_F41021.LILOCN, PRODDTA_F41021.LIPBIN
FROM PRODDTA_F41021
WHERE (((PRODDTA_F41021.LIMCU)="       31101") AND ((PRODDTA_F41021.LIPBIN)="P") AND ((PRODDTA_F41021.LIPQOH)<>0))
ORDER BY PRODDTA_F41021.LIITM;

Open in new window

0
Tom Winslow
Asked:
Tom Winslow
  • 3
  • 3
  • 2
  • +1
1 Solution
 
peter57rCommented:
Can you have more rhan one record for the same values of  LIITM   &   LILOCN      

So could you two records :
1276339      S1DEIC       P
1276339      S1DEIC       P
0
 
DhaestCommented:
SELECT myTable.LIITM
FROM myTable
GROUP BY myTable.LIITM, myTable.LILOCN
HAVING (((Count(myTable.LILOCN))>1));
0
 
Rey Obrero (Capricorn1)Commented:
try

SELECT Distinct PRODDTA_F41021.LIITM, PRODDTA_F41021.LIPBIN
FROM PRODDTA_F41021
WHERE (((PRODDTA_F41021.LIITM) In (SELECT [LIITM] FROM [PRODDTA_F41021] As Tmp GROUP BY [LIITM] HAVING Count(*)>1 )) AND ((PRODDTA_F41021.LIPBIN)="P"))
ORDER BY PRODDTA_F41021.LIITM;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Tom WinslowAuthor Commented:
Let me do a re-do on this one.

I may not have stated the question correctly.

LIPBIN can be either a P or and S. I only want to see P.
Some LIITMs will have P and S and/or More than one P.

If I have a situation like this:

LIITM      LIPBIN      LIMCU
1177844      P      31101
1177844      P      31101
1177844      S      31101
1177899      P      31101
1177899      P      31101
1177899      S      31101

I would want the result to be like this:

LIITM      LIPBIN      LIMCU
1177844      P      31101
1177899      P      31101

I need to know which LIITMs have more than one P.

The SQL with the recommended change above looks like this:

SELECT DISTINCT PRODDTA_F41021.LIITM, PRODDTA_F41021.LIPBIN, PRODDTA_F41021.LIMCU
FROM PRODDTA_F41021
WHERE (((PRODDTA_F41021.LIITM) In (SELECT [LIITM] FROM [PRODDTA_F41021] As Tmp GROUP BY [LIITM] HAVING Count(*)>1 )) AND ((PRODDTA_F41021.LIPBIN)="P") AND ((PRODDTA_F41021.LIMCU)="       31101"))
ORDER BY PRODDTA_F41021.LIITM;

Thanks,

Tw

SELECT DISTINCT PRODDTA_F41021.LIITM, PRODDTA_F41021.LIPBIN, PRODDTA_F41021.LIMCU
FROM PRODDTA_F41021
WHERE (((PRODDTA_F41021.LIITM) In (SELECT [LIITM] FROM [PRODDTA_F41021] As Tmp GROUP BY [LIITM] HAVING Count(*)>1 )) AND ((PRODDTA_F41021.LIPBIN)="P") AND ((PRODDTA_F41021.LIMCU)="       31101"))
ORDER BY PRODDTA_F41021.LIITM;

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
so is there any problem with the query


SELECT DISTINCT PRODDTA_F41021.LIITM, PRODDTA_F41021.LIPBIN, PRODDTA_F41021.LIMCU
FROM PRODDTA_F41021
WHERE (((PRODDTA_F41021.LIITM) In (SELECT [LIITM] FROM [PRODDTA_F41021] As Tmp GROUP BY [LIITM] HAVING Count(*)>1 )) AND ((PRODDTA_F41021.LIPBIN)="P") AND ((PRODDTA_F41021.LIMCU)="       31101"))
ORDER BY PRODDTA_F41021.LIITM;
0
 
Tom WinslowAuthor Commented:
I think the problem is not with the query but it is with my inability to properly define the problem.

In the sample F41021 table below, the first Query_1 would return ONLY LIITM = 1217033 because LIPBIN has two occurrences of P. I do not want to know if there is only one occurrence of P and I do not care if LIPBIN = S. I only want to see that, on this sample table, 1271033 has two occurrences of P.

In the second Query_2 I want to know that 15300107 has only LIPBIN = S and NO LIPBIN = P. Example, 12166761 has both P and S. I do not want to see 12166761 at all.

Table F41021
LIITM      LIPBIN      LIMCU      LILOCN      LILOTN
1216671      P      31101      S1VSAT       
1216671      S      31101      000065BKFL       
1216671      S      31101      000072BKFL       
1216671      S      31101      000075BKFL       
1216971      P      31101            
1216989      P      31101            
1216997      P      31101            
1217009      P      31101            
1217009      S      31101      S1DEIC       
1217009      S      31101      S1VSAT       
1217017      P      31101            
1217033      S      31101            
1217033      P      31101            
1217033      P      31101            
15300107      S      31101            
15300107      S      31101      C1GAPK       
15300107      S      31101      C1LGAP       

So, I am really trying to write two queries to look at the same table and answer two separate questions.

Tw
0
 
Rey Obrero (Capricorn1)Commented:
so that we are clear to what you want to see, from table data you posted above

post the results you want to see for
query1

result here


query2

result here
0
 
Tom WinslowAuthor Commented:
The results from Query_1 would be: 1217033

The results from Query_2 would be: 15300107

0
 
DhaestCommented:
First query:
----------------
SELECT F41021.LITM
FROM F41021
GROUP BY F41021.LITM, F41021.LIPBIN
HAVING (((F41021.LIPBIN)="P") AND ((Count(F41021.LIPBIN))>1));

Second query:
------------------
SELECT F41021.LITM
FROM F41021
GROUP BY F41021.LITM, F41021.LIPBIN
HAVING (((F41021.LIPBIN)="S"))
AND F41021.LITM
NOT IN (
SELECT F41021.LITM
FROM F41021
GROUP BY F41021.LITM, F41021.LIPBIN
HAVING (((F41021.LIPBIN)="P")))
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now