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

    Unanswered: Show all records from all tables

    Hello guys,

    i am trying to print the total records for some period of time for the database model shown in the attachment. I dont care if the records duplicate...since i dont do it in C# program, i am doing it in Microsoft Access.

    take a look at my code:
    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 ON employees.EmployeeID = cards.EmployeeID) INNER JOIN (areas INNER JOIN rights ON areas.AreaID = rights.AreaID) ON cards.CardID = rights.CardID) ON titles.TitleID = employees.TitleID
    WHERE cards.CardIssueDate BETWEEN ? AND ?
    ORDER BY employees.EmployeeID;
    problem is... it doesnt show ppl who dont have any areas... For example there are ppl who have access cards, but they dont have any areas assigned to this card. But i want those ppl to be shown in my report also. i tried to change INNER word on LEFT word... but MS Access said that there is error in SQL code..

    please could u help me out to remake this code, so that it will show all the records from database for the specified period of time. (including duplicated records too, i want see everything)

    thanks
    Attached Thumbnails Attached Thumbnails data.gif  

  2. #2
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Simply Change "INNER JOIN" in your code to "LEFT JOIN"
    Cheers

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't know the direct SQL equivalent, but I would take the SQL to the SQL window of a new query and then go to Design view. From there, you should see lines that represent your relationships. If you double click one of those lines, you'll get options.

    For example, double-click the line between CARD and AREA. You get options to include records where both join fields are equal, include all records from AREA and only those from CARD that match, and the one you want, include all records from CARD and only those AREAS that match.

    Then you go back to the SQL view and you can then take that SQL back to code.

    It may be a lot easier to just edit the SQL, but I need to memorise SQL statements more :P

    HTH
    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

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    AH... LEFT JOIN then ^^

    Is there a RIGHT join? I definitely need to do more SQL without the design grid lol
    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

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    AH... LEFT JOIN then ^^

    Is there a RIGHT join? I definitely need to do more SQL without the design grid lol
    Yes there is, and there's lots more too! (e.g. FULL JOIN)
    But Access is funny and doesn't support some of them.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Are they only in SQL Server? Do you mean that the Design grid doesn't support them or the Jet engine itself doesn't support them-- so that if I used FULL JOIN in code, I'd get an error?

    Any recommendations on where I can look to study it? I know I can google it, but there are 1293874987 places to try. Know any good ones?
    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

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    Is there a RIGHT join? I definitely need to do more SQL without the design grid lol
    Don't bother with Access. You'll never really get your head round SQL using Access. I would stick to the design grid and only edit directly to do stuff that the grid won't support (e.g. derived tables). If you work in SQL Server then defo write all your sripts and don't go near the SQL Server designer.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^ Roger that. Makes sense tbh... I think I'd get horribly confused between MySQL, SQLServer and AccessSQL. Two is enough

    However, I'm gonna look over that link George posted ... thanks George!
    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

Posting Permissions

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