Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    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:
    names, etc.
    addresses
    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.
    Scott

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If I correctly understand what you mean, you can:
    - Retrieve married people with:
    Code:
    SELECT * FROM Tbl_Names WHERE Foreign_Key IS NOT Null;
    - Retrieve unmarried people with:
    Code:
    SELECT * FROM Tbl_Names WHERE Foreign_Key IS Null;
    - Retrieve a spouse with:
    Code:
    SELECT * FROM Tbl_Names WHERE Primary_Key = Some_Foreign_Key_Value;
    Or:
    Code:
    SELECT * FROM Tbl_Names WHERE Foreign_Key  = Some_Primary_Key_Value;
    - The whole list associating married people with:
    Code:
    SELECT * FROM
        (SELECT Col1, Col2, ..., Colx FROM Tbl_Name) AS a 
    LEFT JOIN
        (SELECT Col1, Col2, ..., Colx FROM Tbl_Name) AS b
    ON a.Primary_Key = b.Foreign_Key;
    Note: Access will probably change the syntax of the last one.
    Have a nice day!

  3. #3
    Join Date
    Mar 2013
    Posts
    70

    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:
    Code:
    SELECT * FROM
        (SELECT [firstName], [lastName] FROM tblBoardMember) AS a 
    LEFT JOIN
        (SELECT [firstName], [lastName] FROM tblBoardMember) AS b
    ON a.memberID = b.spouseFK;
    I really appreciate the help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    Code:
    SELECT a.firstName, a.lastName, b.firstName, b.lastName
    FROM ...
    Have a nice day!

  5. #5
    Join Date
    Mar 2013
    Posts
    70

    text box to show text related to fk number field

    aha. i got the sql to work in a query. what i need to do in the end is to put a field on a report that lists the spouses.any thoughts? thanks.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •