Checking for Duplication

[Shirley]

I have a table called Key Log Out Table that has these fields name:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TransID<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SiteNumber<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;KeyNumber<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;EmployeeID<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IssuedDate<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ReturnedDate<br><br>This table is linked to Site Table, Key Table and Employee Table.&nbsp;&nbsp;On this table there is a site number and a key number assigned to each<br>key, I would like to let the user know if that particular key have already been assigned to someone else because it doesn't have a returned date.&nbsp;&nbsp;But if there is a return date then the user may issue that key out again.&nbsp;&nbsp;The problem is that I'm not a very good VB or SQL coding person, but I'm good at developing database.&nbsp;&nbsp;I can not make those three fields the primary key because the return date is sometimes null.&nbsp;&nbsp;I have tried to make the other two fields primary, but then it will not allow me to ever use the site number and key number again.&nbsp;&nbsp;I've even tried to set up a unique index, but I did not know how the coding for that would go.&nbsp;&nbsp;So if anyone has any coding that would enable to to do this, please let me know.

 

[carp]

If you are using Oracle, you can set up a composite unique constraint on the site and key columns.&nbsp;&nbsp;If a non-unique site/key combination is entered, an exception will be raised.

 

[Invicta]

Shirley, a select statement may be the answer you're looking for if you simply want to know if the key is booked out or not.<br><br>SELECT COUNT(*) FROM KeyLogOutTable WHERE KeyNumber = 123 AND SiteNumber = 56 AND ReturnedDate IS NULL<br><br>This statement will return 1 if the key is out, 0 otherwise.&nbsp;&nbsp;If you get a number greater than 1 you have a problem because the key is out twice or more!<br><br>Good luck.