Composite Primary Key or Unqiue Index Compose of Multiple Fields

[Eric]

Greetings,

I would like to ask for your wonderful help in determining what method I
should use to guarantee the uniqueness of the data being imported into an
Access 2002 database on Windows XP. The data file is downloaded as CSV text
file with 40 fields defined. The source provide states that PROG_NUM,
START_DT, ORDER_NUM, STUDENT_ID form a unique key for roster records. I
weeded out about 40 fields from the import file (created an import
specification) that were not needed for my desired output for my reports.

Which of these approaches would be appropriate to use:

1) Should I add an ID field as an autonumber in addition to the following
four fields to make a composite primary key?

If the correct answer is #1, should the index property for each field be set
to "Yes (No Duplicates)" before the composite key is created or does it
matter? Should anything else be done in this example? When I attempt to
import the CSV file into the table, I do not encounter any problems
whatsoever and subsequent imports do not produce any errors.

2) Should I add an ID field as an autonumber as a primary key and then
create a unique index on the four fields?

If the correct answer is #2, just create the ID field and set it to the
primary key. Should each of the four have their Index property set to "Yes
(No Duplicates)'? Then click on the Indexes button, which then shows the
following information which I have created:

Index Name Field Name Sort Order
Roster ProgNum Ascending
StartDate Ascending
OrderNum Ascending
StudentID Ascending

Index Properties: When I attempt to define this unique index, it only allows
me to assign the following values below to the ProgNum field and no other
fields.

Primary - No
Unique - Yes
Ignore Nulls - No

The first time the data is imported into the table, I do not encounter any
problems whatsoever. The problems occur when I attempt to import new data
into the table. It seems that Access is rejecting the import file due to the
other values in the ProgNum field.

I hope someone out there would be able to advise me in how to proceed. If I
haven't provided enough information for you to digest, please let me know
and I will do my best to get you this information. I believe that the first
approach seems appealing but I am somewhat worried about having too many
fields defined as a composite primary key, any truth to this?

Many TIA's,

Eric

 

[Eric]

Greetings,

Please forgive my lack of not knowing what I am doing in Access yet. My
first mistake was in stating the CSV file has only 40 fields. This should of
been 80 fields total with 40 of them being skipped. The second mistake was
in the second scenario; I reran this procedure again and noticed where I was
messing up with the import. I was using an import file that had only one set
of values, ex. 10777, for the ProgNum field. After I used the correct CSV
file that contained numerous values for the ProgNum field, the import
process proceeded without a glitch.

Sorry for making such a stupid mistake. So, now both procedures work, which
one is the preferrable method, if you do not mind me asking...

Thanks,

Eric