By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

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

P: 8
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 :

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM 
  2.    Nz(Sum(Duration),0) AS SumOfDuration
  3.    SELECT 
  4.       Employee.EmployeeName, 
  5.       AnnualLeave.Licenses
  6.    FROM Season 
  7.       INNER JOIN (LeaveType 
  8.          INNER JOIN ((Employee INNER JOIN Main 
  9.             ON Employee.EmployeeID = Main.EmployeeID) 
  10.                INNER JOIN AnnualLeave 
  11.                   ON Employee.EmployeeID =
  12.                       AnnualLeave.EmployeeID) 
  13.                ON LeaveType.LeaveID = Main.LeaveID) 
  14.             ON (Season.SeasonID = Main.SeasonID) 
  15.             AND (Season.SeasonID = AnnualLeave.SeasonID)
  16.     WHERE (((AnnualLeave.SeasonID)=2 
  17.        Or (AnnualLeave.SeasonID) Is Null))
  18.     GROUP BY Employee.EmployeeName, 
  19.         AnnualLeave.Licenses
  20.     PIVOT LeaveType.LeaveType In
  21.         ("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.LeaveType.
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
Sep 24 '12 #1
Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,341
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.
Sep 24 '12 #2

P: 8
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
Sep 25 '12 #3

zmbd
Expert Mod 5K+
P: 5,331
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_comment] 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.
Sep 25 '12 #4

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

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Nz(Sum(Main.Duration))+0 AS SumOfDuration
  2. SELECT Employee.EmployeeName, AnnualLeave.Licenses, Main.SeasonID
  3. FROM Season INNER JOIN (LeaveType INNER JOIN ((Employee RIGHT JOIN AnnualLeave
  4.  ON Employee.EmployeeID = AnnualLeave.EmployeeID)
  5.  INNER JOIN Main
  6.  ON Employee.EmployeeID = Main.EmployeeID)
  7.  ON LeaveType.LeaveID = Main.LeaveID) 
  8. ON (Season.SeasonID = Main.SeasonID) 
  9. AND (Season.SeasonID = AnnualLeave.SeasonID)
  10. WHERE (((AnnualLeave.SeasonID)=2 Or (AnnualLeave.SeasonID) Is Null))
  11. GROUP BY Employee.EmployeeName, AnnualLeave.Licenses, Main.SeasonID
  12. PIVOT LeaveType.LeaveType;
  13.  
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
Sep 26 '12 #5

zmbd
Expert Mod 5K+
P: 5,331
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.LeaveType.
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)
Sep 26 '12 #6

P: 8
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
Sep 27 '12 #7

zmbd
Expert Mod 5K+
P: 5,331
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.
Sep 27 '12 #8

P: 8
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 .
Expand|Select|Wrap|Line Numbers
  1. SELECT Employee.EmployeeName, AnnualLeave.Licenses, AnnualLeave.InBonus
  2. FROM Employee 
  3. LEFT JOIN AnnualLeave 
  4. ON Employee.EmployeeID = AnnualLeave.EmployeeID;
  5.  
Summary , yes i am getting all the employee names now but still when i joined the three more tables i have the same error
Sep 27 '12 #9

zmbd
Expert Mod 5K+
P: 5,331
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.
Sep 27 '12 #10

P: 8
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Employee.EmployeeName, AnnualLeave.Licenses, AnnualLeave.InBonus, Sum(Main.Duration) AS SumOfDuration
  2. FROM LeaveType RIGHT JOIN 
  3. ((Employee LEFT JOIN
  4.  AnnualLeave 
  5.  ON Employee.EmployeeID = AnnualLeave.EmployeeID)
  6.  LEFT JOIN Main
  7.  ON Employee.EmployeeID = Main.EmployeeID) 
  8.  ON LeaveType.LeaveID = Main.LeaveID
  9. GROUP BY Employee.EmployeeName, AnnualLeave.Licenses, AnnualLeave.InBonus;
  10.  
Sep 27 '12 #11

Rabbit
Expert Mod 10K+
P: 12,341
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?
Sep 27 '12 #12

P: 8
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 .
Sep 28 '12 #13

Rabbit
Expert Mod 10K+
P: 12,341
But I see nothing in our original query that uses the season table other than the join.
Sep 28 '12 #14

zmbd
Expert Mod 5K+
P: 5,331
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
Sep 29 '12 #15

P: 8
Rabbit and zmbd i thank you both of your support , i have change my mind and i just left the query how it is as i can spend more time to fix this.
I have made another form that you can see those people that are not assigned to the query ,
Thanks once more
Sep 30 '12 #16

Post your reply

Sign in to post your reply or Sign up for a free account.