Data from another table

[rocknmoll]

Ok, so this is basic, but I am relatively new to access and have hit a roadblock. I have looked at a bunch of threads here but can't find anything that addresses my question, so I thought it easier to state it here.

I have a master table, Project Tracker, which has all the details of the individual projects I have going on stored in columns, nearly 200 different data points. I have created a form which displays all the information from this table, and have included some tabs/pages in this form to help spread the load across a number of different pages.



One page, "Lab Results" is different. I want this to display information from another table. This table has 11 columns, and could contain multiple results for a certain case. Each time lab results come in, they are entered into the Lab Results table.

What I want to do is display the information only for that record on my lab results page. They share a common "Job Number", the only informational link between the 2 tables.

I am sure this is really easy, but need help. Thanks!!!

[kd2017]

We need more info on your current set up. Can you post screen shots of your table relationships and your form?

Sounds like you just want a subform...

[rocknmoll]

Here you go. Subform may be the answer. The first screenshot shows the Header screen. The second one is how I want the Lab page to look. There may be 20+ rows of results. I could do it by having a column for each field but I know that isn't the way to do it and there is a field limit.

Thanks for the response and your help to this most basic question.

[kd2017]

Yup, it just looks like you want to build a "continuous form" based on your lab results table. Then you would place this new form in the Laboratory tab of your main form. You would then link the form and subform ID's appropriately so the subform only shows lab results for the currently selected record in the main form.

Google or youtube search "Access Subforms" to learn how all of this done. Here is an example video: https://www.youtube.com/watch?v=8BZoR7JaKsY or https://www.youtube.com/watch?v=Yk-OtW4IHNA&t=182s

[rocknmoll]

Thank you, I will do that. Really appreciate the help.

[rocknmoll]

Ok, so I watched the video, and this is exactly what I want. However, I have another question. Attached is a SS of my table where I collect the Lab info. I have blanked out the proprietary info.

What I wish to show is every lab result for that particular job number in a list, like in my second screenshot above. A job may have 1 lab result, it might have 20. For example, job 371 in my list has 2 entries. When I look on my sub form, I want both sets of results to show in my form.

One way would be to have a single row for each job, and repeat the columns for each job, so have job name 1, job name 2, 3 etc, and then design the layout to look like my second screenshot. But I am guessing that isn't the correct way to do it, and it again limits me to 255 fields, and with 11 columns per Lab sample, I could only have a maximum of 13 Lab records per job number. I don't want to have to scroll through each record on my subform, there must be a way of displaying it all. It's almost like I want to see all my table info in a table like layout, but then it auto filter by job number as I move between records.

Sorry, and thanks for any help!

[kd2017]

Is [Job Number] a unique foreign key field?

You would want to link the foreign key field of the subform to the master key field of the main form. To do this, on the main form click on the subform and open the property sheet, in the Data tab you want to set Link Master Fields and Link Child Fields. This will automatically filter the subform to only records that belong to the current record in the main form.

[rocknmoll]

Job Number is the ID for the Main Table. It is manually entered into the Lab Table. I will try the above, thanks.

[kd2017]

When you have the subform properly linked to the main form there will be no need to manually enter the Job Number into the Lab table, Access will do that for you. This is the Access way to maintain data integrity. Also, make sure you have set up your table relationships and referential integrity in the Relationship window.

Also, per your screenshot in post #6 you don't want to have both Job Number and Job Name fields in the Lab report table. You only need the Job Number field, then you would use a query to pull in the Job Name from the job table (if needed).

[ssanfu]

And you really, really should remove spaces in object names. (fields, table,form,query and report names.)
Object names should only include letters, numbers and the underscore.

In the labels on a form/report you can add spaces to separate words.