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.
"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?
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?
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?
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.
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.
"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.
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?
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.
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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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.
|
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...
|
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...
|
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.
| |
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.
|
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...
|
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"
|
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...
|
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,...
|
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...
| |
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...
|
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,...
|
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...
|
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();...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |