Creating Surrogate Keys in Access

Question

I have a project that I am working on where we have to create a registration system for a university in Access. One of the tables requires a surrogate key for one of the fields, and I am not sure how I can create a surrogate key.

The column instructions look like this:

Field Name Data Type Field Size Caption Format Req'd Index Unique Key Ref. Table Other

FacultyID Text 4 blank blank Yes Yes Primary blank Input Mask '0000;1;_'

FirstName Text 32 blank blank Yes No Surrogate* blank blank

So the first field, FacultyID is straightforward and simple to make. But the FirstName field requires a surrogate key that I do not know how to make.

It also has the asterisk next to it, and at the bottom of the page the asterisk reads:

*Index [LastName], [FirstName]: Duplicates Allowed (surrogate key)

The LastName field is listed later but I didn't show it in my question. What confuses me with this, however, is that in the column instructions, it says that the Index field should be No for FirstName, but this note at the bottom of the page makes it seem like it should be Yes (Duplicates OK) and I do not know what to put.

So can anyone help me figure out how to make the FirstName a Surrogate Key, and also what I should be putting in the Index field?

Is this a class assignment? What 'page' are you referencing? Is FacultyID an autonumber field? Is this the primary key field? This is the surrogate key. Google the topic. https://www.mssqltips.com/sqlservertip/5431/surrogate-key-vs-natural-key-differences-and-when-to-use-in-sql-server/. Since multiple people can have same names, must allow duplicates in the name fields. — June7, Apr 21 '19 at 21:36
@June7 Yes this is a class assignment. Sorry, the page I am referring to is a page of instructions the professor provided. `FacultyID` is the primary key but the data type is `Short Text`. It should probably be a `Number` Type but the teacher specified that it should be `Text` — brent_mb, Apr 21 '19 at 22:03
Then you need to get clarification from instructor because I certainly can't explain that. Did you read the link? — June7, Apr 21 '19 at 22:34

score 0 · Answer 1 · answered Apr 22 '19 at 01:44

You'd need to post more info (like, is there an employees table?), but it looks like the instructions want you to use an employee's last and first name's as a composite key vs. surrogate. When designing a table, you can hold SHIFT + the fields you want to include in your key:

Then press the Primary Key button in the Tools section at the top:

And you'll end up with a composite primary key:

I think the wording on your requirements sheet is a little wonky. I think the only way to get a LastName, FirstName column is via some sort of Data Macro or through an append query.

Also, even if you don't use it for anything, I would suggest ALWAYS having an AutoNumber ID field. I've had database integrity issues when not having a key on each table.

But, without seeing the full assignment sheet, I would be tough to dial-in what you needed specifically.

Creating Surrogate Keys in Access

1 Answers1