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

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?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
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

SteveL13Author 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]
SimonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.