473,779 Members | 2,089 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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,

Jan 23 '07
20 14269
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/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.

Jan 25 '07 #11
"purpleflas h" <ki**@bgs.ac.uk wrote in
news:11******** *************@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?
Jan 25 '07 #12


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?

Jan 25 '07 #13
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?
Jan 25 '07 #14
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.
Jan 25 '07 #15
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.

Jan 25 '07 #16
"keri" <ke*********@di ageo.comwrote in
news:11******** *************@m 58g2000cwm.goog legroups.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(dateWor king) <1 And Weekday(dateWor king) <7 Then
..Execute "INSERT INTO tblDates (fldDate) " _
& "VALUES (" _
& Format(dateWork ing, "\#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.

Jan 25 '07 #17
On Jan 25, 11:59 am, "purpleflas h" <k...@bgs.ac.uk wrote:
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 ("meaningles s") 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_conditi ons] table would be by [date_col] anyway.
So what is the point of having the arbitrary PK?
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?
Jan 25 '07 #18


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.

Jan 25 '07 #19
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?

Jan 25 '07 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
8107
by: DCM Fan | last post by:
{CREATE TABLEs and INSERTs follow...} Gents, I have a main table that is in ONE-MANY with many other tables. For example, if the main table is named A, there are these realtionships: A-->B A-->C A-->D
4
2645
by: qtip | last post by:
I have a simple table the has First Name , Last Name, SSN, Date&Time. I have a report that will show all this information but I would like to put in at calculation to tell the difference between 2 Date&time stamps for the person. The report shows Last name, First Name and Date&Tiem Field. If the persone has 2 records I would like to show the amount of time between each in put.
13
1802
by: Ron | last post by:
Hi All, Okay, I've read previous suggestions about not showing the primary key to the user of forms. If a number is to be shown (let's say, customer number) it should be generated independently of the primary key (which is autonumbered and no dups). I think I've gotten that all accomplished. However, now how should I link files, or perform query's? Should these types of things be based on the customer number I generate or should I...
0
1424
by: Takeadoe | last post by:
First, let me say that I'm brand new to Access, so please assume I know nothing. I've got a table (210k records) of deer harvest information. Date of harvest is one of many variables in the table. Since our season runs from the first SAT in October to 31 January, valid harvest dates for 2005-06 were 4 Oct 2005 to 31 Jan 2006. Many records will have missing or dates outside of this range. Date is needed to assign each dead deer to a...
9
3681
by: AideenWolf | last post by:
Windows XP - Access 2003 I've been researching this for about 3 weeks now and I'm no closer then when I started so any help you all give me will save my hair. I'm VERY new and learning as I go so bare with me. What I want to do is generate a new record for each date an event is held on. The events are reoccuring events. Daily, weekly, etc. What I've done so far: I have a foum that the user enters the Start date and End Date.
4
3572
by: mcca0081 | last post by:
hi - i'm trying to delete one of the duplicate records based on the most current date. here's the code for my access 2000 db. any help would be appreciated!!! - thank you kindly Sub DeleteDuplicateRecords() ' Deletes duplicates from the specified table, keeping the most current received date record. ' No user confirmation is required.
8
2429
by: Trev | last post by:
Hi Can anyone point me in the right direction here, I would like to open a table in access 2003 by date. I have an asp web page which needs to read data from a table with each days today's date (which ever day that is) then a new table is created with today's date. Example: I have a table called 17-may-2007 my ASP page reads this table for 24hours then tomorrow (12:00 midnight 18th) I will have a new table called 18-may-2007 and the...
2
11184
by: Brian Parker | last post by:
I am beginning to work with VB2005.NET and I'm getting some problems with string formatting converting an application from VB6. VB6 code:- sTradeDate = Format(pArray(4,i Record), "mmddyy") pArray is a variant array containing a date string at pArray(4, iRecord) in the format "yyyy/mm/dd"
8
2781
by: Dr Al | last post by:
I have a table with four date fields, some of which may not be filled in based on our data entry needs. I have a criteria set as <date()-180 which is supposed to pull dates older than 180 days ago. The problem is that when I use that criteria for all four fields I am not getting the expected results. I am trying to find out from this query is the date in date field one is older than 180, same thing for the other three date fields. For...
0
9474
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10306
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10139
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10075
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8961
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6727
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5373
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5504
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2869
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.