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 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.

 

[edrest]

Hi Shirley,<br><br>What kind of database are you storing your data in? Access perhaps?<br><br>Is the key out table simply a log table to keep track of who signed keys out when they were signed out and when they were returned?<br><br>Am I correct in thinking that there is a one to many relationship between the key table and key out table?<br><br>If the key out table is just a log table then:<br><br>Perhaps you might consider altering your key table to inculde a logical field like key_out or whatever name you wish. You could then look at that information and tell if a key is out or not. You would need to create a routine to check a key out and in. The out rountine would create a record in the key out log file and mark the key as out in the key table. The in routine would issue a return date in the log file and mark the key_out field as false thus alerting you that the key is available.<br><br>If it is more that a log file... let me know and I'll see about helping with the codeing you need.<br><br>Hope this is helpful!<br><br>

 

[Shirley]

Yes this is more like a log file, for each key that is in the key table the key log only keep track of that key.&nbsp;&nbsp;If you have an idea please let me know.

 

[MikeLacey]

When you create your record set on the KeyLogOut you could have an SQL that looked something like:<br><FONT FACE=monospace><b><br>lsSql = _<br>&nbsp;&nbsp;&nbsp;&nbsp;&quot;Select *&quot; & vbCrLf _<br>&nbsp;&nbsp;&nbsp;&nbsp;&quot; From KeyLogOut&quot; & vbCrLf _<br>&nbsp;&nbsp;&nbsp;&nbsp;&quot; Where SiteNumber = &quot; & <font color=red>liSitenumber</font> & vbCrLf _<br>&nbsp;&nbsp;&nbsp;&nbsp;&quot;&nbsp;&nbsp;And Keynumber = &quot; & <font color=red>liKeynumber</font> & vbCrLf _<br>&nbsp;&nbsp;&nbsp;&nbsp;&quot;&nbsp;&nbsp;And IssuedDate Is Not Null&quot; & vbCrLf _<br>&nbsp;&nbsp;&nbsp;&nbsp;&quot;&nbsp;&nbsp;And ReturnedDate Is Null&quot; & vbCrLf<br></font></b><br><br>This would identify keys that have been checked out but not yet returned. The variables <FONT FACE=monospace><b>liSitenumber</font></b> and <FONT FACE=monospace><b>liKeynumber</font></b> (<font color=red>red</font> in the example above) would have to contain the Site Number and the Key Number respectively. I've assumed they're both numeric. <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>