By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,521 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,521 IT Pros & Developers. It's quick & easy.

Autonumber in query

P: n/a
Bob
Hi Everybody

I have a query that is based on table that I need to have some sort of
Unique ID # for. I can't have a unique ID in the table, but the table
DOES have a date field and I'm working with that.

At the moment it works by creating fields based on the date field that
multiplies the Day x hour x Minute x second

ID: ([IDDay])*([IDTimeHour])*([IDTimeMinute])*([IDTimeSecond])

But this is proving to be unsatisfactory and a bit unreliable.

Does anyone have any better ideas

Regards to All Smiley Bob
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Why can't you have a unique ID in the table? What's the Primary Key of the
table? If it hasn't got one, why, not? There may be a good reason, but we'd
like to hear it. What information does the table store?

Emily

"Bob" <sm*******@hotmail.com> wrote in message
news:ht********************************@4ax.com...
Hi Everybody

I have a query that is based on table that I need to have some sort of
Unique ID # for. I can't have a unique ID in the table, but the table
DOES have a date field and I'm working with that.

At the moment it works by creating fields based on the date field that
multiplies the Day x hour x Minute x second

ID: ([IDDay])*([IDTimeHour])*([IDTimeMinute])*([IDTimeSecond])

But this is proving to be unsatisfactory and a bit unreliable.

Does anyone have any better ideas

Regards to All Smiley Bob

Nov 13 '05 #2

P: n/a
"Emily Jones" <em************@hotmail.com> wrote in
news:40***********************@news.aaisp.net.uk:
Why can't you have a unique ID in the table? What's the
Primary Key of the table? If it hasn't got one, why, not?
There may be a good reason, but we'd like to hear it. What
information does the table store?

Emily
Besides, if the date being used is unique, it can be used
directly as the primary key, no need to jump through all the
hoops the original poster is going through.

Bob Quintal
"Bob" <sm*******@hotmail.com> wrote in message
news:ht********************************@4ax.com...
Hi Everybody

I have a query that is based on table that I need to have
some sort of Unique ID # for. I can't have a unique ID in the
table, but the table DOES have a date field and I'm working
with that.

At the moment it works by creating fields based on the date
field that multiplies the Day x hour x Minute x second

ID:
([IDDay])*([IDTimeHour])*([IDTimeMinute])*([IDTimeSecond])

But this is proving to be unsatisfactory and a bit
unreliable.

Does anyone have any better ideas

Regards to All Smiley Bob



Nov 13 '05 #3

P: n/a
Bob
On Sun, 13 Jun 2004 17:43:08 GMT, Bob Quintal
<bq******@generation.net> wrote:
"Emily Jones" <em************@hotmail.com> wrote in
news:40***********************@news.aaisp.net.u k:
Why can't you have a unique ID in the table? What's the
Primary Key of the table? If it hasn't got one, why, not?
There may be a good reason, but we'd like to hear it. What
information does the table store?

Emily

Besides, if the date being used is unique, it can be used
directly as the primary key, no need to jump through all the
hoops the original poster is going through.

Bob Quintal


The table being used is the Ms Outlook table connection and you are
stuck with the fields that Ms Outlook gives you.

If you have ever tried to use a date and time field as an ID you will
know what I mean

Regards Smiley Bob
Nov 13 '05 #4

P: n/a
Bob <sm*******@hotmail.com> wrote in
news:5k********************************@4ax.com:
On Sun, 13 Jun 2004 17:43:08 GMT, Bob Quintal
<bq******@generation.net> wrote:
"Emily Jones" <em************@hotmail.com> wrote in
news:40***********************@news.aaisp.net.uk :
Why can't you have a unique ID in the table? What's the
Primary Key of the table? If it hasn't got one, why, not?
There may be a good reason, but we'd like to hear it. What
information does the table store?

Emily
Besides, if the date being used is unique, it can be used
directly as the primary key, no need to jump through all the
hoops the original poster is going through.

Bob Quintal


The table being used is the Ms Outlook table connection and
you are stuck with the fields that Ms Outlook gives you.

If you have ever tried to use a date and time field as an ID
you will know what I mean

