Use a date as a primary key - and assign a record to each date in 2007

[keri]

Hi,

I am creating a table where I want to use the date as the primary key -
and to automatically create a record for each working date (eg Mon to
Fri) until 30 June 2007. Is this possible? I do not want my user to
have to create a record for each date.

Thanks,

[Phil Stanton]

Bad idea using date as primary key. Access handles dates erratically. I
interpret 04/05/2006 as 4th May 2006. Access may interpret it as 5th April
2006
You can have a field called Todat and set it's default value as Date() which
means that anythin you enter will automatically have todays date. You can
also set is as indexed - Yes (No Duplicates), which will only allow you to
use that date once. Is that really what you want?

Phil
"keri" <ke*********@diageo.comwrote in message
news:11**********************@j27g2000cwj.googlegr oups.com...

Hi,

I am creating a table where I want to use the date as the primary key -
and to automatically create a record for each working date (eg Mon to
Fri) until 30 June 2007. Is this possible? I do not want my user to
have to create a record for each date.

Thanks,

[keri]

Possibly - but I don't think so.

My db is for planning. I want my users to assign a category (named P1
to P6) to each working day in a year. (This will populate an
appointment in outlook to show on the calendar). However if I do this
directly to outlook (eg the user enters this through a form and I code
it to create the appointment in outlook) the user would have to flick
between the outlook calendar and the db to see which days have not been
assigned a category.

However if I could have a table which already contained a record for
each day then they could view this to see which dates they still needed
to assign categories to.

Does this make sense? I may be going about this in the wrong way.

[Lyle Fairfield]

"keri" <ke*********@diageo.comwrote in news:1169571384.868871.109610
@j27g2000cwj.googlegroups.com:

Hi,

I am creating a table where I want to use the date as the primary key -
and to automatically create a record for each working date (eg Mon to
Fri) until 30 June 2007. Is this possible? I do not want my user to
have to create a record for each date.

Dim dateWorking As Date
Dim dateEnd As Date
dateWorking = DateSerial(2006, 9, 1)
dateEnd = DateSerial(2007, 6, 30)
With CurrentProject.Connection
.Execute "CREATE TABLE tblDates (fldDate DateTime CONSTRAINT PrimaryKey
Primary Key)"
While DateDiff("d", dateWorking, dateEnd) >= 0
If Weekday(dateWorking) <1 And Weekday(dateWorking) <7 Then _
.Execute "INSERT INTO tblDates (fldDate) VALUES (" & Format
(dateWorking, "\#mm\/dd\/yyyy\#\)")
dateWorking = DateAdd("d", 1, dateWorking)
Wend
End With

News Clients will splits some lines that should not be split. These will
will require correction.

[keri]

Lyle,

This looks amazing - i'm just going away to try it. (What would be the
restriction on the end date being 2010 for example - is this too many
records from a practical point of view?)

What does the .connection in the code mean? I have never worked this
out.

Dim dateWorking As Date

Dim dateEnd As Date
dateWorking = DateSerial(2006, 9, 1)
dateEnd = DateSerial(2007, 6, 30)
With CurrentProject.Connection
.Execute "CREATE TABLE tblDates (fldDate DateTime CONSTRAINT PrimaryKey
Primary Key)"
While DateDiff("d", dateWorking, dateEnd) >= 0
If Weekday(dateWorking) <1 And Weekday(dateWorking) <7 Then _
.Execute "INSERT INTO tblDates (fldDate) VALUES (" & Format
(dateWorking, "\#mm\/dd\/yyyy\#\)")
dateWorking = DateAdd("d", 1, dateWorking)
Wend
End With

News Clients will splits some lines that should not be split. These will
will require correction.

[keri]

Lyle,

I am currently having problems with my Access (which I can't solve
until I can re-install next week). Is there another way to insert these
dates into a current table without using the currentproject.conncection
and execute commands as these are ones that bomb my access.

Thanks.

[Lyle Fairfield]

On Jan 24, 5:25 pm, "keri" <[e-mail]:

Lyle,

I am currently having problems with my Access (which I can't solve
until I can re-install next week). Is there another way to insert these
dates into a current table without using the currentproject.conncection
and execute commands as these are ones that bomb my access.

Thanks

Well 2010 could be a problem. It's likely to take another second, maybe
even a second and a half, to add those addtional three years of
records. But if you have that second and half, it'll be fine.

