Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2006
    Posts
    10

    Unanswered: Need Advice on Creating a Database

    I'm a moderate user of databases and MS Access. But I need advice on creating a database.

    My database is going to consist of Universities and each major they offer, with about 100 universities and a more than modest number of majors.

    I have created a table of all the Universities and another of Majors, but now I want to match each University with the majors it offers. Any suggestions on how I should tackle this pickle?

  2. #2
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by EdjamacatedGuy
    I'm a moderate user of databases and MS Access. But I need advice on creating a database.

    My database is going to consist of Universities and each major they offer, with about 100 universities and a more than modest number of majors.

    I have created a table of all the Universities and another of Majors, but now I want to match each University with the majors it offers. Any suggestions on how I should tackle this pickle?

    i don't know how you design your tables relationship. Here is my suggestion:

    University Table
    uni_id (unique)
    uni_name
    uni_address
    etc

    Major Table
    ma_id (unique)
    ma_name
    ma_description
    etc

    Now the table to store all the major in a university, lets called this table: Uni_Major_Offer Table
    table_id (unique)
    uni_id (reference for university table)
    ma_id (reference for major table)

    by using this Uni_Major_Offer Table, you can get the university details, or major details, without worrying of changes in details for the table, because your id will always stay the same

  3. #3
    Join Date
    Jul 2006
    Posts
    10

    Hmm...

    I have 3 Tables

    Universities
    Majors
    Uni_Major

    Universities has a list of all the Universities and University ID
    Majors has a list of all the Majors and Major ID

    Uni_Majo I'm not sure how to build.

    Each Universtiy has MANY Majors and each Major can be offered by MANY universities...How do I build this last table?

  4. #4
    Join Date
    Jun 2006
    Posts
    103
    uni_major table

    uni_major_id (unique, optional to have)
    uni_id (if you decide don't need uni_major_id, then uni_id + major_id will be the unique key for the table)
    major_id

    now what wrong having one uni has many majors? u can have

    uni_major_id uni_id major_id
    1 uni1 major1
    2 uni1 major2
    3 uni1 major3

    for many uni offer same major

    uni_major_id uni_id major_id
    4 uni2 major4
    5 uni3 major4
    6 uni4 major4

    same structure that i told you before, just how you put your record is the point to get the result you want

  5. #5
    Join Date
    Jul 2006
    Posts
    10
    I've got the following right now:

    uni_list:
    -uni_ID (autonumber, PK, 1-65)
    -name
    -city
    -type

    major_list:
    -major_ID(autonumber, PK, 1-950)
    -major_name

    uni_major:
    -uni_ID
    -major_ID

    this table has the form:
    uni_1:maj_1
    uni_1:maj_2
    uni_1:maj_3
    uni_2:maj_2
    uni_2:maj_7

    and so on..

    Matching fields are connected in the relationships section, where uni_list and major_list are connected via uni_major.

    I created a query to test, selecting * from uni_list, major_name from major_list, and the criteria 59 from uni_major. But it gave me a Type mismatch error. Why..?
    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select U.uni_id
         , U.name
         , U.city
         , U.type
      from uni_major as UM
    inner
      join uni_list as U
        on U.uni_id = UM.uni_id  
     where UM.major_id = 59
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by EdjamacatedGuy
    I've got the following right now:

    uni_list:
    -uni_ID (autonumber, PK, 1-65)
    -name
    -city
    -type

    major_list:
    -major_ID(autonumber, PK, 1-950)
    -major_name

    uni_major:
    -uni_ID
    -major_ID

    this table has the form:
    uni_1:maj_1
    uni_1:maj_2
    uni_1:maj_3
    uni_2:maj_2
    uni_2:maj_7

    and so on..

    Matching fields are connected in the relationships section, where uni_list and major_list are connected via uni_major.

    I created a query to test, selecting * from uni_list, major_name from major_list, and the criteria 59 from uni_major. But it gave me a Type mismatch error. Why..?
    Thanks

    did you use 'Create query in Design View' if so, can you show us the SQL statement? by looking as your explaination, is hard to see why you have such error. like r937 said, you need a inner join to do the query, if you are using create query in design view, by adding the tables inside the view, access should do an inner join for you automatically.

  8. #8
    Join Date
    Jul 2006
    Posts
    10
    this is my SQL statement.


    SELECT uni_list.*, major_list.Major, uni_major.major_id

    FROM (major_list INNER JOIN uni_list ON major_list.ID = uni_list.ID) INNER JOIN uni_major ON (uni_list.ID = uni_major.uni_id) AND (major_list.Major = uni_major.major_id)

    WHERE (((uni_major.major_id)=59));


    The purpose of this query is to find All Universities with major ID 59.

    tables are as such:

    major_list:
    -id (autonumber)
    -major (text)

    uni_list:
    -id (autonumber)
    -name (text)
    -city (text)
    -status (text)
    -two_or_four (int)

    uni_major:
    -uni_id (int)
    -major_id (int)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, that's not right, you cannot join major_list to uni_list on major_list.ID = uni_list.ID

    do you see why not?

    just look at a few rows of major_list and then look at a few rows of uni_list and then see whether matching major_list.ID = uni_list.ID makes sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2006
    Posts
    10
    you're right, i found a mistake in the relationships and fixed it, my new SQL statement is as follows, it runs, but it finds no results are found for some reason..

    SELECT uni_list.*, uni_major.major_id, major_list.major

    FROM major_list INNER JOIN (uni_list INNER JOIN uni_major ON uni_list.id = uni_major.uni_id) ON (uni_list.id = major_list.id) AND (major_list.id = uni_major.major_id)

    WHERE (((uni_major.major_id)=59));

  11. #11
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by EdjamacatedGuy
    you're right, i found a mistake in the relationships and fixed it, my new SQL statement is as follows, it runs, but it finds no results are found for some reason..

    SELECT uni_list.*, uni_major.major_id, major_list.major

    FROM major_list INNER JOIN (uni_list INNER JOIN uni_major ON uni_list.id = uni_major.uni_id) ON (uni_list.id = major_list.id) AND (major_list.id = uni_major.major_id)

    WHERE (((uni_major.major_id)=59));
    before you do a where clause, first make sure your query works.

    This is my query SQL statement:

    SELECT uni_list.*, uni_major.major_id, major_list.major
    FROM (uni_major INNER JOIN uni_list ON uni_major.uni_id = uni_list.id) INNER JOIN major_list ON uni_major.major_id = major_list.id;

    when you add the table, the inner join is already been done by the access database, all you need to do is just select those fields you wanted, and in what critiria, first, dont do any critiria first. i saw your SQL statement, you are not suppose to use and AND to join 3 tables, the theory goes... for eg. u have A,B,C tables, that you want to join, first you join A+B form D, then you use D+C to get the final table you want.

    By doing (uni_major INNER JOIN uni_list ON uni_major.uni_id = uni_list.id), i join uni_major and uni_list form a new table, then i use this table do an inner join with INNER JOIN major_list ON uni_major.major_id = major_list.id

    i hope you can understand.

    i attached a copy of the database i created, including a working inner join query, then just insert your records and add some more fields for the uni table, then you will get what you want.
    Attached Files Attached Files
    Last edited by mkggoh; 07-24-06 at 07:38.

  12. #12
    Join Date
    Jul 2006
    Posts
    10
    dog gone it mk you got it. It's my damn relationships.

    When I opened the query you made in the design view, the relationships shown there looked exactly like the relationships I had made using the relationship manager.

    But when I opened the query I had made, Access for some reason showed a line connected the ID field in major_list to the ID in uni_list. When infact they're not related. Why did access make this alteration? Is it because of the similar field names and values? Because they're both autonumbers.

    I see that you made an ID1 field in the uni_list, what was the purpose of that? (By the way, I made the changes to my DB and it works A-OK).

  13. #13
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by EdjamacatedGuy
    I see that you made an ID1 field in the uni_list, what was the purpose of that? (By the way, I made the changes to my DB and it works A-OK).
    nah...they are the same, just leave the auto number id in the table, and remove the second id, i did that because i followed the table details you given, focusing on copying but lack of thinking (u know, swapping view of window, ensuring i have copied the right details, lack of thinking, haha...), i tried not to use too much time on creating a sample database. Just remove the second id, and relink all the key link to the first id, they should work the same way.

    Have Fun

  14. #14
    Join Date
    Jun 2006
    Posts
    103
    [QUOTE=EdjamacatedGuy]dog gone it mk you got it. It's my damn relationships.

    When I opened the query you made in the design view, the relationships shown there looked exactly like the relationships I had made using the relationship manager.

    But when I opened the query I had made, Access for some reason showed a line connected the ID field in major_list to the ID in uni_list. When infact they're not related. Why did access make this alteration? Is it because of the similar field names and values? Because they're both autonumbers.

    QUOTE]

    Forgot to answer the other question....
    I am not exactly sure how your created your query, but by looking at the SQL statement is surely not right

    SELECT uni_list.*, uni_major.major_id, major_list.major

    FROM major_list INNER JOIN (uni_list INNER JOIN uni_major ON uni_list.id = uni_major.uni_id) ON (uni_list.id = major_list.id) AND (major_list.id = uni_major.major_id)

    WHERE (((uni_major.major_id)=59));
    look at the inner join code

    INNER JOIN (uni_list INNER JOIN uni_major ON uni_list.id = uni_major.uni_id) ON (uni_list.id = major_list.id) AND (major_list.id = uni_major.major_id)


    this bit
    (uni_list.id = major_list.id) AND (major_list.id = uni_major.major_id)
    it means uni_list.id = major_list.id, major_list.id = uni_major.major_id

    they all has to be the same, not sure what the result is, but if they are all the same, ie uni_list.id =major_list.id=uni_major.major_id=1 then your query will have this record(u can give it a try, to set the ids to be the same), i am sure this is not what you want (i think this is the reason why you have 0 record, but the query seems to be working)
    Last edited by mkggoh; 07-24-06 at 22:37.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you guys still struggling over how to write the query?

    based on the design and the question in post #5, the query in post #6 does what you want

    if you also want to join the third table in, i could write that query for you too

    that design in post #5 is just fine, and the relationships are obvious, as are the columns to join on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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