Solved

Query to filter items listed multiple times

Posted on 2008-06-20
9
314 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 119

Expert Comment

by:Rey Obrero
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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now