Culling unique records out in a query

[Luke]

Hello -

I have a table with inventory components with a unique primary key
assigned by Access and a StockID that can be duplicated (because some
items in stock are used with different products and must be kept separate). I
would like to do a query in order to make a report that only shows the
records pertaining to StockID's that appear more than once (and not telling
Access in advance what those numbers are). I was trying to do this with a
query, but maybe I need to get into SQL or VB??

For example -

With a table like this:

PrimKey (autonumber) StockID Item Name Project etc
1 111 container 1 etc
2 222 box 1 etc
3 333 paper 1 etc
4 444 pen 2 etc
5 222 box 2 etc
6 555 tape 2 etc

The exact same box is used for both Project 1 & 2, so it has the same
StockID. In order to keep up with these types of items, I would like to drop
them into results. I need to do a query that will cull out StockID's 111, 333,
444, and 555 - they are unique. I need to get the two records that have 222.
It's not as simple as just asking if a record has a value of - say - "222" I'm
leaving this database with others, so I need Access to detect which record
StockID's have duplicate entries and drop them out of the query into the
results. How can I do that automatically?

[Note: I'm new to Access and using the graphical front-end of it . SQL
doesn't make complete sense to me (yet).]

Thanks for any help -

Luke Latham

 

[Nikos Yannacopoulos]

Luke,

Make a query on the table, and while in design view type the following in
the first criterion line under the StockID field:

In (SELECT StockID FROM tblInventory GROUP BY StockID HAVING
Count(StockID)>1)

Where I have assumed the table name to be tblInventory, and the field name
to be StockID; change as required.

The expression in brackets is actually an SQL query that returns StockID's
that occur more than once. Using it in a query criterion makes it a
subquery.

HTH,
Nikos

Hello -

I have a table with inventory components with a unique primary key
assigned by Access and a StockID that can be duplicated (because some
items in stock are used with different products and must be kept separate).
I
would like to do a query in order to make a report that only shows the
records pertaining to StockID's that appear more than once (and not telling
Access in advance what those numbers are). I was trying to do this with a
query, but maybe I need to get into SQL or VB??

For example -

With a table like this:

PrimKey (autonumber) StockID Item Name Project etc
1 111 container 1 etc
2 222 box 1 etc
3 333 paper 1 etc
4 444 pen 2 etc
5 222 box 2 etc
6 555 tape 2 etc

The exact same box is used for both Project 1 & 2, so it has the same
StockID. In order to keep up with these types of items, I would like to drop
them into results. I need to do a query that will cull out StockID's 111,
333,
444, and 555 - they are unique. I need to get the two records that have 222.
It's not as simple as just asking if a record has a value of - say - "222"
I'm
leaving this database with others, so I need Access to detect which record
StockID's have duplicate entries and drop them out of the query into the
results. How can I do that automatically?

[Note: I'm new to Access and using the graphical front-end of it . SQL
doesn't make complete sense to me (yet).]

Thanks for any help -

Luke Latham

 

[Luke]

Nikos -

THANKS!!!!! I can't wait to get in to the office to apply the expression!

Luke