We help IT Professionals succeed at work.

Query to return no record if part # is only one time

SteveL13
SteveL13 asked
on
I have a query that returns Id, RecordDate, PartN, and Qty.  But if there is only one record for PartN (a text field) I don't want the record to be included in the query results.

In other words, I only want the records that have PartN more than one time to be included.

How can this be accomplished?
Comment
Watch Question

SimonPrincipal Analyst

Commented:
This should work. Replace both occurences of  't1' with the name of your table

select Id, RecordDate, PartN, and Qty from t1 inner join (select id from t1 
group by partN
having count(*) >1) as dtbl on t1.id=dtbl.id

Open in new window

Author

Commented:
Sorry, I was looking at the wrong query.  The one I need has ore fields.  Her is what I have but I get a syntax error.

select Ack_Date, DateSerial, Part_No,  [Record ID], Order_Qty, CastQty, ScrapQty, QtyLeftToCast, and Date from qryQtyLeftToCastForProduction inner join (select [Record ID] from qryQtyLeftToCastForProduction
group by part_No
having count(*) >1) as dtbl on qryQtyLeftToCastForProduction.[Record id]=dtbl.[Record id]
Principal Analyst
Commented:
1. My code above was wrong as I'd used the wrong column in the subquery...

2. You have a odd ',and Date' in your select field list that I've omitted from the column list:

Please try:
select Ack_Date, DateSerial, Part_No,  Q.[Record ID], Order_Qty, CastQty, ScrapQty, QtyLeftToCast 
from qryQtyLeftToCastForProduction Q inner join 
  (select [Part_No] from qryQtyLeftToCastForProduction
  group by part_No
  having count(*) >1) as dtbl 
on Q.[Part_No]=dtbl.[Part_No] 

Open in new window

Hamed NasrRetired IT Professional

Commented:
Try this example for table b (aid, bid, v), use your fields where v represents Part_No
aID bID v
1      1      5
1      2      5
1      3      4
1      4      3
2      1      3
2      2      3
2      3      8
3      1      8
3      2      8
4      1      7

Query
SELECT b.aID, b.bID, b.v
FROM b
WHERE ((((Select Count(v) from b x where b.v=x.v))>1));

Open in new window

Output
aID bID v
1      1      5
1      2      5
1      4      3
2      1      3
2      2      3
2      3      8
3      1      8
3      2      8