Looking Alternative to fldDateTime PrimaryKey

[David]

Re: Access

I have a number of tables where the PrimaryKey is DateTime.

I just noticed that in one of the tables contains multiple records with the
same DateTime. Most likely this is because Access only goes out to
seconds and the double value that represents DateTime may go out to more
precision.

Looking for recommendations on how best to handle both entry and seeking
records?

One thought was to make PrimaryKey AutoNumber and the store DateTime as Text
in a yyyymmddhhmmss format and make this unique -- or I guess it could even
be the PrimaryKey since a Text field.
This would slow things a bit as I would have to convert the double back and
forth.

Suggestions / Comments / Recommendations?

Thanks
David

 

[John W. Vinson]

Re: Access

I have a number of tables where the PrimaryKey is DateTime.

I just noticed that in one of the tables contains multiple records with the
same DateTime. Most likely this is because Access only goes out to
seconds and the double value that represents DateTime may go out to more
precision.

Looking for recommendations on how best to handle both entry and seeking
records?

One thought was to make PrimaryKey AutoNumber and the store DateTime as Text
in a yyyymmddhhmmss format and make this unique -- or I guess it could even
be the PrimaryKey since a Text field.
This would slow things a bit as I would have to convert the double back and
forth.

Suggestions / Comments / Recommendations?

Thanks
David

I'd consider the date... data. Why not use an autonumber Primary Key to
uniquely identify each record, and store the date/time value in an indexed
date/time field? No conversion would be needed, you could just search it; it
would sort chronologically, and you could use date functions on it with no
conversion.

Have you established WHY records are getting entered with the same time to the
second? Is that in fact reasonable in a business sense, or might there be some
sort of erroneous data entry? Is this a multiuser app, and if so is it split?

 

[David]

Mr. Vinson:

Thanks for response.

In regard to:

Is this a multiuser app, and if so is it split?
No

Have you established WHY records are getting entered with the same time to
the second?

See below

====================

What's happening is one of those odd things. I'm using the following query:

'SQL Query
strParam = "PARAMETERS [pDateBeg] DATETIME, [pDateEnd] DATETIME; "
strSQL = strParam
strSQL = strSQL & "SELECT LAST(fldHistDateTime) AS fldDT,
FIRST(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow)
AS fldLow, LAST(fldHistClose) AS fldClose"
strSQL = strSQL & " FROM " & strTBLName
strSQL = strSQL & " WHERE ((fldHistDateTime) BETWEEN [pDateBeg] And
[pDateEnd]"
strSQL = strSQL & " And (Not fldHistOpen = 0)"
strSQL = strSQL & " And (Not fldHistHigh = 0)"
strSQL = strSQL & " And (Not fldHistLow = 0)"
strSQL = strSQL & " And (Not fldHistClose = 0));"

The source table has a DateTime Field. The above query is executed and a
single record returned. A seek is then performed of the Dest Table looking
for a date using the parameter (pDateEnd). If found it is edited,
other wise added. For whatever reason SOMETIMES -- and can be
replicated -- the Date is added when it should be Edited so two record
appear in the Access Table with the same DateTime even though it is a
PrimaryKey.

===========================
Not really sure an Indexed field would solve the problem as the .Seek
compare IMHO would result in same issue -- or am I missing something?

 

[John W. Vinson]

What's happening is one of those odd things. I'm using the following query:

'SQL Query
strParam = "PARAMETERS [pDateBeg] DATETIME, [pDateEnd] DATETIME; "
strSQL = strParam
strSQL = strSQL & "SELECT LAST(fldHistDateTime) AS fldDT,
FIRST(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow)
AS fldLow, LAST(fldHistClose) AS fldClose"
strSQL = strSQL & " FROM " & strTBLName
strSQL = strSQL & " WHERE ((fldHistDateTime) BETWEEN [pDateBeg] And
[pDateEnd]"
strSQL = strSQL & " And (Not fldHistOpen = 0)"
strSQL = strSQL & " And (Not fldHistHigh = 0)"
strSQL = strSQL & " And (Not fldHistLow = 0)"
strSQL = strSQL & " And (Not fldHistClose = 0));"

The source table has a DateTime Field. The above query is executed and a
single record returned. A seek is then performed of the Dest Table looking
for a date using the parameter (pDateEnd). If found it is edited,
other wise added. For whatever reason SOMETIMES -- and can be
replicated -- the Date is added when it should be Edited so two record
appear in the Access Table with the same DateTime even though it is a
PrimaryKey.

