Filtering question

[craigmorley]

I am trying to filter a column of an Access table so that it shows ONLY
records which appear more than once in the table. Other than manually
looking through the entire table, is there a way to filter out all
instances of records which occur only once?

All help gratefully received!!!

 

[Nikos Yannacopoulos]

Craig,

Yes, there is. Create select a query on the table, and use a subquery to
filter on the desired field, i.e. type something like the following
expression in the criteria line under the field in question (State in my
example):

In (Select State from Customers Group by State Having Count(State) > 1)

where I have assumed that the table is called Customers, and the field in
question is called State; substitute as appropriate.

HTH,
Nikos

 

[craigmorley]

Nikos, thanks for your help but I still can't get the query to run as
need. If I give a specific example, can you (or anyone else) pleas
try and help me further?

I have 2 fields, PART NUMBER and INCLUDE. The "part number" is
unique product code for our products. The "include" is the location i
our warehouse where the product is to be picked from. The full table i
currently something like this:

PART NUMBER / INCLUDE
123 A10
123 B17
123 C20
1497 A12
1497 A19
168 B12
174 D11

Each part number should only have 1 INCLUDE record as there should onl
be 1 location that it is to be picked from for an order. Therefore,
want to report on any occurances of a part number having more than
INCLUDE code allocated to it.

Ideally, if filtered properly, the query would return a table whic
looks as follows (based an my earlier example):

PART NUMBER / BIN CODE
123 A10
123 B17
123 C20
1497 A12
1497 A19

Part numbers 168 and 174 would be excluded from the report as the
correctly only have 1 INCLUDE location.

Thanks again in advance of anyone's help.

Regards,
Crai

 

[Nikos Yannacopoulos]

Craig,

But that's exactly what it does!
I put your sample data in a table and made the query. The criterion on the
PART NUMBER field is:

In (Select [PART NUMBER] From tblName Group by [PART NUMBER] Having
Count([PART NUMBER]) > 1)

where, of course, you need to change "tblName" (that's what I called the
table, not knowing its name) to the actual table name.

HTH,
Nikos

 

[craigmorley]

Don't worry, problem solved. I've figured it out and it works fine!!

 

[craigmorley]

Nikos,

I think I entered the wording incorrectly when I tried it. I manage
to solve my problem by creating a query with criteria of count>1 on th
required field followed by a second query which simply returned the
fields in question. This filtered out the unique records and left onl
the duplicates.

I'm still learning about Access so your help is very much appreciated.

Regards,
Crai