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:
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..
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.
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...