Difficulty creating a Form

• rayzor

  SSC Journeyman

  Points: 85

  More actions

  November 4, 2009 at 2:08 pm

  #73007

  Very new to Access.

  I have 3 different tables in MS Access - Each table has the same 2 fields (JobID and JobName) I want my form to pull all the data that each table has based on the JobName and/or JobID. I realize there's probably a lot to making this happen but if someone can point me in the right direction. I found the MS help on table relationships to be not enuf. Any help would be great.

• DougGifford

  SSCommitted

  Points: 1815

  More actions

  November 6, 2009 at 11:08 am

  #1076044

  Here are three different ways you may resolve your issue.

  1) Try creating a view (query) that joins the three tables, like:

  Select t1.column1,t1.column2,t1.column3,t2.column1,t2.column2,t2.column3,t3.column1,t3.column3, etc listing the columns you want to show

  From Table1 as t1 Join Table2 as t2 on

  t1.JobID = t2.JobID and t1.JobName = t2.JobName

  Join Table3 as t3 on

  t3.JobID = t2.JobID and t3.JobName = t2.JobName

  Now create a form with the view (query) as the data source.

  2) Another way is to create a Union query and make a for with the data source using the Union query.

  (This depends on your tables layouts and if you will be updating, changing any data.)

  3) I would probably do this: create a form for Table1

  then create a subform within the main form for Table2 Linked to Table1 JobID

  then create a second subform within the main form for Table3 linked to Table1 JobID

  There are lots of ways you can approach this.

  It depends on if you are planning to update the data using your form or just look at the data.

  I would suggest looking up subforms. Lots of info on using subforms online.

  Good luck.

  "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
  "Robert A. Heinlein"