Dim dateWorking As Date
Dim dateEnd As Date
dateWorking = DateSerial(2006, 9, 1)
dateEnd = DateSerial(2010, 6, 30)
With CurrentDb
.Execute "CREATE TABLE tblDates (fldDate DateTime)"
.Execute "CREATE INDEX [Primary Key] ON tblDates (fldDate) WITH
PRIMARY"
While DateDiff("d", dateWorking, dateEnd) >= 0
If Weekday(dateWorking) <1 And Weekday(dateWorking) <7 Then
_
.Execute "INSERT INTO tblDates (fldDate) VALUES (" &
Format(dateWorking, "\#mm\/dd\/yyyy\#\)")
dateWorking = DateAdd("d", 1, dateWorking)
Wend
End With

[Tim Marshall]

keri wrote:

Does this make sense? I may be going about this in the wrong way.

I also wouldn't recommend a date as a primary key. I could be wrong,
but it sounds too scary. My personal approach would be to populate
records, perhaps as Lyle has suggested, except add a unique index to the
date field. I'd then just use an autonumber as the PK.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[purpleflash]

Hi Folks

I agree in general dates make very iffy PK's
And it is generally speaking not a great idea to pre-create 'blank'
rows in a table!
I may be missing something here but does the table has a date and a
category field for each user? If so what happens when you get a new
user - add a new field? - not great design

Perhaps a better solution would be a table with Autonumber(PK),
[User(Unique Index Duplicates allowed), Date(Unique Index Duplicates
allowed)] as an Alternate Key plus Category!
then only store completed records.

Calendaring applications are a bit of difficulty. I'd try to use shared
calendars in outlook/exchange myself.

Purpleflash

On Jan 25, 4:17 am, Tim Marshall
<[e-mail]:

keri wrote:

Does this make sense? I may be going about this in the wrong way.I also wouldn't recommend a date as a primary key. I could be wrong,

but it sounds too scary. My personal approach would be to populate
records, perhaps as Lyle has suggested, except add a unique index to the
date field. I'd then just use an autonumber as the PK.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[purpleflash]

I don't really get this
In outlook if there isn't a category assigned then surely that is
enough to show that there isn't one? Why do you need a table as well?

Purpleflash

keri wrote:

Possibly - but I don't think so.

My db is for planning. I want my users to assign a category (named P1
to P6) to each working day in a year. (This will populate an
appointment in outlook to show on the calendar). However if I do this
directly to outlook (eg the user enters this through a form and I code
it to create the appointment in outlook) the user would have to flick
between the outlook calendar and the db to see which days have not been
assigned a category.

However if I could have a table which already contained a record for
each day then they could view this to see which dates they still needed
to assign categories to.

Does this make sense? I may be going about this in the wrong way.

[Lyle Fairfield]

On Jan 24, 5:25 pm, "keri" <[e-mail]:

I am currently having problems with my Access (which I can't solve
until I can re-install next week). Is there another way to insert these
dates into a current table without using the currentproject.conncection
and execute commands as these are ones that bomb my access.

CurrentProject appeared in Access 2000. If you are using Access 97,
CurrentProject should not be there.

If you are using Access 2000 or later version you might try downloading
and installing MDAC 2.8 from
http://www.microsoft.com/downloads/d...7-185d0506396c
and setting your ADODB reference to
Microsoft ActiveX DataObjects 2.8 Library
first. That's a much simpler process then reinstalliing Access, and,
IMO, much more ikely to solve the problem you describe which can occur
when requisite parts of MDAC are missing.

In any case, I would download and run ComChecker
http://www.microsoft.com/downloads/d...3-14332ef092c9
It's should identify your CurrentProject problem.

[Lyle Fairfield]

"purpleflash" <ki**@bgs.ac.ukwrote in
news:11*********************@s48g2000cws.googlegro ups.com:

Perhaps a better solution would be a table with Autonumber(PK),
[User(Unique Index Duplicates allowed), Date(Unique Index Duplicates
allowed)]

Would these --- Unique Indexes Duplicates Allowed --- be both Ascending
and Descending?

[Gord]

On Jan 24, 11:17 pm, Tim Marshall
<[e-mail]:

keri wrote:

Does this make sense? I may be going about this in the wrong way.

I also wouldn't recommend a date as a primary key. I could be wrong,
but it sounds too scary. My personal approach would be to populate
records, perhaps as Lyle has suggested, except add a unique index to the
date field. I'd then just use an autonumber as the PK.

I'm curious as to why people seem to object in principle to using a
date column as a primary key. Is it because Access stores date/time
values as floating point numbers, or is it for some other reason?

Say, for example, I have a fleet of ice cream trucks. I have a database
that tracks sales activity for the various trucks each day. I want to
store weather information for each day so I can compare daily sales to
things like daily_high_temp, conditions ('sunny', 'cloudy'), etc.. This
information will help me schedule my drivers based on the weather
forecast for the next day. The [weather_conditions] table would look
like

date_col
daily_high_temp
conditions
....

and [date_col] is the obvious candidate for the primary key, isn't it?

[purpleflash]

One objection is that when you store a date (in Ms-Access) you are
beholden to the international settings in WIndows for the storage of
the entered date to a number.

So 11/12/06 on entry can be either 11th Dec 2006 or Nov 12 2006
depending on those settings! On setup Windows defaults to US settings
not UK ones. It's easy to see that errors can happen on entry. Often
the settings are not noticed untill an impossible date turns up
13/12/07 is fine in UK and impossible in US for example. The mess can
grow considerably when formatting and input masks are introduced on top
of international settings

There is another tenet in relational design (born out by bitter and
painful experience) which argues that there are potential dangers in
giving a PK meaning (outside of a domain/dictionary) and disastrous if
it can have multiple meanings!
On Jan 25, 1:37 pm, "Gord" <[e-mail]:

On Jan 24, 11:17 pm, Tim Marshall

<[e-mail]:

keri wrote:

Does this make sense? I may be going about this in the wrong way.

I also wouldn't recommend a date as a primary key. I could be wrong,
but it sounds too scary. My personal approach would be to populate
records, perhaps as Lyle has suggested, except add a unique index to the
date field. I'd then just use an autonumber as the PK.I'm curious as to why people seem to object in principle to using a

date column as a primary key. Is it because Access stores date/time
values as floating point numbers, or is it for some other reason?

Say, for example, I have a fleet of ice cream trucks. I have a database
that tracks sales activity for the various trucks each day. I want to
store weather information for each day so I can compare daily sales to
things like daily_high_temp, conditions ('sunny', 'cloudy'), etc.. This
information will help me schedule my drivers based on the weather
forecast for the next day. The [weather_conditions] table would look
like

date_col
daily_high_temp
conditions
...

and [date_col] is the obvious candidate for the primary key, isn't it?

[keri]

Hi everyone,

I need a way to force my user to only have one category per date, and
if they want to change this category to do it in my table - NOT through
outlook.

If my users have to look at outlook, then discover which date they want
to assign a category for, then go back and enter this date as a record
it could get messy and complicated.
However if my user could see on a continuous form whcih dates are and
are not planned, and could easily change which categories are assigned
to the dates things become easier (for the user and for me). This way I
can update the categories shown in the outlook calendar he table. I do
not want to be having to import outlook appointments back into my
tables if the user changes something on outlook instead of my db.

Does that help at all?

Anyway, Lyle, the second code is creating the table and assigning the
primary key but not entering any values. I am not sure how to start
altering the code so it works, any suggestions?

On 24 Jan, 21:38, Lyle Fairfield <[e-mail]:

"keri" <[e-mail] in news:1169571384.868871.109610
@j27g2000cwj.googlegroups.com:

Hi,

I am creating a table where I want to use the date as the primary key -
and to automatically create a record for each working date (eg Mon to
Fri) until 30 June 2007. Is this possible? I do not want my user to
have to create a record for each date.Dim dateWorking As Date

Dim dateEnd As Date
dateWorking = DateSerial(2006, 9, 1)
dateEnd = DateSerial(2007, 6, 30)
With CurrentProject.Connection
.Execute "CREATE TABLE tblDates (fldDate DateTime CONSTRAINT PrimaryKey
Primary Key)"
While DateDiff("d", dateWorking, dateEnd) >= 0
If Weekday(dateWorking) <1 And Weekday(dateWorking) <7 Then _
.Execute "INSERT INTO tblDates (fldDate) VALUES (" & Format
(dateWorking, "\#mm\/dd\/yyyy\#\)")
dateWorking = DateAdd("d", 1, dateWorking)
Wend
End With

News Clients will splits some lines that should not be split. These will
will require correction.

[keri]

Sorry a further explanation I forgot to add;

The category is not equal to a user - it is a way of planning an area
that will be visited by the user on that day.
For example if a user programs category P1 on 01/01/07 they will visit
the area that P1 relates to on that date. Other categories (after P1 to
P6) would include holiday, meetings etc so they can also plan days they
would not be visiting a Pnumbered area.

[Lyle Fairfield]

"keri" <ke*********@diageo.comwrote in
news:11*********************@m58g2000cwm.googlegro ups.com:

Anyway, Lyle, the second code is creating the table and assigning the
primary key but not entering any values. I am not sure how to start
altering the code so it works, any suggestions?

