Searching for idential data within the same field

C

caperry123

Hello, I am an admitted novice to Access. My company is trying to se
up a database application to track the inventory levels of differen
parts. We have a field titled "Part Number", and want to search fo
any occurences of identical part numbers showing up in different row
in that one field. If identical part numbers are found, we want t
modify the information in a different field of that row in which
match was found. So, for example, if we find three parts named "A", w
want to change the number in the Inventory Qty Field for the second "A
to 5, and the number in the Inventory Qty Field for the third "A" to 6
Basically, at this point we are stuck on finding a way to searc
through the single field "Part Number" and find identical part numbers
We've tried using loops in VBA, the macros Find and Find Next (whic
produce an Automation class error), and SQL. Any general ideas on th
direction which we should take to solve this problem? Your help woul
be much appreciated
 
B

Barb

hi caperry123
look into find duplicates query (you can make it with the wizard), that
should get you started.

Thanks
Barb
 
G

Gary Walter

"caperry123"wrote
Hello, I am an admitted novice to Access. My company is trying to set
up a database application to track the inventory levels of different
parts. We have a field titled "Part Number", and want to search for
any occurences of identical part numbers showing up in different rows
in that one field. If identical part numbers are found, we want to
modify the information in a different field of that row in which a
match was found. So, for example, if we find three parts named "A", we
want to change the number in the Inventory Qty Field for the second "A"
to 5, and the number in the Inventory Qty Field for the third "A" to 6.
Basically, at this point we are stuck on finding a way to search
through the single field "Part Number" and find identical part numbers.
We've tried using loops in VBA, the macros Find and Find Next (which
produce an Automation class error), and SQL. Any general ideas on the
direction which we should take to solve this problem? Your help would
be much appreciated.
Click to expand...
Hi caperry,

I might first look at my table design...
why can I have more than one record for a part
in a table that keeps track of Inventory Qty??

I work mostly in the "book world" where we
may have more than one record for the same
ISBN (don't get me started)...maybe your situation
is similar?

If it is, I might suggest adding one more field to your
table, say "PartKey" type Long.

if my table looked similar to "tblCap" below

ID PartNumber PartKey
1 A

2 A

3 A

4 B

5 C

6 C



then I ran the following query

UPDATE tblCap AS t1
SET t1.PartKey =
DCount("*","tblCap","[PartNumber] ='" & [t1].[PartNumber]
& "' AND [ID] <" & [t1].[ID])+1;

ID PartNumber PartKey
1 A 1
2 A 2
3 A 3
4 B 1
5 C 1
6 C 2


then it should be easy to do what
you must.....

but, first, please rethink your table design...

for sure, do not give the field InventoryQty
"2 different jobs."

good luck,

gary
 
Top