473,320 Members | 1,990 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
15 2080
Rabbit
12,516 Expert Mod 8TB
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
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
5,501 Expert Mod 4TB
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
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
5,501 Expert Mod 4TB
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
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
5,501 Expert Mod 4TB
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
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
5,501 Expert Mod 4TB
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
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
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
But I see nothing in our original query that uses the season table other than the join.
Sep 28 '12 #14
zmbd
5,501 Expert Mod 4TB
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
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

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

Similar topics

8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
3
by: Bryan | last post by:
I've been looking over this newsgroup, but I can't find an answer to my problem. I see that a few others have posted this issue over the years, but none of them got a response. I'll give it...
0
by: Irfan | last post by:
When creating a connection using DataSource configuration Wizard, i am trying to use a cross-tab query, But the wizard does not show any cross tab query. Even i tried Northwind database, it shows...
2
by: Nenad Markovic | last post by:
Hi everybody, When executing a Crosstab Query I see only rows (defined in a row heading) that have values (defined in value field) in at least one column (defined as column headings). How can...
14
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity....
6
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not been able to really find what I'm looking for, nor...
2
by: jason.teen | last post by:
Hi All, I am having trouble creating a crosstab query. In my original data I have two columns, One called "Categorized" and one called "Mapped' in which those columns can hold values of "true"...
2
by: Keith Wilby | last post by:
I have a report that is based upon a crosstab query which return only the columns (fields) it has data for. When my report runs it sometimes fails because some of the text boxes don't have a field...
1
by: bruce24444 | last post by:
First of all I'm new to the forum and am working on my first database. So far I think I've done not too bad but have hit a stumbling block for which I'm not sure how to get around. What I have is...
3
kcdoell
by: kcdoell | last post by:
Hello Everyone: I have a table called “tblStaticAllForecast”, below are the fields I am currently focusing on within the table: LOB GWP FWeek Any given LOB can have a GWP and FWEEK...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.