Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    43

    Unanswered: Show ALL records

    Hi,

    Please take a look at the attachment of my database model.

    i want to print all the fields of all the tables, which meet the date criteria.. for example only those records from cards table which are within my date search. take a look at my SQL:

    Code:
    SELECT employees.EmployeeID, employees.LastName, employees.FirstName, employees.FathersName, divisions.DivisionName, titles.TitleName, employees.BirthDetails, employees.PassportDetails, employees.Address, employees.PhoneNumber, employees.Details, cards.LetterIN, cards.LetterOUT, cards.CardIssueDate, cards.CardExpireDate, cards.CardDetails, areas.AreaName
    FROM titles INNER JOIN ((divisions INNER JOIN employees ON divisions.DivisionID = 
    
    employees.DivisionID) INNER JOIN (cards INNER JOIN (areas INNER JOIN rights ON areas.AreaID 
    
    = rights.AreaID) ON cards.CardID = rights.CardID) ON employees.EmployeeID = 
    
    cards.EmployeeID) ON titles.TitleID = employees.TitleID
    WHERE EXISTS (SELECT NULL FROM cards WHERE cards.CardIssueDate BETWEEN ? AND ? AND cards.EmployeeID = employees.EmployeeID)
    ORDER BY employees.EmployeeID;
    problem is...ppl who dont have any AreaIDs assigned to their cards do not appear in my report...

    i tried to fix this problem by adding this code to my SQL:
    WHERE EXISTS (SELECT NULL FROM cards WHERE cards.CardIssueDate BETWEEN ? AND ? AND cards.EmployeeID = employees.EmployeeID)

    but it didnt help... can u help me to remake this SQL so that it will show everyone? even those who have cards...but dont have any AreIDs associated with them..

    thanks
    Attached Thumbnails Attached Thumbnails data.gif  

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    INNER joins show only records that match based on the criteria supplied in the ON clause. therefore if there is no areaID, there is no match! Look up LEFT joins and you should be on your way
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Posts
    43
    problem is...MS Access says that there is error when i try to change INNER to LEFT...

    any more ideas?

    thanks in advance

  4. #4
    Join Date
    Nov 2007
    Posts
    43
    I tried LEFT OUTER also, and FULL OUTER.. nothing helped...

    maybe i must reconstruct my SQL for MS Access?

    could u help me with advice please?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well I can't get too specific on what LEFT and RIGHT joins are, because I am too "design grid" oriented :P

    Here is an Access SQL statement which shows all DEBTORs and their matching SUBURBS. Even if there are DEBTORS without a selected SUBURB, the debtor will still show.

    SELECT DebtorID, DEBTOR.SuburbID, SUBURB.SuburbID
    FROM SUBURB RIGHT JOIN DEBTOR ON SUBURB.SuburbID = DEBTOR.SuburbID;


    If "right" doesn't work, try "left" ^^

    Hope that helps
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, howsaboot we try this using the Access GUI.
    When you've selected your tables - it should pull through the relationships (the black lines joining each of the boxes); by default these will be INNER joins.

    Double click the lines and see what types you can change it to; Access will then automatically construct the changes to the SQL.
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2007
    Posts
    43
    i did it...Access says that there are errors... "create SQL statement and add it to existing one" something like that...
    can anyone please show a way of how to view the ppl who dont have any AreIDs associated in Rights table please...

Posting Permissions

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