Access Unique Primary Key

  • Thread starter Thread starter Ronald Jones
  • Start date Start date
R

Ronald Jones

First some background on the software, we have created a
product that is both a stand alone and replicated version
of the database. We use the import/export features to
collect data from various sources to one database. The
issue is, we need to develop a primary key to filter out
duplicate records. We have identified that we could use
the field names "City" and "Date" as differeniating fields
to create a primary key. Can we create a new field that
consolidates "City" and "Date" into this field to purge
duplicate records? If so, how would that field be
programmed?

Is there another possible way to filter out duplicate
records using the parameters illustrated above?
 
We have identified that we could use
the field names "City" and "Date" as differeniating fields
to create a primary key.
Click to expand...

That does NOT sound very plausible to me. There are over 40 cities
named Springfield in the United States - can you be ABSOLUTELY CERTAIN
that you will NEVER have two records with the same city name and date?

This is called an "Intelligent Key" - not a compliment! Why not use an
Autonumber, and put code in the Form's BeforeUpdate event to check for
duplicates (and either allow them to be entered if there really is a
record for Springfield, MO and for Springfield, OR on the same day) or
allow the user to cancel?
Can we create a new field that
consolidates "City" and "Date" into this field to purge
duplicate records? If so, how would that field be
programmed?
Click to expand...

That would be neither necessary nor appropriate. If you are completely
confident that these two fields are a robust and valid Primary Key,
simply ctrl-mouseclick the two fields in table design view and click
the Key icon. A Primary Key can consist of up to TEN fields.
Is there another possible way to filter out duplicate
records using the parameters illustrated above?
Click to expand...

You can simply create a Totals query, grouping by these two fields,
and counting records:

SELECT [City], [Date], Count(*)
FROM yourtable
GROUP BY [City], [Date]
HAVING Count(*) > 1;

to find all the dups before you set the PK.
 
Back
Top