Viewing information not in form's record source

[zepphead80]

Hi:

I know there's a real basic answer to this. I'm fairly new at designing Access forms...

I have a form whose primary responsibility is to display information from two different tables, tblEmployees and tblWC. These tables are joined in a one-to-many relationship (one employee can have many Workers Compensation claims). In addition, the form's Recordsource contains the two tables via a SELECT statement.

The problem is, I have several smaller tables that contain information to display on the form as well. For example, tblWC contains fldInjuryCode, while tblInjuryType has fldInjuryCode and fldInjuryDescription. The injury code will display on the form because it's already in tblWC, which is part of the form Recordsource, but how can I get the form to display the injury description from tblInjuryType, which is NOT in the Recordsource?

Thanks in advance for any help...

Pat

 

[PHV]

Why not add tblInjuryType in the RecordSource ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SgtJarrow]

Ideally what I would want to do in this scenario is get the recordsource corrected to include all the fields to show.

The easiest way to do this is to create a query. Start a query based of the table you want to start with. Add the other tables to the query and make sure your relationships are set up. If using the designer window, you can look at all the litle connecting lines between the tables.

As long as you have all the relationships set up, you add all the columns from the appropriate tables...So EmpId, blah, blah, InjDesc, etc until complete.

Then, your form/report is based off this query, instead of the tables directly.

Does that make sense to you?? If you need a bit more help, post your table structures here. We can help you build the query via a SQL statement and you can paste it in and see that graphical representation and see how it did what it did.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[zepphead80]

Thank you...

This is my first time using the forum. How do I post my table structures here?

 

[zepphead80]

Hi again:

What I ended doing was setting the form's Recordsource equal to the following SQL statement...

SELECT tblWC.*, tblEmployees.*, tblInjuryType.*
FROM tblEmployees INNER JOIN (tblInjuryType INNER JOIN tblWC ON tblInjuryType.fldTypeOfInjuryCode = tblWC.fldTypeOfInjuryCode) ON tblEmployees.fldERN = tblWC.fldERN;

This is essentially PHV's suggestion from above, and it works just fine. It just seems to me that there's a more "elegant" way to do it rather than adding each table that I need to the SQL statement.

Pat

 

[SgtJarrow]

Pat,

What would be more elegant? It would not make any sense to have a value in a table and not reference it via that table. PHV's suggestion (and mine - they were basically the same thing worded two different ways) was spot on - and this is the way relational databases have worked for as long as I can remember.

All said, glad you worked it out.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer