#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2010
    Posts
    35
    Rep Power
    15

    Question Access relational database question?


    Hello,

    I started a new job and an learning Access 2003 on the fly. i know its old and ugly : )

    Everything seems pretty simple and strait forward. I am having a bit of trouble with relational databases and the join properties.

    I set up two simple tables and created a relationship between them. I used the 3rd join type. Is there any simple query or test I can use to make sure everything is working properly?

    Sorry if I posted this in the wrong section. Any help would be greatly appreciated! Thanks

    omgjtt
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2010
    Posts
    35
    Rep Power
    15
    Also, is a foreign key the primary key in the other table? Sorry if I am wording this stuff in an odd way but this is all kind of new to me.

    Thanks
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by omgjtt
    Is there any simple query or test I can use to make sure everything is working properly?
    the best way is to populate the tables with live data, and build a query on them

    in query design view, select the related tables one after the other, and access should build the join automatically for you based on the relationship

    Originally Posted by omgjtt
    Also, is a foreign key the primary key in the other table?
    yup, it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2010
    Posts
    35
    Rep Power
    15
    Thanks r937,

    I have data entered in both tables. I saw an example on a website that talked about two tables. One had a list of classes (tbl_classes) and the other table had teachers (tbl_teachers).

    The tbl_teachers table has a primary key for the teach I.D.

    THe tbl_classes table has a primary key called class I.D.

    I have the teacher I.D listed in a field on the tbl_classes and that is my relationship between the two tables.

    I am having a bit of a hard time wrapping my head around the join properties.

    Some of the classes had no teacher. I ran a query to find all classes that do not have a teacher. If the query ran without any error messages does that (sort of) validate my relationship and join type?

    It ran and returned my results of classes without teacher.

    Also thank you for moving this to the right place. Thanks for the help. I am learning and want to make sure I am not getting a poor foundation.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    looks like you've done everything correctly

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2010
    Posts
    35
    Rep Power
    15
    Thanks again,

    One quick question. I fell pretty good about Access. Seem like it holds your hand. I am building queries in design view and looking at the SQL code. Access seems really redundant.

    Here is the query I made in design view

    Code:
    SELECT tbl_ClassList.[CLASS NAME], tbl_ClassList.DAY, tbl_ClassList.TIME
    FROM tbl_ClassList
    WHERE (((tbl_ClassList.[CLASS ID])=4));
    I am watching training videos on Lynda about just SQL
    it was written this way.

    Code:
    Select [CLASS NAME], [DAY], [TIME] FROM tbl_ClassList WHERE DAY='mon';
    It seems so much easier to list the rows and then cite the table. Thanks for all you help. I am going to keep watching videos and hopefully it will click sooner than later : )

    This stuff is really fun.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2010
    Posts
    35
    Rep Power
    15
    Just found out that each DBMS has slightly vendor specific SQL. Thank you for all your help.

IMN logo majestic logo threadwatch logo seochat tools logo