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?

On Wed, 30 Jun 2010 20:32:22 -0400, "LAS" <(E-Mail Removed)>
wrote:

>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/

On Wed, 30 Jun 2010 20:32:22 -0400, "LAS" <(E-Mail Removed)> wrote:

>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.
--

John W. Vinson [MVP]

On Thu, 01 Jul 2010 10:10:43 -0600, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>>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.

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/

Tony Toews <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> 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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> 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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Tony Toews" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Wed, 30 Jun 2010 20:32:22 -0400, "LAS" <(E-Mail Removed)>
> wrote:
>
> 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.

>
> 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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Wed, 30 Jun 2010 20:32:22 -0400, "LAS" <(E-Mail Removed)> wrote:
>
>>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.

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.
>
> 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.
> --
>
> John W. Vinson [MVP]