Autonumber in query

[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

[Emily Jones]

Re: Autonumber in query

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" <smileyBob@hotm ail.com> wrote in message
news:ht1pc0tkn1 6088jskh6am7lad 39fefbnod@4ax.c om...[color=blue]
> 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
>
>[/color]

[Bob Quintal]

Re: Autonumber in query

"Emily Jones" <emmersthejones @hotmail.com> wrote in
news:40cc8ebb$0 $58820$5a6aecb4 @news.aaisp.net .uk:
[color=blue]
> 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
>[/color]
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


[color=blue]
> "Bob" <smileyBob@hotm ail.com> wrote in message
> news:ht1pc0tkn1 6088jskh6am7lad 39fefbnod@4ax.c om...[color=green]
>> 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
>>
>>[/color]
>
>[/color]

[Bob]

Re: Autonumber in query

On Sun, 13 Jun 2004 17:43:08 GMT, Bob Quintal
<bquintal@gener ation.net> wrote:
[color=blue]
>"Emily Jones" <emmersthejones @hotmail.com> wrote in
>news:40cc8ebb$ 0$58820$5a6aecb 4@news.aaisp.ne t.uk:
>[color=green]
>> 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
>>[/color]
>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[/color]

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

[Bob Quintal]

Re: Autonumber in query

Bob <smileyBob@hotm ail.com> wrote in
news:5kepc09pep lnocg5lb1a7njl6 lj9ukj1g5@4ax.c om:
[color=blue]
> On Sun, 13 Jun 2004 17:43:08 GMT, Bob Quintal
> <bquintal@gener ation.net> wrote:
>[color=green]
>>"Emily Jones" <emmersthejones @hotmail.com> wrote in
>>news:40cc8ebb $0$58820$5a6aec b4@news.aaisp.n et.uk:
>>[color=darkred]
>>> 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
>>>[/color]
>>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[/color]
>
> 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
>[/color]
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

[color=blue]
> Regards Smiley Bob
>[/color]

[Bob]

Re: Autonumber in query

On Mon, 14 Jun 2004 21:52:28 GMT, Bob Quintal
<bquintal@gener ation.net> wrote:

[color=blue]
>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[/color]

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

[color=blue]
>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
>[/color]

[Bob Quintal]

Re: Autonumber in query

Bob <smileyBob@hotm ail.com> wrote in
news:f29sc0dfdi nuogm0d5pqnsnjt qhi9mnpd1@4ax.c om:
[color=blue]
> On Mon, 14 Jun 2004 21:52:28 GMT, Bob Quintal
> <bquintal@gener ation.net> wrote:
>
>[color=green]
>>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[/color]
>
> 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
>
>[color=green]
>>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[/color][/color]

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(IDy ear,"0000") & format(IDmonth, "00")& format
(IDday,"00") ...etc.)


Bob Quintal

[Bob]

Re: Autonumber in query

On Mon, 14 Jun 2004 23:28:36 GMT, Bob Quintal
<bquintal@gener ation.net> wrote:
[color=blue]
>Bob <smileyBob@hotm ail.com> wrote in
>news:f29sc0dfd inuogm0d5pqnsnj tqhi9mnpd1@4ax. com:
>[color=green]
>> On Mon, 14 Jun 2004 21:52:28 GMT, Bob Quintal
>> <bquintal@gener ation.net> wrote:[/color][/color]

[color=blue]
>Just use the time date-itself as the key, not some expression you
>have created.
>
>cdbl(Format(No w(), "yyyymmddhhnnss ")) works perfectly
>
>or if your linked table gives each portion in a separate field,
>
>cdbl(format(ID year,"0000") & format(IDmonth, "00")& format
>(IDday,"00") ...etc.)
>
>
>Bob Quintal[/color]

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