Populating feilds from one table into another table in a form

[PhilMan2006]

I have a main form containing all the fields from Table 4. I have added a subform and have a look up field of Name and Clock# from Table 2 in which the Clock# from Table 2 populates Clock# in Table 3, and 3 other fields from Table 3 containing Clock #, Cost of Travel, Overall Rating. When I add a record all tables get updated, but the fields in Table 3 (Hours of Training, Internal Training, Vendor, and Cost of Training) do not get updated from Table 4 which are fields from the main form. If I can get these 4 fields to update I will be set. What am I doing wrong or what should I be doing? This is my first time using sub forms.

Here are the Tables and relationships.


Table 1
Department #
Department Desc

Table 2
Name
Clock #
Department #
Assigned Shift
Location Code
Active Status
Hire Date

Table 3
Class Number
Clock #
Hours of Training
Internal Training
Vendor
Cost of Training
Cost of Travel
Overall Rating

Table 4
Class Number (Autonumber)
Class Title
Begin Date
End Date
Hours of Training
Internal Training
Vendor
Cost of Training

Relationships:
Table1-Department # (1) to Table2 – Department # (Many)
Table2-Clock # (1) to Table3 – Clock# (Many)
Table3-Class Number (Many) to Table4 – Class Number (1)

 

[ProgramError]

have you tried requiry or refresh thew forms? Take a look in the help for more information about these.


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[PhilMan2006]

I have never worked with relational databases before. This is my first time. I am used to writing all the data to one table. I don't know where to start in getting the other 4 fields populated. Can you give me a starting point? What is your idea of the tables and the relationships?

 

[fneily]

Don't know why nobody else has answered your question. A few things first:
You can't learn Access on your own. Take classes.
You state you're used to writing data to one table. This is true for everyone in the world. That's called a "list", like Excel. It is not Access.
You MUST learn NORMALIZATION.
Give your tables names that suggest their topics.
Do not use autonumber.
You have duplicate entries for Hours of Training, Intenal Training, Vendor, Cost of Training. This is against NORMALIZATION.
Now:
You have the following tables:
tblDept with Department# and Department Name
tblPersonnel with Clock#, Name, Department#, Assigned Shift,Location Code, Active Status, and Hire Date.

tblClass with ClassID(no autonumber, make one up), Class Title, Begin Date, End Date, Hours of Training, Internal Training, Vendor, Cost of Training

tblTransaction with ClassID, Clock#, Cost of Travel, Overall Rating

The Primary key to tblTransaction is both ClassID and Clock# - a multifield primary key.

There's a one-many relationship between tblDept and tblPersonnel, there's a many-many between tblPersonnel and tblClasss. The tblTransaction is called a JUNCTION table because you can not have many-many relationships in a relational database. So it create two one-many's (tblPersonnel to tblTransaction, tblClass to tblTransaction).

So tblDept, tblClass, tblPersonnel are pre-filled. The tblTransaction table is where all the future data is entered. After that all the queries and report just fall out.

 

[ProgramError]

I find an easy way to understand relationships is..
If a field has the same data in more than one record then the duplicated data can be shifted into a separate table and looked up in this table using relationships.

for example: a suppliers name and address may be associated with some records, but you don't want to have to type the details in every time it appears in these records. So, you link the suppliers ID (not the address because the address will always be the same for the supplier) by using relationships one supplier to many records (one to many). Thus saving on space and if the supplier ever moves or changes their trading name then updating the supplier details updates all records linked to that suplier.

Hope this helps a little



Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[fneily]

ProgramError - you are correct basically. Foreign keys are permitted to be duplicated. But again, when you go through the steps of NORMALIZATION, it will clear up the situation you described.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PhilMan2006]

I modified the tables as listed above. All is working well, except that when I try to delete a record I get an error message that the record cannot be deleted because there are records in another table associated to the main record.

How do I delete a record from all the tables when I want to delete a record from the main table?

Do I have to use a delete query?

Thanks everyone for helping and answering my questions.

 

[PHV]

Either you enable cascade delete in the relationship (risky IMHO)
or you delete all the child rows (FKs) before deleting the main (PK).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PhilMan2006]

PHV, why do you feel that Cascade deletes are risky? There will be very few people in this database, does that change anything?

Would I have to delete the Child Rows first and then delete the Parent rows?