Combining Data Between Two Tables

[clong2013]

Hello,

I am still in the learning phase of Access but I am designing a classification program for my correctional facility that I work for. I want to basically retrieve information from another table on a form. On my form I have selected my Record Source from a table I called "ReClassification. That table stores my questionnaire answers. My other table called "ActiveUsers" stores all the user information of the people.

When I open up the form, I want to be able to link their files accordingly from ReClassification Table to ActiveUsers Table. I have tried to research about Table Relationships, but have not been found any good examples.

So if I opened up "John Smith", The data from ActiveUsers personal information will show up, but I the rest of the fields to be blank for me to fill out. Then storing all that information into Reclassifcation.

I'm sorry if this sounds confusing, I am trying to explain the best I can.

Thanks in advance!

 

[theDBguy]

Hi. Do you know if you have a 1-to-many relationship between the users info table and the reclassification table? If so, it would be better to use a form/subform setup. But if you have a many-to-many relationship instead, then that's a little complicated.

 

[clong2013]

This is the only relationship that I have, other than the ones Access has by default.

Each person we enter we give a BookingID number that is found in our other system. So I created a relationship with ActiveInmates "BookingID" to the Reclassification BookingID. I mainly want to store that, and their name information with the Reclassification answers on my form.

So highlighted in the red, I want it to pull information from ActiveInmates and in the green this will remain blank for me to fill out and to be stored in Reclassification table.

Hopefully that clears some of this up.

 

[June7]

Inmate info should not be saved into Reclassification. That would be duplication of data.

There is no designated primary key. Seems BookingID should be designated primary key in ActiveInmates (what happens when inmate is no longer 'active'?). Then set up a form/subform arrangement with linking on the two BookingID fields.