Unique Values

[Ian Mac]

All,

I have a table of data:

Book1
	A	B	C	D	E	F
1	Pid	DateOfRequest	TimeOfRequest	DateOfHoliday	StartTime	EndTime
2	6074114	04/02/2004	12:32	27/02/2004	17:00	22:00
3	6074114	04/02/2004	12:33	28/02/2004	17:00	22:00
4	6074114	04/02/2004	12:43	27/02/2004	17:00	22:00
5	6074114	05/02/2004	17:43	01/03/2004	17:00	22:00
6	6074114	05/02/2004	18:43	02/03/2004	17:00	22:00
7	6073456	05/02/2004	19:43	03/03/2004	17:00	22:00
8	6073456	05/02/2004	20:43	04/03/2004	17:00	22:00
9	6073456	05/02/2004	21:43	05/03/2004	17:00	22:00
10	6078564	05/02/2004	22:43	03/03/2004	17:00	22:00
11	6078564	05/02/2004	23:43	07/03/2004	17:00	22:00
12	6078564	05/02/2004	00:43	08/03/2004	17:00	22:00
13	6078564	05/02/2004	01:43	03/03/2004	17:00	22:00
Sheet1

This is an Access table in real life.

Can I prevent multiple entries based on certain fields only.
The yellow and pink highlights are duplicate entries because of Pid and DateOfHoliday

These are the only to fields I would like to compare.

Any suggestions?

(And they gave me a an Advanced Access Cert, I ask you?! )

kind regards

 

[dk]

Hi Ian,

Can you set the PID and Holiday Date as your primary key? If what you posted is your entire table then it seems as though that would make a good 'natural' primary key and would also solve your duplication problem.

 

[Ian Mac]

Hi Ian,

Can you set the PID and Holiday Date as your primary key? If what you posted is your entire table then it seems as though that would make a good 'natural' primary key and would also solve your duplication problem.

The Pid is the primary key of the tblEmployees
Where the Pid come from in a Combobox Query on this table tblHolidayReq

How Can I set it as a primary key in this table as the same person will ask for lots of holidays their Pid will be duplicated many times
Also the holiday date because each person can request the same day.

Unless I've got it wrong??

Regards

 

[dk]

Ian,

Are you a night owl or an early riser?

You can set a composite primary key so that the combination of PID and Holiday Date must be unique for each record. In table design view select BOTH the PID and the Holiday date and click the primary key button. You will be able to enter a PID multiple times and a holiday date multiple times but you could only enter a specific date once for each employee.

 

[bat17]

You can also set up multiple indexes as unique indexes.
In table design select >View>Indexes

The dialouge box that pops up allows you to set the indexes for the table. You can start a new one by adding a name under the index name column, then select the field names that you want to index together. at the bottom of the box you have the option to set the index as unique.

This has the advantage over a compound key because when you work in queries you have a single keyfield to join queries together with rather than remebering to connect all of the ones used to make the compound key.

Hope that makes sense

Peter