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

On Mon, 8 Sep 2008 12:58:00 -0700, CDChilds
<CDChilds@discussions.microsoft.com> wrote:

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

Well... don't use table datasheet view for ANYTHING; use a form instead. And
use Lookup Fields with great caution: see
http://www.mvps.org/access/lookupfields.htm
for a critique of what many consider a misfeature.

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

That's an incorrect table design. What will you do when the company gets
another location? Redesign your table, all your queries, all your forms, all
your reports? OUCH! "Fields are expensive, records are cheap"; you need
another table to do this. You would need a 45 row table of Locations, and
store the LocationID in the schedule table to indicate where this employee is
located for this schedule event.

> Is there a simple way to do this? Should I use a macro or record validation
>rules? Any advice would be greatly appreciated

Post a description of your current table structures, it's a bit hard to tell
just what you're doing (though if you have 45 location fields you're
definitely doing it wrong!)

--

John W. Vinson [MVP]

Stop right where you are. You database design is going in the wrong direction.
You should not have repeating values in a record. That is, you say you have
45 work locations thus 45 fields. That is (sorry, but) a very bad idea.
Your table design should in include only one work location, who is assigned
to that location, and whatever else you need to identify it like date and
time range.
Once you have the correct design in place, you can set validation properties
in your table so duplicates are not allowed.
--
Dave Hargis, Microsoft Access MVP


"CDChilds" wrote:

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