Solved

Query to filter items listed multiple times

Posted on 2008-06-20
9
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 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