Displaying info from an underlying table in my form

[mihndog]

I am an access newb- hopefully someone can understand my description of my problem well enough to give me some direction- in short...please, help me!!!

I have 3 tables related as follows:

contactTBL one-many sourcematchTBL
sourceTBL one-many sourcematchTBL

each contact can have multiple sources so I set up a "linking" table sourcematchTBL which only contains numeric data- the contactID and the sourceID, and a primary key (autonumb) for anything I may want to do later. That makes sense, right? OK, well that all seems to work great but here is the rub...

I have a form which needs to display all of the sources for each customer along with other customer info- so I have a subform to display the sourceID field from the sourcematchTBL using the contactID as the Master/Child linkage but I can only get it to display the numeric data stored in the sourcematchTBL not the actual sourcename from the related sourceTBL-

can someone point me in the right direction here? Do I have things set up right to accomplish this task or do I need to modify my table structure/relationships? Thanx in advance....

 

[charityg]

The recordsource for your subform should be a query joining the ContactID from the sourcematch table with the source information from the source table.

Such a query's SQL statement would look like:

SELECT sourcematchTBL.ContactID, sourceTBL.*
FROM sourceTBL RIGHT JOIN sourcematchTBL ON sourceTBL.SourceID = sourcematchTBL.SourceID;


Keep your master/child links the way you have them.

 

[mihndog]

so my underlying structure is ok- I just need the right SQL statement to display the text field [SourceTBL].[SourceName] in the form? Let me give it a whirl and see if I have it right..... Thanx.