Help with duplicated Entries.

[dalaniz85]

Greetings...
I'm looking for help; is there a way to allow "certain" duplicate entries in an Access table and still be able to restrict others?

I have a entry access database for tracking visitors. On the access table itself, there's a column for a visitor badge numbers. However, since that column is indexed and won't allow duplicates. How do I allow exceptions for certain badge numbers/names, that need to be duplicated?

And yes, this table is fed from a form; which all information then creates a report "our entry access list". Our visitor badges have a unique 4-digit number. Which the user would enter in the form. If the same number is entered more than once, then yes, that index control prevents the user from entering a badge number that's already assigned to another visitor.

However, if a visitor doesn't have an assigned badge with a unique number, they're issued a TEMP. I'd like to be able to use the word "TEMP" for more than one record, and still restrict entering a duplicate badge number already assigned .

[NeoPa]

So, you want the design of the table to restrict some duplicates but not others? I hope it won't be a surprise to you to know that table design doesn't allow for that. Unique or not are the options.

You could cheat and assign a negative number for TEMP badges and then ensure your badge-printing report replaces any negative number with the string "TEMP". You could do all sorts of clever things, but don't expect a database to restrict values in one range to being unique while allowing others to be duplicated.

[ADezii]

You may be able to accomplish this by creating a Before Change Data Macro in the Table that contains the Badge Number Field. The pseudo-logic would be if the status of the individual = 'TEMP', allow the Duplication, otherwise do not allow. Just a few points to mention:

1. The Duplication Logic for the Before Change Data Macro would propagate to the Form whose Record Source is the Table containing the Data Macro.
2. The minimal Version for using Data Macros is 2010.
3. You would have to allow Duplicates on the [Badge] Field in your Table, the Macro would control whether or not a Badge can be duplicated.

P.S. - I am at work right now, and cannot test this Theory since I only have Access 2007 here.

[NeoPa]

Are you sure you want to complicate this further my friend? Remember the KISS Principle ;-)

[ADezii]

Not al all, NeoPa, but weren`t you the one who said `you could do all sorts of clever things,` Simply wanted to imply that it may not be the end of the road for the OP. Please feel free to Delete the Post, I always trust your judgement in these matters.

By KISS do you mean Keep Implying a Secondary Solution? (LOL).

[NeoPa]

Clever things are good if they get you a better solution. My impression of this is extra complication for a less beneficial result.

Start with a basic concept of uniqueness. Allow the database to help you in that respect. From there you can use that managed data to provide a report that matches your requirements.

NB. No. I won't be deleting the post of a respected friend - or indeed anyone else either - simply because I don't agree with where they're going.

And, you make a good point that there are more options available if you think outside the box.

ADezii:
By KISS do you mean Keep Implying a Secondary Solution? (LOL).

NO! Of course not. BUT - it did make me laugh :-D