Record Validation?? for newbie

[CDChilds]

I have designed a database in Access 2003 which we will use for scheduling.
I am looking for the best solution to identify duplicate fields within the
same record. The values in the work location fields will come from an
employee table using lookup. I just want to have some type of msgbox showing
the person scheduling that they may have used the same employee in two
different work locations. There are about 45 different work locations within
our company. So there are 45 fields that need to be compared for duplicates.
Is there a simple way to do this? Should I use a macro or record validation
rules? Any advice would be greatly appreciated

 

[Dennis]

How are the available locations supplied to the user? If it's by combo-box,
you could write the query such taht only unassigned locations populate the
drop-down. that way, it would be impossible to double-assign someone.

 

[CDChilds]

actually the locations are the field labels for the schedule table. I used
lookup for each fields data type to lookup the names from an employee table

 

[Duane Hookom]

Your statement 'So there are 45 fields that need to be compared for
duplicates' suggests an un-normalized table structure. In the words of MVP
Jeff Boyce, you have "committed spreadsheet". If your tables were properly
normalized, you might have a much easier time finding duplicates and
performing other tasks.

 

[CDChilds]

So I should break the schedule up into smaller tables? Its probably a little
too late for that, oooppss. I will remember that next time though

 

[KARL DEWEY]

So I should break the schedule up into smaller tables?
Break your table into multiple records instead of multiple fields.
Use a union query like this --
SELECT Employee, WorkDate, Location1 AS Location
FROM YourTable
UNION ALL SELECT Employee, WorkDate, Location2 AS Location
FROM YourTable

UNION ALL SELECT Employee, WorkDate, Location3 AS Location
FROM YourTable
--- through all 45 location fields ---
UNION ALL SELECT Employee, WorkDate, Location45 AS Location
FROM YourTable;

 

[Piet Linden]

So I should break the schedule up into smaller tables?  Its probably a little
too late for that, oooppss.  I will remember that next time though

If you choose not to normalize, querying could get ugly fast. What if
you have to find where someone is working?

 

[Duane Hookom]

Normalizing doesn't mean to break a table into smaller tables. Quite the
opposite. For instance, if you have a table with fields like:
tblSchedule
================
WorkPosition
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
With names of worker entered into the appropriate weekday field, you would
change the structure to something like:
tblSchedule
================
WorkPosition
WorkerName
DayOfWork

Duane Hookom
Microsoft Access MVP