Unanswered: text box to show text related to fk number field
greetings, i have an access 2007 db of contact info for a church board. a good number of members of the board are married to each other (spouses).
i have three tables:
list of services the members are in charge of.
in the names table is a foreign key field that hold the record id of another record in the names table. that record is the spouse. so, sally jones has a record and one field of that record is her husband's name because he is a board member and a spouse.
so in the data input form i use a combo box to look up the names in the names table and put the name id in the spouseFK field of the names table.
I need to get that info back out. specifically right now i am trying to put the spouses name in a report of the contact info for each board member. I cannot figure out how to do it.
hope this is clearer than mud. thanks in advance.
re: text box to show text related to fk number field
sinndho, thank you very much. your sql code "The whole list associating married people with:" looks like exactly what I am looking for. I have tried some of the other pieces of sql code in a query and they work fine. But I do not know what "a" or "b" stand for in the sql code.
my table specific sql:
SELECT * FROM
(SELECT [firstName], [lastName] FROM tblBoardMember) AS a
(SELECT [firstName], [lastName] FROM tblBoardMember) AS b
ON a.memberID = b.spouseFK;
a and b are aliases. As we use the same table twice, we must find a way to specify from which table come the columns used to create the relationship in the join (the ON... part). So we have a first subquery that selects firstName and lastName from tblBoardMember that we name a (that's the AS a after the first closing parenthese) and a second subquery that's the same as the first but that we name b (AS b).
Should you decide to explicitly name the columns in the SELECT part instead of using the *, you would use:
SELECT a.firstName, a.lastName, b.firstName, b.lastName
Once you have the query, you can build a report based on it:
1. Don't use SELECT *, use the names of the columns (see my former post).
2. Build the query or have the assistant (wizard) to build it for you.