New Database - 2003 Version-Access

[sjvandevoorde]

II am hoping someone out there will be able to give me some guidance, as I am about to give up. I have been working on this for some time and I think I have looked at it so much that I can't even think straight.

I am working on a database to help track projects. There are three different types of projects/screens that I need. What I have so far:

TABLE: Taskers (Screen/form 1)
FIELDS:
RMTaskerID – Autonumber/Primary Key
CGTaskerNmbr - Text
RMB - yes/no
RMC - yes/no
RMF - yes/no
DuetoRM - Date
Subject - Text
Description - Text
Completed - yes/no
FrequencyID - Number/Foreign key (Table: Frequency)
POCID - Number/Foreign key (Table: POCList)
RecvdFromID - Number/Foreign key (Table: RecvdFrom)

TABLE: SignatureLog (Screen/form 2)
SignatureID – Autonumber/Primary Key
Subject - Text
Description - Text
Completed - yes/no
FrequencyID - Number/Foreign key (Table: Frequency)
POCID - Number/Foreign key (Table: POCList)
RecvdFromID - Number/Foreign key (Table: RecvdFrom)

TABLE: CSALog (Screen/form 3)
CSAID – Autonumber/Primary Key
EM – yes/no
HardCopy – yes/no
Subject - Text
Description - Text
Completed - yes/no
FrequencyID - Number/Foreign key (Table: Frequency)
POCID - Number/Foreign key (Table: POCList)
RecvdFromID - Number/Foreign key (Table: RecvdFrom)

TABLE: Actions
ActionID – Autonumber/Primary Key
DateofAction – date
NotesRemarks – text
CSAID
SignatureID
RMTaskerID
POCID

TABLE: Frequency (List: Weekly, Monthly, Yearly, etc.)
FrequencyID – Autonumber/Primary Key

TABLE: ReceivedFrom (List of divisions assignment came from)
RecvdFromID – Autonumber/Primary Key
RecevdFrom – text

TABLE: POCList (List of employees)
POCID – Autonumber/Primary Key
LastName – text
FirstName – text
Extension – text

What I need are three different screens for tracking and each record will need their own unique number for each record added. There will be many actions to each record/project.

When trying to connect relationship between table Signaturelog and Actions I keep getting the error message:

**Data in the table 'tblActions' violates referential integrity rules. For example, there may be records relating to an employee in the related table, but no record f or the employee in the primary table.
Edit the data so that records in the primary table exist for all related records.**

I haven’t done one of these in some time and I know there’s something wrong with the relationships….

I have a subform for actions which I developed for the Tasker table and it seems to be working okay, but can’t get any further. What is wrong with this setup??

I don’t necessarily have to have it setup this way, but need to be able to have unique numbers for each record for each table:
Taskers
CSALog
SignatureLog

I know there are some fields that are the same and used in all three tables – is there a way to have only one table (instead of three) but be able to have an autonumber generated for each different project/record when each separate form is opened? (I will have a different form for each one CSA, Signature, Taskers.)
The table/fields would be:
TABLE: Projects
ProjectID - Autonumber
CGTaskerNmbr - Text
RMB - yes/no
RMC - yes/no
RMF - yes/no
EM – yes/no
Hardcopy – yes/no
DuetoRM - Date
Subject - Text
Description - Text
Completed - yes/no
FrequencyID - Number/Foreign key (Table: Frequency)
POCID - Number/Foreign key (Table: POCList)
RecvdFromID - Number/Foreign key (Table: RecvdFrom)

Thanks for your help.

Sandra

[Rabbit]

II am hoping someone out there will be able to give me some guidance, as I am about to give up. I have been working on this for some time and I think I have looked at it so much that I can't even think straight.

I am working on a database to help track projects. There are three different types of projects/screens that I need. What I have so far:

TABLE: Taskers (Screen/form 1)
FIELDS:
RMTaskerID – Autonumber/Primary Key
CGTaskerNmbr - Text
RMB - yes/no
RMC - yes/no
RMF - yes/no
DuetoRM - Date
Subject - Text
Description - Text
Completed - yes/no
FrequencyID - Number/Foreign key (Table: Frequency)
POCID - Number/Foreign key (Table: POCList)
RecvdFromID - Number/Foreign key (Table: RecvdFrom)

TABLE: SignatureLog (Screen/form 2)
SignatureID – Autonumber/Primary Key
Subject - Text
Description - Text
Completed - yes/no
FrequencyID - Number/Foreign key (Table: Frequency)
POCID - Number/Foreign key (Table: POCList)
RecvdFromID - Number/Foreign key (Table: RecvdFrom)

TABLE: CSALog (Screen/form 3)
CSAID – Autonumber/Primary Key
EM – yes/no
HardCopy – yes/no
Subject - Text
Description - Text
Completed - yes/no
FrequencyID - Number/Foreign key (Table: Frequency)
POCID - Number/Foreign key (Table: POCList)
RecvdFromID - Number/Foreign key (Table: RecvdFrom)

TABLE: Actions
ActionID – Autonumber/Primary Key
DateofAction – date
NotesRemarks – text
CSAID
SignatureID
RMTaskerID
POCID

TABLE: Frequency (List: Weekly, Monthly, Yearly, etc.)
FrequencyID – Autonumber/Primary Key

TABLE: ReceivedFrom (List of divisions assignment came from)
RecvdFromID – Autonumber/Primary Key
RecevdFrom – text

TABLE: POCList (List of employees)
POCID – Autonumber/Primary Key
LastName – text
FirstName – text
Extension – text

What I need are three different screens for tracking and each record will need their own unique number for each record added. There will be many actions to each record/project.

When trying to connect relationship between table Signaturelog and Actions I keep getting the error message:

**Data in the table 'tblActions' violates referential integrity rules. For example, there may be records relating to an employee in the related table, but no record f or the employee in the primary table.
Edit the data so that records in the primary table exist for all related records.**

I haven’t done one of these in some time and I know there’s something wrong with the relationships….

I have a subform for actions which I developed for the Tasker table and it seems to be working okay, but can’t get any further. What is wrong with this setup??

I don’t necessarily have to have it setup this way, but need to be able to have unique numbers for each record for each table:
Taskers
CSALog
SignatureLog

I know there are some fields that are the same and used in all three tables – is there a way to have only one table (instead of three) but be able to have an autonumber generated for each different project/record when each separate form is opened? (I will have a different form for each one CSA, Signature, Taskers.)
The table/fields would be:
TABLE: Projects
ProjectID - Autonumber
CGTaskerNmbr - Text
RMB - yes/no
RMC - yes/no
RMF - yes/no
EM – yes/no
Hardcopy – yes/no
DuetoRM - Date
Subject - Text
Description - Text
Completed - yes/no
FrequencyID - Number/Foreign key (Table: Frequency)
POCID - Number/Foreign key (Table: POCList)
RecvdFromID - Number/Foreign key (Table: RecvdFrom)

Thanks for your help.

Sandra

Take a look at Normalisation and Table Structures. This will help you to better organize your tables.

As for your problems, I haven't done an in depth analysis of your tables but it seems to me that you have records in your foreign key tables that do not have matching records in the primary key tables. For every record with a foreign key, there has to be a matching record in the table with a primary key. The opposite is not true. You can have a primary keyed record without foreign keyed records.
So check that all your foreign keys have a matching primary key.

I have a subform for actions which I developed for the Tasker table and it seems to be working okay, but can’t get any further.
You can't get any further than what? What's the hurdle that's stopping you? Where are you trying to get to?

It's best not to have repeating data over between and within tables. The link above will further explain.