I must have failed I thought I covered it all

[BladeCanyon via AccessMonster.com]

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

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[BladeCanyon via AccessMonster.com]

Thanks!! I will try it.

BC

 

[BladeCanyon via AccessMonster.com]

Yes. this worked. thanks again

BC