Query to update table values based on another table

[ndouglas48]

Hello All!



I must start by saying Access is rather new to me. I am more of an Excel guru but am starting to dip my feet into Access and am in need of a little help. I have created 2 tables. One is roster file that lists all of our current athletes including their unique ID #, name, and group. Obviously on this table the ID # is the primary key. On the second table I possess the exact same fields (with other columns) but cannot use a primary key on this table because this is where I place all of my athlete lifting data. Many athletes test multiple (could be unlimited) times so the ID # field must be able to accept duplicates (hence, can't be a primary key). So one table possesses all individual athletes, and the other table possesses all testing data for all athletes.

So here is my issue, whenever classes change I must change the "group" field on the Roster table for all athletes. I created an update query to update all records to null/blank. But now when I go to the Roster table and input an athlete's new group I want it to update all of the athlete's test records "group" field on the testing table. This way I don't have to go into the testing table and update all of these records.

Specific example would be if Steve Jones has his data on the Roster table and has 6 records of testing on the testing table, after all "group" fields are cleared using the update query I want to be able to update Steve's group on the Roster table and have this same value show up on all 6 records on the testing table.

Is this possible without a primary key on the testing table? If anyone knows a way to achieve this I would GREATLY appreciate it. Again, I am very new to Access so you may need to provide detailed steps. Thanks!

ND

[ItsMe]

Create a foreign key field in your second table. Store the relative PK value in the FK.

Also, your second table should have its own PK of the Autonumber data type.

[ndouglas48]

Still can't get it to work properly. I created an Autonumber data type in my Testing table labeled "#". I then made the "ID #" field on the Roster table the primary key (no duplicates) of the table and the "ID #" of the Testing table a foreign key (duplicates allowed); however, when I change the "Group" field on an athlete's record in the Roster table it does not update all of the records on the Testing table with the same "ID #" value. Thoughts?

[ItsMe]

First thought is not to use special characters in field names and object names. Using a conventional naming standard aka Naming Convention is highly recommended. Second thought is you will need the value in the foreign key field to match the value of the primary key field for the relative records. This will need to be the case before you can query the relative data.

[ndouglas48]

I have attached the file. I think I may be in over my head on this one. Basically all I want to happen is when I change the "group" field on the Master Roster table I would like it to fill in the exact same value in the "group" field on the Max Data table based on the ID #. If you could take a look at this I would greatly appreciate it. You are more than welcome to change anything around on the file to make this thing work. I have saved previous versions. Again, I am a noob and there is probably a better way to set up the table and relationships for this to work; however, I am not sure what needs to be done. I just need this to work and be simple. Again, any help is greatly appreciated. Thanks!

[ItsMe]

Like I mentioned, you need to study up on naming conventions. You can not have names with characters like #. Avoid spaces in names. Also, study up on normalization. You don't need to have the same dat in different tables. The data base uses rel;ationships to show the relative data. YOu do not place relative data in other tables/fields, you query it via the table relations.

I got rid of your duplicate fields, rid of your relationships, and the goofy ID name.

I created a couple queries and a couple forms. One form is a data entry form. On lift day you can use this form to enter results. It is named frmMaxLiftEnter

Afterwards, you can use the frmMaxLiftHist to view the results.

One form is for entering data and allows for editing, the other is for viewing and editing is locked.

You need to create a third form so you can add athletes to the Roster table.
Data.zip

[ndouglas48]

Thank you for all of you helpful tips. It is greatly appreciated!