Part of the problem is that the LAST and FIRST operators may not be doing what
you think they're doing. They're returning the last and first records *in the
order that they were added to the database* - or the last and first in disk
storage order; these will *not* in general be the latest and earliest
chronologically. If you run this query multiple times this query may well
retrieve the same record each time.

Not sure if this is relevant to the very peculiar issue that you describe!

 

[David]

Not sure if this is relevant to the very peculiar issue that you describe!

The SQL query isn't but the paragraph after is. Put in query for a complete
read.

Since Access uses a double to hold a date, that precision can go out to
milliseconds. Access only displays dates to the second. Read some
conflicting articles though that Access 97 (which I'm using) also only
stores to second --- but I don't buy it .

My guess is that each time I pass in [pDateEnd] and subsequently use this
value in Seek, that the double is slightly different because of the
milliseconds. Hence two records which display the same but are actually
different.

However, In trying compare both records with the same date in Access
(debug.Print from VB5), it shows as the same datetime.

Any idea how you can display milliseconds?

What's happening is one of those odd things. I'm using the following
query:

'SQL Query
strParam = "PARAMETERS [pDateBeg] DATETIME, [pDateEnd] DATETIME; "
strSQL = strParam
strSQL = strSQL & "SELECT LAST(fldHistDateTime) AS fldDT,
FIRST(fldHistOpen) AS fldOpen, MAX(fldHistHigh) AS fldHigh,
MIN(fldHistLow)
AS fldLow, LAST(fldHistClose) AS fldClose"
strSQL = strSQL & " FROM " & strTBLName
strSQL = strSQL & " WHERE ((fldHistDateTime) BETWEEN [pDateBeg] And
[pDateEnd]"
strSQL = strSQL & " And (Not fldHistOpen = 0)"
strSQL = strSQL & " And (Not fldHistHigh = 0)"
strSQL = strSQL & " And (Not fldHistLow = 0)"
strSQL = strSQL & " And (Not fldHistClose = 0));"

The source table has a DateTime Field. The above query is executed and a
single record returned. A seek is then performed of the Dest Table
looking
for a date using the parameter (pDateEnd). If found it is edited,
other wise added. For whatever reason SOMETIMES -- and can be
replicated -- the Date is added when it should be Edited so two record
appear in the Access Table with the same DateTime even though it is a
PrimaryKey.

Part of the problem is that the LAST and FIRST operators may not be doing
what
you think they're doing. They're returning the last and first records *in
the
order that they were added to the database* - or the last and first in
disk
storage order; these will *not* in general be the latest and earliest
chronologically. If you run this query multiple times this query may well
retrieve the same record each time.

Not sure if this is relevant to the very peculiar issue that you describe!

 

[John W. Vinson]

Not sure if this is relevant to the very peculiar issue that you describe!

The SQL query isn't but the paragraph after is. Put in query for a complete
read.

Since Access uses a double to hold a date, that precision can go out to
milliseconds. Access only displays dates to the second. Read some
conflicting articles though that Access 97 (which I'm using) also only
stores to second --- but I don't buy it .

My guess is that each time I pass in [pDateEnd] and subsequently use this
value in Seek, that the double is slightly different because of the
milliseconds. Hence two records which display the same but are actually
different.

However, In trying compare both records with the same date in Access
(debug.Print from VB5), it shows as the same datetime.

Any idea how you can display milliseconds?

I'm not sure that this is the problem, but you could try using CDbl() to
convert the date time to a double, and multiply by 86400 to convert days
(date/time) to seconds.

 

[??]

Not sure if this is relevant to the very peculiar issue that you
describe!

The SQL query isn't but the paragraph after is. Put in query for a
complete
read.

Since Access uses a double to hold a date, that precision can go out to
milliseconds. Access only displays dates to the second. Read some
conflicting articles though that Access 97 (which I'm using) also only
stores to second --- but I don't buy it .

My guess is that each time I pass in [pDateEnd] and subsequently use this
value in Seek, that the double is slightly different because of the
milliseconds. Hence two records which display the same but are actually
different.

However, In trying compare both records with the same date in Access
(debug.Print from VB5), it shows as the same datetime.

Any idea how you can display milliseconds?

I'm not sure that this is the problem, but you could try using CDbl() to
convert the date time to a double, and multiply by 86400 to convert days
(date/time) to seconds.