Not allowing 3 field entries to be the same

C

chrisdudeperson

New member
Local time
Today, 21:11
Joined
Jan 25, 2009
Messages
1
Hey guys,

I'm pretty new to access and i need some help!

I have a table for bookings.

I need to make sure that the Court, Date, and Time fields are only allowed one combination of the three once.

E.g.

Record 1

Court - 1
Date - 1/3/2009
Time - 6:00


Record 2

Court - 2
Date - 6/2/2009
Time - 12:00


That can be allowed but this can't:

Court - 1
Date - 1/3/2009
Time - 6:00


Record 2

Court - 1
Date - 1/3/2009
Time - 6:00


Can anyone help?

Thanks Chris
 
The simplest way would be to make the 3 fields a compound key. In table design view, highlight all 3 and click the key icon.
 
pbaldy said:
The simplest way would be to make the 3 fields a compound key. In table design view, highlight all 3 and click the key icon.
Click to expand...

Actually, I'm going to disagree with you on this Paul. As it is very difficult to use a composite key as a foreign key in another table. Perhaps an autonumber is still the best PRIMARY key, but use a multi-field index to limit the duplicates. I have a new "Quick Tutorial" on how to create a multi-field index in conjunction with an autonumber:
http://www.btabdevelopment.com/main...createamultifieldindex/tabid/140/Default.aspx
 
  • Like
Reactions: jal
Bob,

Unless I am doing it the wrong way the problem I get with that system is it makes each of the fields in the list require a unique entry as opposed to a combination of the fields.

It stops a combination but it also stops what are not equal combinations if the entries in one of the fields are duplicated.Thus it won't allow

1 4 9
2 4 8
 
Mike375 said:
Bob,

Unless I am doing it the wrong way the problem I get with that system is it makes each of the fields in the list require a unique entry as opposed to a combination of the fields.

It stops a combination but it also stops what are not equal combinations if the entries in one of the fields are duplicated.Thus it won't allow

1 4 9
2 4 8
Click to expand...
You're doing it wrong then. Upload a screenshot of your Index dialog.
 
boblarson said:
You're doing it wrong then. Upload a screenshot of your Index dialog.
Click to expand...

Mike:

If you look at the screenshots on my website, I show exactly how it works and I posted actual screenshots as to how it really works. So, it would be good to see exactly what you're doing.
 
Bob,

Attached.

I just removed some fields to stop confusing myself and left only the index fields.
 

Attachments

Okay, Mike - you gotta follow the directions the way they say. You didn't. You added a name for the other field. See - this is how it SHOULD look:

mike375index.png


No Index name for f as it goes along with c
 
It is working now Bob.

That is the first time I have seen that.

Although the joined fields would still be needed for such things as opening a related form etc.??
 
boblarson said:
no, just the auto number primary key which should be the foreign key.
Click to expand...

Related...but not related by primary key.

eg.

LawFirm........Phone and search/remove dups etc. on LawFirmPhone

In other words the indexed system you illustrated would be for data entry whereas joined fields are for search etc
 
The indexes prevent someone from entering the exact same data over multiple fields. The autonumber is the primary key which is used during searches and relating main form/sub forms.
 
boblarson said:
The autonumber is the primary key which is used during searches and relating main form/sub forms.
Click to expand...

Only for some types of search or subform.

When a telemarketer comes to Bill Smith's record he wants to see what other people are at the same law firm and with the same phone number.
 
Mike375 said:
Only for some types of search or subform.

When a telemarketer comes to Bill Smith's record he wants to see what other people are at the same law firm and with the same phone number.
Click to expand...

I don't understand your point. The point I was making is that if you use an autonumber for primary key, you can let the system do its thing which is what it does best (behind the scenes) and nobody has to care. But, the drawback to using an autonumber, you end up with a possibility of duplicate entries. So you can set an index on a single field. But it doesn't help you if you have more than one field that you need to have checked to keep duplicates from happening. So, you set up a multi-field index (instead of using a composite key). Then you do not have to include all fields in your other tables, but use the autonumber as a foreign key just as if you didn't need a composite key.

Does that help to understand it better?
 
Bob,

We are on different pages.

I am using composite fields to find matching records. Of course I could also do an And but I choose composite fields. One reason I use composite fields is because they have been used initially to extract a single record (append to table) for a huge list of names and then later they pick up when a duplicte appears because the someone has moved to another law firm and now there are two active people in the maintable at the same law firm. Ditto for other category of prospects.

But if I used the index system it would prevent the telemarketer from entering the new details he was given by the secretary.
 
The way I see it, there are only two direct ways to enforce this 3-field uniqueness issue.

1. Compound index with NoDups. This absolutely stops you but then requires you to catch a trap using OnError code (and look for the specific error code).

2. In the data entry form (you ARE using one, of course?), a "BeforeUpdate" type of check for DCount() of records with that set of three values. And if the count is not zero, disallow the update. This means you don't actually need the index to be NoDups.

On the other hand, if you have a populated table with the NoDups index, the presence of that index with three fields doesn't stop you from searching on combinations of two of those fields. It just might not use the 3-field index for that purpose, so your query efficiency might be less than you expected. For small tables, this is chump-change anyway because you would never see the hesitation while the query runs.
 
Doc_Man

I think in the real world this is simple (but I could be missing something:D)

Bobs method allows for a a combination of fields to be a "restrictor" and from within the table itself. Joining fields initially only exists as the result of a formula and will place no restirction on data entry.

But Bob's method is not suitable for when you only want joined fields to find records with that same combination of data and for the simple reason that with Bob's method there can't be other records with the same combination of data.:)
 
You must log in or register to reply here.

Similar threads

A
Tab Index of fields NOT reacting in Order
Replies
5
Views
244
The_Doc_Man
The_Doc_Man
R
Unique values
Replies
15
Views
571
Pat Hartman
P
O
Adding days to Now() but with specific times
2
Replies
22
Views
455
Gasman
Gasman
N
Need help with data filtering form
Replies
4
Views
727
nax009
N
K
Solved Report Group by Day with a Start time of 4:00:00am, End time 3:59:59AM the next Day
Replies
5
Views
690
kjbrack
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom