• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • 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
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!

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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