How do I make a multi-field primary key?

[LAS]

I have a table with a date, time and ID field. I want them all,
concatenated, to constitute a primary key. Can I do that?

 

[Tony Toews]

I have a table with a date, time and ID field. I want them all,
concatenated, to constitute a primary key. Can I do that?

In talbe design view select both fields and click on the primary key
button.

However you do not want a date/time field to be a primary or even
foreign key. The problem is that under the covers a date field is
actually a Double field type. And the portion to the right of the
decimal point might cause troubles due to how floating point numbers
are recorded in computers.

Now a datetime field can be part of an index. Not a problem there.

Finally you state you have an ID field. By this do you mean an
autonumber field? If so why not just use that as the primary key that
child tables reference?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[John W. Vinson]

I have a table with a date, time and ID field. I want them all,
concatenated, to constitute a primary key. Can I do that?

Don't.

For one thing, a Date/Time field works best when you have the date and time
stored in the same field. For a second, a Date/Time field (or two of them) are
risky as Primary Keys, as Tony says. For a third, creating a concatenated
redundant field containing data from three other fields is simply *not* good
design.

If you have a good reason to create a multifield primary key (I'm doubtful
that you do in this case, though I'll be glad to hear your rationale) you do
not need a new field; a primary key can consist of up to ten fields. You would
ctrl-click each field comprising the key in table design view and click the
Key icon to make them a joint multifield key.

 

[Tony Toews]

Don't.

For one thing, a Date/Time field works best when you have the date and time
stored in the same field.

Ahh, thanks. I missed the comma between date and time.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[David W. Fenton]

However you do not want a date/time field to be a primary or even
foreign key. The problem is that under the covers a date field is
actually a Double field type. And the portion to the right of the
decimal point might cause troubles due to how floating point
numbers are recorded in computers.

Why would this be a problem? Surely your objection would apply
equally to any field using double as its data type (since that's
what a date/time field is behind the scenes).

 

[David W. Fenton]

For one thing, a Date/Time field works best when you have the date
and time stored in the same field.

Works best for what? I generally only very seldom store anything but
date fields with no time part, but it's often quite helpful to have
the time part in a separate field (it makes query criteria much
simpler, for instance). I think whether or not it's a good idea
depends entirely on the particular situation in which it is being
used.

For a second, a Date/Time field (or two of them) are
risky as Primary Keys, as Tony says.

I don't get this. Why? Surely if this is true, and double has this
problem, and if that's so, then perhaps single does, too?

I just don't see the objection.

For a third, creating a concatenated
redundant field containing data from three other fields is simply
*not* good design.

This I wholeheartedly agree with.

 

[LAS]

Finally you state you have an ID field. By this do you mean an
autonumber field? If so why not just use that as the primary key that
child tables reference?

The ID is a student id, not an autonumber.

 

[LAS]

Don't.

For one thing, a Date/Time field works best when you have the date and
time
stored in the same field.

How do you create input forms that allow the user to use familiar date and
time notation in separate fields?

For a second, a Date/Time field (or two of them) are

risky as Primary Keys, as Tony says. For a third, creating a concatenated
redundant field containing data from three other fields is simply *not*
good
design.

I didn't mean that I wanted to concatenate them into a fourth field. Just
make them all primary keys. The ctrl-click suggested above was what I
needed.