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*********@di ageo.comwrote in message
news:11******** **************@ 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]

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*********@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]

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]

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" <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]

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

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.