Solved

Query to filter items listed multiple times

Posted on 2008-06-20
9
343 Views
Last Modified: 2011-10-03
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
Comment
Question by:Tom Winslow
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 21830781
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 21830819
SELECT myTable.LIITM
FROM myTable
GROUP BY myTable.LIITM, myTable.LILOCN
HAVING (((Count(myTable.LILOCN))>1));
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21830962
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

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

Author Comment

by:Tom Winslow
ID: 21835443
The results from Query_1 would be: 1217033

The results from Query_2 would be: 15300107

0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 21853029
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.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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