Only allow duplicates of certain responses but not others (1 Viewer)

Mr. SLP

Registered User.
Local time
Today, 06:15
Joined
Dec 23, 2017
Messages
56
I’m setting up an inventory database. Some/most of the items will have barcodes but others do not, and it’s not practical to barcode them. Is there a way to generally not allow duplicates in a field but allow a small handful of things to be entered multiple times in the field with multiple records? Is this something I would need to do at the form level or the table level?


Sent from my iPhone using Tapatalk
 

sneuberg

AWF VIP
Local time
Today, 04:15
Joined
Oct 17, 2014
Messages
3,506
I believe this would be very difficult to implement at the table level. Access has data macros (kind of like table triggers) but I find them hard to work with. At the form level you could use the DCount function in some event to determine if the item is a duplicate.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
42,970
There is no "allow some duplicates but not others" option. At the table level, duplicates would need to be allowed and you would have to manage the duplication programatically via code.

I'm going to suggest rethinking the design. There may be a better design that will satisfy your requirements. Perhaps another table needs to be added.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Jan 23, 2006
Messages
15,361
I agree that a little more analysis and design would be helpful. Mock up ( pencil/paper) the things you can't barcode but want to inventory. Try some scenarios to see the details of the problem. You may find a pattern and a more general solution to your issue. Better to resolve it on paper than get stuck during physical database set up.

Good luck.
 

AccessBlaster

Registered User.
Local time
Today, 04:15
Joined
May 22, 2010
Messages
5,823
I’m setting up an inventory database. Some/most of the items will have barcodes but others do not, and it’s not practical to barcode them. Is there a way to generally not allow duplicates in a field but allow a small handful of things to be entered multiple times in the field with multiple records? Is this something I would need to do at the form level or the table level?


Sent from my iPhone using Tapatalk
Your absolutely right its not practical to tag certain item's. A camera or lens are difficult to tag. Our solution is a virtual tag. We collect as much unique information about an item as possible. Asset tag number is unique no dupes allowed, serial numbers are semi unique dupes are allowed. Purchase Order numbers can span multiple line items and can be revised so they are semi unique dupes allowed. We do index the serials and PO's but we keep the indexing to a minimal.

Below is a example of our physical tag.

 

Attachments

  • Tag.png
    Tag.png
    37.7 KB · Views: 244

Mark_

Longboard on the internet
Local time
Today, 04:15
Joined
Sep 12, 2017
Messages
2,111
As an option, can you leave the field "NULL"?

NULL is simply "No value" but if you need one specific value that says "We don't have a barcode" this could work. If so, you could then have a second field that does allow duplicates that holds the data you are looking for.

Think of the logic as

IF IsNull(UniqueField)
Use Other field
ELSE
Use UniqueField
End IF

If you make this into a public function then it becomes much easier to use. Simply pass UniqueField and other field when you want to get the value.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
42,970
A schema design change will solve the problem. The serial number needs to be held in a separate table. Your queries use a left join to the serial number table since not all items will have serial numbers. That means that when there is a serial number, it is unique for each item. Otherwise, the generic number exists on the product record
 

Users who are viewing this thread

Top Bottom