Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012
    Posts
    21

    Unanswered: Basic relationship

    Hi I have 2 tables:

    tbl_Inspection
    InpectionID (PK)
    ApprovedBy
    CheckedBy


    tbl_Empolyer
    Empolyer_ID
    Name



    In the inspection table different employer ID's are inserted into approved by and checked by.

    I want to run a query which will show the employer name instead of the ID.

    How would I do this? Thanks for the help

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    with two joins
    presuming you are using MS Access...
    ..do you want the question moving there?
    you should be able to do this in the query designer. its fairly straightforward assuming you have already defined the relationships between employer (presumaby thsat should real tbl_employee not employer) and inspection

    if you havent' then that's be the first place to start... make certain the datatypes of approvedby, checkedby and employer_id are the same type.
    then open up the realtionships window, stick inspection table on the window then add the employee table twice
    click the employer table, select the employer_id then drag to the inspection table and specify the other column is approvedby, tick enforce RI

    repeat the process with checked by (you will need to use the other tbl_employer table

    note if you haven't already used RI you ma have problems enforcing RI. dont' eb temtped to ignore the message. fix the errors (every value in inspection (for both approved and checked MUST have a matching value in emploer..

    get your terminology right
    get your table & column names right
    don't repeat the table name in the column name.. its redundant. ferinstance you refer to employer_id in table employer... instead use ID, drop the employer bit
    no need to prefix the table name with tbl, although I recognise that is a sort of Access standard
    you mix CamelCase and underscores, use one or t'other but not both. ISO SQL tends to favour all lowercase and underscore eg approved_by others prefer CamelCase eg:- ApprovedBy.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2012
    Posts
    21
    Hi Healdem,

    Thanks for you help, my problem was I didn't realise you could add the same table twice in the relationship manager and the queries. I was trying to create the multiple relationships from the one table. Lesson learned, thank you very much.

    Yes, this was done in access, feel free to move the thread.

    I will have a read over the ISO SQL to get my naming conventions correct, I know my example was a bit sloppy, I was just using it as an example.

    Thanks again for your help, very useful.

Posting Permissions

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