I'm having trouble with AutoNumber

[Tim Gillen Jr.]

I am trying to create a database that will track tasks
that my boss has with his secretary throughout the day. I
am having trouble with creating the relationships with the
tables (I think, maybe im way off!). Here is the design
of my database so far (plz recommend any changes you see
fit)...

Table 1 Tasks

FIELDNAME DATATYPE
TaskNumber AutoNumber
TaskType Text
Task Memo
TaskPriority Number
OriginatedBy Text
DesignatedTo Text

Table 2 Date/Time

FIELDNAME DATATYPE
TaskNumber AutoNumber
StartDate Date/Time
PresumedEndDate Date/Time
EndDate Date/Time
StartTime Date/Time
EndTime Date/Time
TimeAlive Text


If you see any changes to this structure plz let me know.
I wanted to create a relationship with table1 to table2 by
using the AutoNumber, but when I do this I get an error
saying,

"Invalid field definition 'TaskNumber' in definition of
index or relationship."

Also it says my relationship type is a One-to-One, which
im unsure if that is correct, since a task could
conceivably have the same date and time etc. I only get
this error when I turn "Enforce Referential Integrity" on
with "Cascade Updates/Deletes". I would want this,
wouldn't I?

When I turn Referential Integrity off I get a One-to-One
relationship but there are no number 1's on the line in
the relationships window.

I'm not sure what I am doing wrong! Any help would be
appreciated!

 

[Guest]

-----Original Message-----
I am trying to create a database that will track tasks
that my boss has with his secretary throughout the day. I
am having trouble with creating the relationships with the
tables (I think, maybe im way off!). Here is the design
of my database so far (plz recommend any changes you see
fit)...

Table 1 Tasks

FIELDNAME DATATYPE
TaskNumber AutoNumber
TaskType Text
Task Memo
TaskPriority Number
OriginatedBy Text
DesignatedTo Text

Table 2 Date/Time

FIELDNAME DATATYPE
TaskNumber AutoNumber
StartDate Date/Time
PresumedEndDate Date/Time
EndDate Date/Time
StartTime Date/Time
EndTime Date/Time
TimeAlive Text


If you see any changes to this structure plz let me know.
I wanted to create a relationship with table1 to table2 by
using the AutoNumber, but when I do this I get an error
saying,

"Invalid field definition 'TaskNumber' in definition of
index or relationship."

Also it says my relationship type is a One-to-One, which
im unsure if that is correct, since a task could
conceivably have the same date and time etc. I only get
this error when I turn "Enforce Referential Integrity" on
with "Cascade Updates/Deletes". I would want this,
wouldn't I?

When I turn Referential Integrity off I get a One-to-One
relationship but there are no number 1's on the line in
the relationships window.

I'm not sure what I am doing wrong! Any help would be
appreciated!

.
The datatype of the second table TaskNumber field should

be Long Integer instead of Autonumber. But it looks to me
like all data from both tables could be in one single
table.

 

[Immanuel Sibero]

Hi

I agree with the TaskNumber on the second table being long integer. As a
matter of fact, the Autonumber designation is the reason why Access thinks
it's a one-to-one relationship. A one-to-one relationship is rarely needed.
In most cases, two tables with one-to-one relationship should probably be
one table containing fields from the two tables.

As suspected, the second table is really the child table of Tasks, so if
there is any changes it would be to set the relationship in such a way where
there is one-to-many relationship between Tasks table and Data/Time table.
However, I would suggest a design with three tables:



Task 1 -----> M TaskAssignment M<----1 Staff



Task
- TaskNumber PK
- TaskName
- TaskType
- etc...



TaskAssignment
- TaskNumber FK
- StaffNumber FK
- StartDate
- EndDate
- etc...


Staff
- StaffNumber PK
- StaffName
- etc....


HTH,
Immanuel Sibero