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]

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

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" <keri.dowson@di ageo.comwrote in message
news:1169571384 .868871.109610@ j27g2000cwj.goo glegroups.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]

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

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]

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

"keri" <keri.dowson@di ageo.comwrote in news:1169571384 .868871.109610
@j27g2000cwj.go oglegroups.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(dateWor king) <1 And Weekday(dateWor king) <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]

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

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(dateWor king) <1 And Weekday(dateWor king) <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]

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



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]

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



On Jan 24, 5:25 pm, "keri" <keri.dow...@di ageo.comwrote:

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(dateWor king) <1 And Weekday(dateWor king) <7 Then
_
.Execute "INSERT INTO tblDates (fldDate) VALUES (" &
Format(dateWork ing, "\#mm\/dd\/yyyy\#\)")
dateWorking = DateAdd("d", 1, dateWorking)
Wend
End With

[Tim Marshall]

Re: Use a date as a primary key - and assign a record to each datein 2007

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]

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

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
<TIM...@PurpleP andaChasers.Moe rtheriumwrote:

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]

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

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]

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

On Jan 24, 5:25 pm, "keri" <keri.dow...@di ageo.comwrote:

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/details.aspx?familyid=6c050fe3-c795-4b7d-b037-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/details.aspx?FamilyID=8f0a8df6-4a21-4b43-bf53-14332ef092c9

It's should identify your CurrentProject problem.

[Lyle Fairfield]

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

"purpleflas h" <kigl@bgs.ac.uk wrote in
news:1169715011 .954328.14700@s 48g2000cws.goog legroups.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]

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



On Jan 24, 11:17 pm, Tim Marshall
<TIM...@PurpleP andaChasers.Moe rtheriumwrote:

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_conditi ons] 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]

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

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" <g...@kingston. netwrote:

On Jan 24, 11:17 pm, Tim Marshall
>
<TIM...@PurpleP andaChasers.Moe rtheriumwrote:

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_conditi ons] 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]

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

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 <n...@thanks.co mwrote:

"keri" <keri.dow...@di ageo.comwrote in news:1169571384 .868871.109610
@j27g2000cwj.go oglegroups.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(dateWor king) <1 And Weekday(dateWor king) <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.