New Database and Linking tables

[Kelly]

Hi, I have a database that I have created in a normalized structure. The data
is information I've imported in from excel and all data tables have the same
amout of records or row count.

I've created a primary table and are releating to the other tables on the
Primary and Foreign keys.

How do I update or fill in the main table foreign key values that are
related to the tables?

I've tried a cascade update, but that didn't work. Do I need to create a
query to do this?

Thanks for any help, I'm stuck...

 

[Larry Linson]

I'm not certain what, exactly, you mean by "

Hi, I have a database that I have created in a normalized structure. The data
is information I've imported in from excel and all data tables have the same
amout of records or row count.

I've created a primary table and are releating to the other tables on the
Primary and Foreign keys.

When you say "relating to the other tables", do you mean you have, in the
Tools | Relationships window not only created a join but have specifically
created a relationship between the tables? I take what you said about
cascade update to mean "Yes" to this question. If I misunderstood, that is
the first thing to do.

Are you understanding correctly that a "foreign key" in the main table will
point to a "primary key" in the related table, and a cascading update
should, if you change the PK, change the related FKs in the main table? A
FK in the main table would mean that it is linked to one and only one record
in the related table. Have you opened the tables in design view and
designated the keys in the related tables as PKs?

How do I update or fill in the main table foreign key values that are
related to the tables?

I've tried a cascade update, but that didn't work. Do I need to create a
query to do this?

This would depend on how you are creating the tables... it is often
accomplished with a form/subform using the PK and FK as the MasterLinkFields
and ChildLinkFields, it is sometimes accomplished with queries (very
carefully). How are the Records related to one another in the Excel
Spreadsheet?

Larry Linson
Microsoft Office Access MVP

 

[Kelly]

Hi Larry,

Yes, I have created all the tables that have the data in them that came from
an excel file. It will not be linking to the excel file, it's just where the
data came from.

The tables I created are like this and are linked by a one to many
relationship with enforce referential integrity selected. All ID's in the
tables are there except the main table has no ID's in them relating to the
other tables. This is what I'm looking to accomplish;

main table; t_Rp_PpmCredits

RpID (PrimaryKey)
AssignID (foreign key)
CodeID (foreign key)
CustomerID (foreign key)
StartTime
EndTime

Linked Table1;

AssignID (Primary Key)
AssignedTo
ActionItems

Linked Table2;

CodeID (Primary Key)
Policy
VS_COS
Rppm_Code
Qrl

Linked Table3; t_Rp_CustomerInfo

CustomerID (Primary Key)
CustName
CustNum
OrderNum
Complaint

I have a few more tables with information in them (same anount of records).
But just to give an idea I only listed half of them.

I'm trying to get the main table to relate to the other tables.

Thank you for your quick response,

Kelly

 

[Kelly]

Larry, in response to your questions;

"When you say "relating to the other tables", do you mean you have, in the
Tools | Relationships window not only created a join but have specifically
created a relationship between the tables?"

Yes, I have created the relationship between the tables.

"Are you understanding correctly that a "foreign key" in the main table will
point to a "primary key" in the related table, and a cascading update
should, if you change the PK, change the related FKs in the main table? A
FK in the main table would mean that it is linked to one and only one record
in the related table. Have you opened the tables in design view and
designated the keys in the related tables as PKs?"

Yes, the foreign keys are pointing to the Primary Keys in the tables. I did
the Cascade update, on all tables and ran a compress/repair on the database,
but nothing shows up in the primary table. I am using Access2003.
All tables have the Primary Key specified.

No Forms have been created yet.


Thank you Larry,

Kelly

 

[John W. Vinson]

Hi, I have a database that I have created in a normalized structure. The data
is information I've imported in from excel and all data tables have the same
amout of records or row count.

Ummmm... not at all usual.

Each table should have as many records, no more, no fewer, as there are
entities of that type. That is, your Customer table should have one row for
each customer; your Policy table should have one row for each policy, and so
on.

Perhaps you could explain the real-world situation you're trying to model and
the purpose of some of these tables.

 

[Kelly]

Hi John,
Yes, all of the row count's are the same, 147 rcords in each table with the
Primary keys numbered the same 1 -147.

I don't know if this makes a difference, but the last table created was the
primary table with the foreign key. After that I created the one to many
relationship.

Also, there are some empty records in a few tables, and I don't have then
as being required.

"Perhaps you could explain the real-world situation you're trying to model and
the purpose of some of these tables."

The database is based on returned parts from customers, a description of the
problem, and the resolution to the transaction or refund. Also, the
transaction codes, the technician assigned to the problem, etc.

All this data was kept in Excel, and now they want it as a database.

The db will be added on in the future, so I'm trying to think ahead for the
naming scheme and design of the database.

Thank you,

 

[Kelly]

Error was caused from import containing a hyphin or space in front of ID.
Noticed error opening in access2007. All related tables in order - problem is
resovled.