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