If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I must have failed I thought I covered it all
Hello all,
let me give you a small bit of history. I designed a MS Access 2003 Database that would allow tracking attendance and provide easy reporting. the information is imported via an excel linked table. I even provided a switchboard. I thought I was an IT superhero. ok, so I messed up, people can not follow directions. lol now I am getting duplicate records from typos of manual entries, AND wait for it... the data imported also has duplicates because of incorrect entries in the main program. /grrrr Here is the question: How do I use "no duplicates" when everything I need to have such as date (can be multiple employees on the same day), employeeID (can be a single employee multiple days), has to be able to be duplicate. I just need to isolate EmpID and Date together no duplicates.. the fields the are important are EmpID, ODate(occurence date), OHours(occurence hours), and OType(this is the occurence type field). I do have a primary autonumber field RecordID [indexed(no duplicates)]. everyone laugh... lol yes, a losing battle indeed. (and I must admit, I have received many praises for this, it does work well, just needs one more tweak) ID 10 T error protection. hehe any suggestions? thanks in advance BC -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
I must have failed I thought I covered it all
Setting up the tables correctly is always the most important factor, so you
chose the right group to ask in. :-) You have a table where an EmpID can occur many times, and an ODate can occur many times, but ODate + EmpID together must be unique? You can achieve that by setting up a unique index on the combination of the 2 fields. 1. Open the table in design view. 2. Open the Indexes box (toobar icon.) 3. In the first column, type a name for the index in the first blank row. In the second column, choose the first field of the index. It will look something like this: OdateEmpID ODate 4. In the lower pane of the index box, set Unique to Yes. 5. On the next row of the indexes box, leave the Index Name blank (first column), and in the 2nd column choose the other field. The lack of a name, indictes that this row is part of the same indexed named above (and hence gives you the *combination* as unique.) It will now look like this: OdateEmpID ODate EmpID The 2nd row doesn't have any properites (since it's part of the index named above.) 6. Save. Access will ask if you want to check to see if any existing data violates the new index. At this point you have the choice to ignore any bad data (so you can save the index), or to check the table (the index won't save, but you will know if there's bad data.) After saving these changes, Access will not allow a record into the table if it violates the index. Examples: - If you enter a duplicate in a new row, you won't be able to save it. - If you try to import data that has a duplicate, those rows won't import. - If you edit an existing record so that it becomes a duplicate, you can't save it. I'm not clear if you need the OType to be part of the unique index. If so, you just add a 3rd row in the Indexes dialog, again without an Index Name. BTW, the reason I suggested using ODate + EmpID rather than the other way around is efficiency. If you created a relation with Referential Integrity from EmpID to the primary key of your Employee table, Access creates a hidden index on that field. By indexing the ODate + EmpID, Access can use this combined index to sort the ODate without having to create another index on that field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BladeCanyon via AccessMonster.com" u49876@uwe wrote in message news:92993097e9aea@uwe... Hello all, let me give you a small bit of history. I designed a MS Access 2003 Database that would allow tracking attendance and provide easy reporting. the information is imported via an excel linked table. I even provided a switchboard. I thought I was an IT superhero. ok, so I messed up, people can not follow directions. lol now I am getting duplicate records from typos of manual entries, AND wait for it... the data imported also has duplicates because of incorrect entries in the main program. /grrrr Here is the question: How do I use "no duplicates" when everything I need to have such as date (can be multiple employees on the same day), employeeID (can be a single employee multiple days), has to be able to be duplicate. I just need to isolate EmpID and Date together no duplicates.. the fields the are important are EmpID, ODate(occurence date), OHours(occurence hours), and OType(this is the occurence type field). I do have a primary autonumber field RecordID [indexed(no duplicates)]. everyone laugh... lol yes, a losing battle indeed. (and I must admit, I have received many praises for this, it does work well, just needs one more tweak) ID 10 T error protection. hehe |
#3
|
|||
|
|||
I must have failed I thought I covered it all
Thanks!! I will try it.
BC -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200903/1 |
#4
|
|||
|
|||
I must have failed I thought I covered it all
Yes. this worked. thanks again
BC -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200903/1 |
Thread Tools | |
Display Modes | |
|
|