Thank you zmbd for your support,
I will try to be as clear as i can .
I have 5 tables:
Employee (Table)
EmployeeID (PK)
EmployeeName (Text)
-------------------
Season (Table)
SeasonID(PK)
Aseason(Integer) (e.g 2012,2013)
-------------------------------
LeaveType (Table)
LeaveID(PK)
LeaveType (Text) (e.g Annual,Sick,Bonus)
---------------------------------------
AnnualLeave (Table)
AL (PK)
SeasonID(FK)
EmployeeID (FK)
Licenses (Integer) (e.g Initial license for each employee each season)
---------------------------------------------------------
Main (Table)
MainID (Integer)
LeaveID(FK)
EmployeeID (FK)
Start Date (Date Type)
End Date (Date Type)
Duration (Integer)
SeasonID (FK)
------------------------------
This is all my table with the relationship .
The joins are getting to complicated to handle it manual at least for me as i need nested joins to extract or have the appropriate information.
Basicly i will have a form that user will search by Aseason , that is the specific year.
Then it will open a report from crosstab query of course and he can see the appropriate data/table that he wants.
For 2012 it looks good , how ever because of the many joins, i made a sample data for 2013 but it seams joins are not good joined together as when 2013 starts i want in the report to be shown all the employees even if their still not related to any annual leave lisence .
So as i said iv insert in the AnnualLeave table the initial license of all employes for 2013.
Then i have sumbit for 3 different employes some annual leaves and with the join it shows me only the employees that they have already toke some annual leaves .
Query should be shown all the employees even if they do not yet have been sumbited to any annual leave licenses .
-
TRANSFORM Nz(Sum(Main.Duration))+0 AS SumOfDuration
-
SELECT Employee.EmployeeName, AnnualLeave.Licenses, Main.SeasonID
-
FROM Season INNER JOIN (LeaveType INNER JOIN ((Employee RIGHT JOIN AnnualLeave
-
ON Employee.EmployeeID = AnnualLeave.EmployeeID)
-
INNER JOIN Main
-
ON Employee.EmployeeID = Main.EmployeeID)
-
ON LeaveType.LeaveID = Main.LeaveID)
-
ON (Season.SeasonID = Main.SeasonID)
-
AND (Season.SeasonID = AnnualLeave.SeasonID)
-
WHERE (((AnnualLeave.SeasonID)=2 Or (AnnualLeave.SeasonID) Is Null))
-
GROUP BY Employee.EmployeeName, AnnualLeave.Licenses, Main.SeasonID
-
PIVOT LeaveType.LeaveType;
-
Heres one more my query this time with no paremeters as i know how to handle now the parameters but my issue are the joins
In summary , i think i need a left join for employee table but basicly employees connects to 2 tables.
One with Main and another with AnnualLeave
As also all the leavetype.Leavetype to be shown even if employees have not yet got one as my report have functions based on specific leavetypes and when a leavetype is not shown then the function wont work and then the report cannot open