Problem linking tables

[Mikeyfyr]

Firtst I need to thank Wayne, Pat and everyone on this board for taking the time to assist me with my sorting issue. I now have that solved.

My problem now is adding records to "frmPersonnelInput". Initially I did all my input directly into the table. Now have built a form to make adding new personnel, or making changes easier. When I try to add a new name I get a pop up box that says "Field cannot be Updated" I also found out that I cannot make changes in 'tblPersonnel" that will update in the corresponding "tblSort" for rank and battalion even though everthing is linked in relationships. I think this should be very easy but I seem to be missing a step. Thanks for you time. I have attached a copy of the database if anyone would like to take a peek.

Mike
Firefighter
Sparks NV

 

[Pat Hartman]

The problem is the query of the subform. You have an extraneous table in it. I removed the Battalion table and obtained ShiftNum from tblSort.

SELECT tblPersonnel.EmployeeNum, tblPersonnel.LastName, tblPersonnel.FirstName, tblPersonnel.Phone, tblPersonnel.Rank, tblPersonnel.Battalion, tblSort.ShiftNum, tblSort.RankNum
FROM tblPersonnel INNER JOIN tblSort ON tblPersonnel.EmployeeNum = tblSort.EmployeeNum;

 

[Mikeyfyr]

Pat,

Thanks, that allows me to add a new records, great. I still cannot get the linked tables to update completely. "tblPersonnel" doesn't update the rank. "tblSort" doesn't update shiftnum. I built this query and the form with the wizard. I don't understand why everthing is not linked correctly. Should I rebuild the form directly to the tables instead of through a query? I sorry I am at a loss here, I thought I understood this part of Access, but I'm having problems with the smallest details now.

Mike

 

[Mikeyfyr]

Fix It

Okay, I fixed it.

I just had to move the rank and battalion fields out of the sort table and tie everything to the personnel table. I thought that having the employee number fields tied together that Access would make the connection from the personnel table to the rank and battalion tables through the sort table. Apparently that connection wasn't happening. So everything is working now. If you have been follow this and my other "Sort a record" thread, I now have all the problems solved and will be putting the staffing program in place on Friday.

Again thanks to Pat, Wayne, and everyone on this board. Because of this board, an a guy at our city IT office, I have been able to put this program together fairly simply. Something an "expert" told me I would have to get a proffesional programmer to do. We are a small department so we try to solve our problems as simply as possible, when we can.

Thank you very much.

Mike
Firefighter
Sparks NV

 

[Pat Hartman]

Mike, in a relational database there is no reason to duplicate data since you can always use a query to join the necessary tables. Therefore there will never be any option to "duplicate" data in related tables. The CascadeUpdate option is used only to propagate changes to the primary key of a table to the foreign key in any related tables. So, if you had a Customer table and an order table and in the order table you had CustomerID as a foreign key to the customer table, the value of CustomerID in the Order table would be changed if the value of the related CustomerID in the customer table were changed.