What? a date-time field is in reality a double-precision number,
nothing more. It works perfectly as a primary key, as long as you
avoid duplicates. Since the resolution is a few milli-seconds.
it's hardly ever a concern. It's certainly not any concern with
file-creation dates, because of the latency in disk writes..

What problems do you imagine happen using a date-time field as a
primary key?

Bob Quintal

Regards Smiley Bob


Nov 13 '05 #5

P: n/a
Bob
On Mon, 14 Jun 2004 21:52:28 GMT, Bob Quintal
<bq******@generation.net> wrote:

What? a date-time field is in reality a double-precision number,
nothing more. It works perfectly as a primary key, as long as you
Hmm

It becomes unreliable because I am using the expression
ID: ([IDDay])*([IDTimeHour])*([IDTimeMinute])*([IDTimeSecond])
as a Long integer. 10 Digits

If the time happens to be 01: 01:01 and the day is 01 or any low
figure it does not reliably produce a 10 digit usable ID. Sometimes 6
or 7 digits

However, I'm sure that you are right in saying the best way of
creating a unique ID is with time and date combinations, its just that
I need consistancy with the amount of digits I'm working with. I don't
mind 6,8, or 10 digits as long as all are the same amount of digits

Access keeps saying there are duplicate #'s in the table when in fact
there are none.

Thanks for your input
regards Smiley Bob

avoid duplicates. Since the resolution is a few milli-seconds.
it's hardly ever a concern. It's certainly not any concern with
file-creation dates, because of the latency in disk writes..

What problems do you imagine happen using a date-time field as a
primary key?

Bob Quintal


Nov 13 '05 #6

P: n/a
Bob <sm*******@hotmail.com> wrote in
news:f2********************************@4ax.com:
On Mon, 14 Jun 2004 21:52:28 GMT, Bob Quintal
<bq******@generation.net> wrote:

What? a date-time field is in reality a double-precision
number, nothing more. It works perfectly as a primary key, as
long as you


Hmm

It becomes unreliable because I am using the expression
ID: ([IDDay])*([IDTimeHour])*([IDTimeMinute])*([IDTimeSecond])
as a Long integer. 10 Digits

If the time happens to be 01: 01:01 and the day is 01 or any
low figure it does not reliably produce a 10 digit usable ID.
Sometimes 6 or 7 digits

However, I'm sure that you are right in saying the best way of
creating a unique ID is with time and date combinations, its
just that I need consistancy with the amount of digits I'm
working with. I don't mind 6,8, or 10 digits as long as all
are the same amount of digits

Access keeps saying there are duplicate #'s in the table when
in fact there are none.

Thanks for your input
regards Smiley Bob

avoid duplicates. Since the resolution is a few milli-seconds.
it's hardly ever a concern. It's certainly not any concern
with file-creation dates, because of the latency in disk
writes..

What problems do you imagine happen using a date-time field as
a primary key?

Bob Quintal


Just use the time date-itself as the key, not some expression you
have created.

cdbl(Format(Now(), "yyyymmddhhnnss")) works perfectly

or if your linked table gives each portion in a separate field,

cdbl(format(IDyear,"0000") & format(IDmonth,"00")& format
(IDday,"00") ...etc.)
Bob Quintal
Nov 13 '05 #7

P: n/a
Bob
On Mon, 14 Jun 2004 23:28:36 GMT, Bob Quintal
<bq******@generation.net> wrote:
Bob <sm*******@hotmail.com> wrote in
news:f2********************************@4ax.com :
On Mon, 14 Jun 2004 21:52:28 GMT, Bob Quintal
<bq******@generation.net> wrote:

Just use the time date-itself as the key, not some expression you
have created.

cdbl(Format(Now(), "yyyymmddhhnnss")) works perfectly

or if your linked table gives each portion in a separate field,

cdbl(format(IDyear,"0000") & format(IDmonth,"00")& format
(IDday,"00") ...etc.)
Bob Quintal


Ah ha!

The light went on it works great.

It can be relied on now to produce numbers with a unique ID in a
uniform amount of digits.

Thanks a lot

Smiley Bob

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.