As the code works here it should work there. Here is a slightly changed
version that might not get cut up by the news clients. It includes a new
line to delete the table you have already created. That line can be removed
or commented out.

Dim dateWorking As Date
Dim dateEnd As Date
dateWorking = DateSerial(2006, 9, 1)
dateEnd = DateSerial(2010, 6, 30)
With CurrentDb
..Execute "DROP Table tblDates"
..Execute "CREATE TABLE tblDates (fldDate DateTime)"
..Execute "CREATE INDEX [Primary Key] ON tblDates (fldDate) WITH PRIMARY"
While DateDiff("d", dateWorking, dateEnd) >= 0
If Weekday(dateWorking) <1 And Weekday(dateWorking) <7 Then
..Execute "INSERT INTO tblDates (fldDate) " _
& "VALUES (" _
& Format(dateWorking, "\#mm\/dd\/yyyy\#\)")
End If
dateWorking = DateAdd("d", 1, dateWorking)
Wend
End With

In addition, the code is attached in a simple text file which should not
get cut up. Some clients will delete this and some some posters here will
howl. Tell me I care.

[Gord]

On Jan 25, 11:59 am, "purpleflash" <[e-mail]:

One objection is that when you store a date (in Ms-Access) you are
beholden to the international settings in WIndows for the storage of
the entered date to a number.

So 11/12/06 on entry can be either 11th Dec 2006 or Nov 12 2006
depending on those settings! On setup Windows defaults to US settings
not UK ones. It's easy to see that errors can happen on entry. Often
the settings are not noticed untill an impossible date turns up
13/12/07 is fine in UK and impossible in US for example. The mess can
grow considerably when formatting and input masks are introduced on top
of international settings

....but that is an issue with *any* date in Access, whether it is used
as a primary key or not. I don't see it as a compelling argument
against using a date as a PK unless your next point implies that all
PKs should be arbitrary ("meaningless") values.

There is another tenet in relational design (born out by bitter and
painful experience) which argues that there are potential dangers in
giving a PK meaning (outside of a domain/dictionary) and disastrous if
it can have multiple meanings!

However, there are also those who argue that a PK *must* be meaningful
and they rail against the use of arbitrary PKs such as IDENTITY
(AutoNumber) columns. Taken to the extreme, their argument is that if
there is no subset of "real" columns that can serve as the PK then the
PK for that table should be *every* column (because one of "the rules"
is that a table must never have two identical rows).

Furthermore, in my example the [date_col] PK would have meaning but
that meaning would be clear and unambiguous. To create an arbitrary PK
and then add a "UNIQUE, NOT NULL" constraint on [date_col] would simply
introduce redundancy because there would have to be a 1:1 relationship
between the PK and [date_col], and the relationship between the [sales]
table and the [weather_conditions] table would be by [date_col] anyway.
So what is the point of having the arbitrary PK?

On Jan 25, 1:37 pm, "Gord" <[e-mail]:

On Jan 24, 11:17 pm, Tim Marshall

<[e-mail]:

keri wrote:
Does this make sense? I may be going about this in the wrong way.

I also wouldn't recommend a date as a primary key. I could be wrong,
but it sounds too scary. My personal approach would be to populate
records, perhaps as Lyle has suggested, except add a unique index to the
date field. I'd then just use an autonumber as the PK.I'm curious as to why people seem to object in principle to using a

date column as a primary key. Is it because Access stores date/time
values as floating point numbers, or is it for some other reason?

Say, for example, I have a fleet of ice cream trucks. I have a database
that tracks sales activity for the various trucks each day. I want to
store weather information for each day so I can compare daily sales to
things like daily_high_temp, conditions ('sunny', 'cloudy'), etc.. This
information will help me schedule my drivers based on the weather
forecast for the next day. The [weather_conditions] table would look
like

date_col
daily_high_temp
conditions
...

and [date_col] is the obvious candidate for the primary key, isn't it?

[keri]

Lyle, thanks, this works perfectly.
On the MDAC note - I tried all those things (i followed a post of
instructions that I didn't understand!) but it made no difference.

Thanks again for the code.

[keri]

One more thing (demanding I know!) any way of opening the form of this
table starting from today's date?

Should I query the table and use an expression to only show dates after
today?

[Lyle Fairfield]

On Jan 25, 1:06 pm, "keri" <[e-mail]:

One more thing (demanding I know!) any way of opening the form of this
table starting from today's date?

Should I query the table and use an expression to only show dates after
today?

Sounds like an idea to me. You could try, post the code/sql and make us
all a little smarter. Well, maybe not all ....