Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: Database mutliple tables join to one

    I have 20+ tables all have a PERSONALID field that relates to a PERSONALID field in a table called personal which holds all info about the person. I want to Join all the tables into one big table. I was thinking ths would work fine using the Access querydesign but i get an error saying query too complex, so i've tried just to do like 5 at a time but it's cutting the records to only show the persons who are in all 5 tables, but i want it to show all the records in the personal table then add those new fields from the other 4 tables to it.

    for example. I have the 'personal' table which holds all personal information and the personalid number.

    then and 'agency' table, which just tells if the person is in an agency buy a yes or no check box.

    then one called 'alzheimer' which is a group and you can be in the group and a leader/spokesman.

    then 'attorneys'
    then 'awards'

    these are just the 5 of many.

    Is this Join possible?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try changing "INNER" to "LEFT" in the SQL, presuming that the personal table is the first listed.
    Paul

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    SELECT personal.PERSONALID, personal.[FIRST NAME], personal.[LAST NAME], personal.[E MAIL ADDRESS], personal.BIRTHDAY, agency.*, alzheimerprogram.*, attorneys.*, awards.*
    FROM (((personal INNER JOIN agency ON personal.PERSONALID = agency.PERSONALID) INNER JOIN alzheimerprogram ON personal.PERSONALID = alzheimerprogram.PERSONALID) LEFT JOIN attorneys ON personal.PERSONALID = attorneys.PERSONALID) LEFT JOIN awards ON personal.PERSONALID = awards.PERSONALID;



    This is the SQl view. If you need anymore information about this ask i'll be on this forum checking all day cause my deadline is very soon. Thank you

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is there a follow-up question there? You haven't changed them all, so I wouldn't expect it to be working yet.
    Paul

  5. #5
    Join Date
    Jul 2009
    Posts
    7
    ok that seemed simple : ) Thank you pbaldy. You are my Hero. I'm thinking this will work for all the tables if there wasn't a "query too complex error". Is there a way to get around that?

  6. #6
    Join Date
    Jul 2009
    Posts
    7
    SELECT personal.PERSONALID, personal.SALUTATION, personal.[FIRST NAME], personal.[LAST NAME], personal.BusinessName, personal.[ADDRESS 1], personal.CITY, personal.STATE, personal.ZIP, personal.[HOME PHONE], personal.[WORK PHONE], personal.[CELL PHONE], personal.[FAX OR BEEPER], personal.[E MAIL ADDRESS], personal.BIRTHDAY, personal.NOTES, agency.*, alzheimerprogram.*, attorneys.*, awards.*, boardofdirectors.*, bullyingprevention.*, businessandindustry.*, celebration.*, ceuprovider.*, churches.*, clubsorganizations.*, depressionsupport.*, friendshiphouse.*, futureseminars.*, generalmailings.*, Government.*, lawenforcement.*, longtermcarefacilities.*, maymentalhealthmonth.*, MCSAN.*, Media.*, medical.*, memberships.*, menwhocook.*, operationsantaclaus.*, raperesponseadvocacyprogram.*, schools.*, summercamp.*, thumbody.*, walkathon.*, wallofremembrance.*
    FROM childsafetyconference, schools, (((((((((((((((((((((((((((((personal LEFT JOIN agency ON personal.PERSONALID = agency.PERSONALID) LEFT JOIN alzheimerprogram ON personal.PERSONALID = alzheimerprogram.PERSONALID) LEFT JOIN attorneys ON personal.PERSONALID = attorneys.PERSONALID) LEFT JOIN awards ON personal.PERSONALID = awards.PERSONALID) LEFT JOIN boardofdirectors ON personal.PERSONALID = boardofdirectors.PERSONALID) LEFT JOIN bullyingprevention ON personal.PERSONALID = bullyingprevention.PERSONALID) LEFT JOIN businessandindustry ON personal.PERSONALID = businessandindustry.PERSONALID) LEFT JOIN celebration ON personal.PERSONALID = celebration.PERSONALID) LEFT JOIN ceuprovider ON personal.PERSONALID = ceuprovider.PERSONALID) LEFT JOIN churches ON personal.PERSONALID = churches.PERSONALID) LEFT JOIN clubsorganizations ON personal.PERSONALID = clubsorganizations.PERSONALID) LEFT JOIN depressionsupport ON personal.PERSONALID = depressionsupport.PERSONALID) LEFT JOIN friendshiphouse ON personal.PERSONALID = friendshiphouse.PERSONALID) LEFT JOIN futureseminars ON personal.PERSONALID = futureseminars.PERSONALID) LEFT JOIN generalmailings ON personal.PERSONALID = generalmailings.PERSONALID) LEFT JOIN Government ON personal.PERSONALID = Government.PERSONALID) LEFT JOIN lawenforcement ON personal.PERSONALID = lawenforcement.PERSONALID) LEFT JOIN longtermcarefacilities ON personal.PERSONALID = longtermcarefacilities.PERSONALID) LEFT JOIN maymentalhealthmonth ON personal.PERSONALID = maymentalhealthmonth.PERSONALID) LEFT JOIN MCSAN ON personal.PERSONALID = MCSAN.PERSONALID) LEFT JOIN Media ON personal.PERSONALID = Media.PERSONALID) LEFT JOIN medical ON personal.PERSONALID = medical.PERSONALID) LEFT JOIN memberships ON personal.PERSONALID = memberships.PERSONALID) LEFT JOIN menwhocook ON personal.PERSONALID = menwhocook.PERSONALID) LEFT JOIN operationsantaclaus ON personal.PERSONALID = operationsantaclaus.PERSONALID) LEFT JOIN raperesponseadvocacyprogram ON personal.PERSONALID = raperesponseadvocacyprogram.PERSONALID) LEFT JOIN summercamp ON personal.PERSONALID = summercamp.PERSONALID) LEFT JOIN thumbody ON personal.PERSONALID = thumbody.PERSONALID) LEFT JOIN walkathon ON personal.PERSONALID = walkathon.PERSONALID) LEFT JOIN wallofremembrance ON personal.PERSONALID = wallofremembrance.PERSONALDID;



    Here is all the tables, I tried it and it said 'join expression not supported'. WHat's up with this?

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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