Need to add a PK after running Make Table

[khughes46]

I have data I import into Access then run a Make Table query from a Crosstab Query. All that works fine, but now I want to add a PK to an existing field (text) that will remain unique. Is this programmatically possible, and if so how?

[JoeM]

I have had situations like this, and go about it a little differently.

Instead of using a "Make Table" query, I set up the shell of an empty table, with all the fields and properties (and key fields) that I want. I then use an Append Query to write the records to it (instead of using a Make Table query where you don't have control over all those other things).

Is that a possibility for you?

[khughes46]

Unfortunately no. The data I import has duplicate data in the fields I need to use for a PK, hence the Crosstab Query.

[orange]

One of the things I have done, somewhat similar to joeM,

Create a make table query with all of the fields you need;
Use a where clause on the MakeTable query along the line of Where 1=2 (no records will qualify)
But it builds the table; now alter the table and create the PK;

Now, use the append query; your duplicates will not be loaded; they will fail on key violation.

Good luck.

[khughes46]

But then I lose the count. Each record is an individual Unexcused Absence.

[orange]

Maybe you could restate the problem with an example.

This is confusing:

The data I import has duplicate data in the fields I need to use for a PK

A PK is unique so duplicates won't be included in a PK.

You could add a new field, make it autonumber and PK.
Then you could still count the duplicate/replicates in your other field(s).
But I'm not clear on your requirement.

[khughes46]

The data has a student ID, the date of each absence in each record. So a student with more than 1 absence would have more than one record. If I do this in Excel using a Pivot table I get one record with the count of all the absences the student has for the reporting period. Example: Student ID = 999999 Absence 1 Date 3/1/2016, 999999 1 3/2/2016, 999999 1 3/3/2016; the Pivot table would have 999999 3. If I do this in Access as a Crosstab Query I get the desired result, but I need to make the student ID a PK.

[orange]

As I see your business rule.

1 Student can have 0,1 or Many Absences

tblStudent--->tblStudentAbsence

[khughes46]

That is correct: See a sanitized sampling attached.

[orange]

Great, but what does your Access attempt/effort look like?
Tables?
Relationships?

[khughes46]

From this table I create a Crosstab Query making a table that has the LocalID, the count of all absences by grade and campus (there are two more columns "Grade" and "Campus"; there 6 campuses, but the LocalID is still unique. It is this table I want to programmatically add a PK (LocalID).

[orange]

Please post a copy of the Access database.

[khughes46]

It is the Absences table where I want to add a PK (LocalID) programmatically.