CrossTab Query - Fields should be shown with out anyone being assigned

[Andreasmis]

Hello,
I have create some crosstab querys and basically now i have an issue , i am not sure if its a "logical" issue.
First of all here is the SQL :

TRANSFORM 
   Nz(Sum(Duration),0) AS SumOfDuration
   SELECT 
      Employee.EmployeeName, 
      AnnualLeave.Licenses
   FROM Season 
      INNER JOIN (LeaveType 
         INNER JOIN ((Employee INNER JOIN Main 
            ON Employee.EmployeeID = Main.EmployeeID) 
               INNER JOIN AnnualLeave 
                  ON Employee.EmployeeID =
                      AnnualLeave.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
    PIVOT LeaveType.LeaveType In
        ("Annual", "Personal", "Sick", "Bonus");

The query is working fine but the problem i think its on the joins.
I want the query to show me all employeenames that are assigned to the table AnnualTypes (basically there is employeeid but they have a relationship) even if they are not assigned to any LeaveType.Leave Type.
I have made a combination of querys as i have 5 tables and the joins are getting to complex but i still cant figure it out .
But , again , i am not sure if its the join or something else .
Here is a screenshot of my tables and relations:

http://www.2shared.com/photo/BWhzu9Iu/sample.html

[Rabbit]

Use one of the outer joins instead of the inner. Either left outer join or right outer join. Those joins allow for returning all data from one table if it doesn't exist in the joined table.

[Andreasmis]

Should i do it mannualy or from the schema that access gives me ?
As i am trying from the schema it always says some warnings/errors such as it contains ambigious outer joins etc etc

[zmbd]

Andreasmis:
Let's take a look at your five tables.... just the related fields and the information you're looking at:

I am not able to open the picture you've posted... has to do with the filters inplace by my IT...

Would you mind listing tables using something like:

Name: tbl_employee
[employee_pk] autnumber primarykey
[employee_id] number long
[employee_Fname] text(50)
[employee_Lname] text(50)

Name: tbl_leavetype
[leavetype_pk] autnumber primarykey
[leavetype_name] text(50), no duplicates, no nulls, required

Name: tbl_annualleave
[annualleave_pk] autnumber primarykey
[FK_employee] numeric-long, 1:M, FK from tbl_employee
[FK_leavetype] numeric-long, 1:M, FK from tbl_leavetype
[annualleave_com ment] text(255)

etc...

IMHO, Rabbit can make magic happen if the right information is available. From what I'm reading is that your transform query is based on queries that are based on yet more queries and so forth; thus, I don't think the relevant data is there yet and is the root cause of your ambigious outer join error.

[Andreasmis]

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,Bon us)
---------------------------------------

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.Leave type 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

[zmbd]

One Bite At A Time

As the title says, I tend to take these one bite/step at a time. In your last post you say:

I want the query to show me all employeenames that are assigned to the table AnnualTypes (basically there is employeeid but they have a relationship) even if they are not assigned to any LeaveType.Leave Type.

This is a left join between the [Employee] table and [AnnualLeave] table on the employee keys. In the fields drag down the information from the tables that you want to see... at least the [employee]![EmployeeID] and [annualleave]![Licenses]. This will return every employee id and either a blank for [annualleave]![Licenses] or the value. You can add the other fields and conditions as needed for your work.

You should be able to poke this into a crosstab query if you need pretty reports.

SO let's see if this returns the desired records, and then we can go to the next step. (I will warn you now... cross tabs are my weakest Access skill... I simply don't need them most of the time and the wizard makes the simple ones for me)

[Andreasmis]

Well i know that is about left join , the issue that is i cant manage it to a left join , when i am trying to have a left join on employee and annualleave it warns me about those ambigious outer joins etc

[zmbd]

For right now, ignore your current query.

In a new query
Show the [Employee] table and [AnnualLeave] table
- the join might be automatically made for you on the employee id fields, if so, then right click the join and change the type so as to show all records from [Employee] table. If the join isn't made for you then create one between the two employee id fields.

Working directly with the tables should not return any errors, if it does, AND the join was made for you, then delete it and make it by hand... there was a bug on these in the older software; however, I thought that was fixed a long time ago.

[Andreasmis]

Yes i am aware how to implement join via access through the line .
Always the join is automaticly to INNER however iv change it to LEFT that means Include ALL records from Employee and only records that are equal to AnnualLeave to be joined .

SELECT Employee.EmployeeName, AnnualLeave.Licenses, AnnualLeave.InBonus
FROM Employee 
LEFT JOIN AnnualLeave 
ON Employee.EmployeeID = AnnualLeave.EmployeeID;

Summary , yes i am getting all the employee names now but still when i joined the three more tables i have the same error

[zmbd]

I'm sure that you're aware of how to do this...
This is just one step as I said in post #6.

The next step is to join ONE table against this query... which table depends upon what you need to show next.
Unless Rabbit has a better approach, this is how I troubleshoot these things.

I'll not be able to get back to this for a few hours, busy day in the lab.

[Andreasmis]

No problem zmbd take your time ,
Now i understand what you want me to do .
Basically start inserting table one by one to see in which table we will have the issue.
I think i have the answer , first as you told me i have the Employee with AnnualLeave.
Then i am inserting the Main table and then the LeaveType.
Until here all is good , four tables and my joins is still on the Left for Employee .
As soon As i insert the Season table that i want to retrive the Aseason that is the year i have problem with the joins.
I need to change the employee join from left to inner to be worked.
So the problem is the Season table i think

Here is also the query with the four tables that is working with out changing my query until now:

SELECT Employee.EmployeeName, AnnualLeave.Licenses, AnnualLeave.InBonus, Sum(Main.Duration) AS SumOfDuration
FROM LeaveType RIGHT JOIN 
((Employee LEFT JOIN
 AnnualLeave 
 ON Employee.EmployeeID = AnnualLeave.EmployeeID)
 LEFT JOIN Main
 ON Employee.EmployeeID = Main.EmployeeID) 
 ON LeaveType.LeaveID = Main.LeaveID
GROUP BY Employee.EmployeeName, AnnualLeave.Licenses, AnnualLeave.InBonus;

[Rabbit]

Why do you even need to join to Season? I don't see that you're even pulling anything from the Season table into your query, why not just leave it out of the query altogether?

[Andreasmis]

Hello Rabbit,
Season stores the year.
E.g SeasonID=1 - Aseason=2012
SeasonID=2 - Aseason=2013
If i extract the seasonID from main or from AnnualLeave i will have multivalues.
I have a combobox that user choose what year wants through the Season Table .

[Rabbit]

But I see nothing in our original query that uses the season table other than the join.

[zmbd]

There maybe another approach. The pivot table view in a select query.
Make a normal select query that has all of the fields of interest then change to the pivot table view and do a drag and drop. Make sure that ALL of the fields of interest are in the select query, don't wory about grouping, totals etc... http://office.microsoft.com/en-us/ac...101901543.aspx