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

    Unanswered: Show only different Employees

    Hello!

    i have my C# program accessing database with employees and their passcards.

    i have one problem. For example, there are ppl who had received 2 cards within one month. and when i do the Query, which shows ppl who did receive cards within this month... C# program gives an error... "EmployeeID" is constrained to be unique.. value "xxx" is already present..

    so..my query shows employee which received card in this month TWICE.

    here is a code for my query:

    Code:
    SELECT employees.EmployeeID, employees.LastName, employees.FirstName, employees.FathersName, employees.DivisionID, employees.TitleID, 
                   employees.BirthDetails, employees.PassportDetails, employees.Address, employees.PhoneNumber, employees.Details, divisions.DivisionName, 
                   titles.TitleName
    FROM  (((employees LEFT OUTER JOIN
                   cards ON employees.EmployeeID = cards.EmployeeID) LEFT OUTER JOIN
                   divisions ON employees.DivisionID = divisions.DivisionID) LEFT OUTER JOIN
                   titles ON employees.TitleID = titles.TitleID)
    WHERE (Month(cards.CardIssueDate) = ? AND Year(cards.CardIssueDate) = ? )
    i dont want to show one employee twice in my list...even if he received card twice in this month..

    (because my program shows all data regarding him when i click on him in the list)

    attached is my database model..so that u can know better what i want.

    Please could you help me to modify above SQL code, so that it will show only different employees... (so that it wont show one employee twice)

    thank you
    Attached Thumbnails Attached Thumbnails data.gif  

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Do you want to see both (or all) cards?

    Essentially the query is one that gives all CARDS and their matching EMPLOYEE, so you will get the same employee replicated for every card they have.

    My first thought is that you'll need to get your C# program to ignore those duplicates.
    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

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    See how you get on with the below:
    Code:
    SELECT employees.EmployeeID, employees.LastName, employees.FirstName, employees.FathersName, employees.DivisionID, employees.TitleID, 
                   employees.BirthDetails, employees.PassportDetails, employees.Address, employees.PhoneNumber, employees.Details, divisions.DivisionName, 
                   titles.TitleName
    FROM  ((employees LEFT OUTER JOIN
                   divisions ON employees.DivisionID = divisions.DivisionID) LEFT OUTER JOIN
                   titles ON employees.TitleID = titles.TitleID)
    WHERE EXISTS (SELECT NULL FROM cards WHERE cards.CardIssueDate BETWEEN ? AND ? AND cards.EmployeeID = employees.EmployeeID)
    Note - I've changed your code to select dates between two dates. Why?
    1) More flexible
    2) If you have an index on CardIssueDate then it cannot be used if you use the Year and Month functions. If instead you pass the first and last days of the respective month then any index at least has a chance of being used if JET decides it will speed up the query.

    HTH

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yes - and you won't get dupes due to multiple cards neever

  5. #5
    Join Date
    Nov 2007
    Posts
    43
    what i can say... THANK YOU VERY MUCH pootle flump!

  6. #6
    Join Date
    Nov 2007
    Posts
    43
    Hello guys,

    i am trying to print the total records for some period of time for the same database model shown in my first post in this thread. This time, 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

  7. #7
    Join Date
    Nov 2007
    Posts
    43
    can anyone help me out plz?

Posting Permissions

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