Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2007
    Posts
    17

    Unanswered: More Database Help Required.

    Thank you for answering all the questions I have so far. I have a feeling when all is said and done, I can compile a small how-to!

    Here is my latest:

    I re-worked all of the database relations so now everything is related off of a number to a record vice a last name and first name, etc. Number to Number relations only. I think that is step 1 in normalizing the database.

    Now here is my query, that returns no results, even though there should be!

    Code:
    SELECT Reservists.[Last Name ], Reservists.[First Name], Reservists.Rank, [Reservist Assignments].Assignment, [Reservist Assignments].Location, [Reservist Assignments].[Start Date], [Reservist Assignments].[End Date]
    FROM Reservists INNER JOIN ([Order Types] INNER JOIN ([N5 Officers] INNER JOIN [Reservist Assignments] ON [N5 Officers].ID = [Reservist Assignments].[N5 Desk Officer]) ON [Order Types].ID = [Reservist Assignments].[Type of Orders]) ON Reservists.ID = [Reservist Assignments].[Assigned Reservist]
    WHERE ((([Reservist Assignments].[Start Date])<Date()) AND (([Reservist Assignments].[End Date])>Date()));
    i have attached two JPG files, one being the overall datbase relationships I set up. The other is the relationship view of the query.
    Attached Thumbnails Attached Thumbnails relationships.JPG   relationships2.JPG  

  2. #2
    Join Date
    Dec 2007
    Posts
    17
    Next Question: A seperate Query is not showing all of the records! Here is the code:

    Code:
    SELECT Countries.Country, Countries.[Language 1], [N5 Officers].[N5 Desk Officer], [N5 Officers].[E-mail], [N5 Officers].Phone, Regions.Region, [Reserve Units].[Reserve Unit], Countries.[Language 2], Countries.[Language 3]
    FROM [Reserve Units] INNER JOIN (Regions INNER JOIN ([N5 Officers] INNER JOIN Countries ON [N5 Officers].ID=Countries.[N5 Desk Officer]) ON Regions.ID=Countries.Region) ON [Reserve Units].ID=Countries.[Reserve Unit];
    Bascially, if the Reserve Unit Field is empty, it doesnt show any of that countries info.

    EDIT: I changed the JOIN in the relationship type to all 3 options, and the results are still the same.
    Last edited by Jasen Hicks; 12-06-07 at 08:52.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Countries.Country
         , Countries.[Language 1]
         , [N5 Officers].[N5 Desk Officer]
         , [N5 Officers].[E-mail]
         , [N5 Officers].Phone
         , Regions.Region
         , [Reserve Units].[Reserve Unit]
         , Countries.[Language 2]
         , Countries.[Language 3]
    FROM   [Reserve Units]
     LEFT
      JOIN Countries
        ON [Reserve Units].ID = Countries.[Reserve Unit]
     INNER
      JOIN Regions
        ON Regions.ID = Countries.Region
     INNER
      JOIN [N5 Officers]
        ON [N5 Officers].ID = Countries.[N5 Desk Officer]
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's going to have the same unfortunate results, george

    you cannot follow a LEFT JOIN with an INNER JOIN and still retain the left-outer-ness

    also, you forgot the nested parentheses needed for ugly stepchild msaccess
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question

    Quote Originally Posted by r937
    that's going to have the same unfortunate results, george

    you cannot follow a LEFT JOIN with an INNER JOIN and still retain the left-outer-ness

    also, you forgot the nested parentheses needed for ugly stepchild msaccess
    Just for the sake of knowledge, what exactly is a Left Join, Outer Join, Inner Join? I see you guys do this often but have no idea what it really means?

    thanks,
    Bud

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    that's going to have the same unfortunate results, george

    you cannot follow a LEFT JOIN with an INNER JOIN and still retain the left-outer-ness

    also, you forgot the nested parentheses needed for ugly stepchild msaccess
    Ugh, quite right.
    I tend to trip up on this same problem often

    And if you slapped that into Access I'm sure it would parenthesise the crap out of it for you


    For the OP: I stumbled across this link the other day which I feel does a good job of explaining different joins.
    http://threenineconsulting.com/forum..._2D00_SQL.aspx
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    For the OP: I stumbled across this link the other day which I feel does a good job of explaining different joins.
    http://threenineconsulting.com/forum..._2D00_SQL.aspx
    great reference for those using SQL server in Access, however for those beleagured the swamps of JET/default Access

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    And if you slapped that into Access I'm sure it would parenthesise the crap out of it for you
    No - the smegger can't parse it and will throw up errors. The main reason I don't answer any Access SQL questions that require several joins

  9. #9
    Join Date
    Dec 2007
    Posts
    17
    So, back to my question.... any one? :-D

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - read your original post. I don't know what has gone on before but:
    Quote Originally Posted by Jasen Hicks
    I re-worked all of the database relations so now everything is related off of a number to a record vice a last name and first name, etc. Number to Number relations only. I think that is step 1 in normalizing the database.
    I'm afraid that is not step one:
    http://www.tonymarston.net/php-mysql...se-design.html

    Also - relation <> relationship They are two totally different things, often confused.

    Finally - in English what is your query actually meant to do? Without knowing that it is rather hard to correct it.

  11. #11
    Join Date
    Dec 2007
    Posts
    17
    OK! Now were cooking....

    Here is what I want to do... I have a record of data that stores a countries information, who the officer is in charge of it, what languages are spoken there, what region it is in, what continent it is on, etc.

    In the Country Database I store text data for the name of the country, and the languages it speaks.

    In the same databse (the country database) I store a number for the officer in charge of it, the unit attached to it, the region it is in, and the continent it is on. These numbers are the id numbers to other tables that keep the text version (in case i need to change the country name, or the officer in charge, etc easily)

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry Jason - that tells me what is in your database but not what you want your query to do. Imagine we are in a business. Before you started thinking about SQL someone would have told you their business requirement. "I need to the top 10 performing sales people in the first 2 quarrters of this year". That sort of thing. What would the business requirement be for your query?

  13. #13
    Join Date
    Dec 2007
    Posts
    17
    Ok, i want a Report for a country: information with what languages it speaks, what officer is in charge of it, thier contact info, what continent its on, and what region it lies within.

Posting Permissions

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