| Author |
Message |
bshfdan
Newbie
Joined: 11 Dec 2008
Location: United States
Online Status: Offline
Posts: 3
|
   Topic: Select records question
Posted: 11 Dec 2008 at 6:37am |
I need a report to show all of our employees and if they have a certain deduction code to show that deduction otherwise show a blank field. I have two linked tables, one with the emp info and one with the deduction info. If I use selection to select dedcution code 2015 I only get emps who have the deduction. Here is a sampe of my data>
File 1 (employee)
Emp # Name
123 Joe Smith
124 Jane Smith
File 2 (deduction)
Emp # Deduction Amt
123 2015 15
I would like the report to look like this:
123 Joe Smith 2015 15
124 Jane Smith
Any help would be greatly appreciated.
|
 IP Logged |
|
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 11 Dec 2008 at 6:58am |
Hi,
Yes you are correct ,if you select deduction code 2015 you will get only 2015 i.e. what you are filtering in your report,but if you want to include nulls also then you need to check for one more condition
isnull({Table.deductioncode) or {Table.deductioncode} = 2015
Cheers
Rahul
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 11 Dec 2008 at 7:03am |
I saw this for another post, you need to change the link to an outer join.
In the Database Expert/Links tab arrange your tables so that the employee table is on the left and the deduction table is on the left. The you want to right click on the link and select Linking Options. Select the option Left Outer Join...All of the employees will be included in the report, but only the deductions that match the employees will be displayed. Employees that have no deductions will be still be displayed.
It sounds like this is what you are after.
Hope it helps.
|
|
IP Logged |
|
bshfdan
Newbie
Joined: 11 Dec 2008
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 11 Dec 2008 at 7:09am |
|
That would work only if there were only 2015 deductions on the deduction table. I should have clarified that there are many different deductions on the deduction table for each employee but I only want to show 2015 on the report. Thanks for your help.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 11 Dec 2008 at 7:12am |
|
Then the next step, I would think, is to conditionally suppress the deduction and amount fields if the deduction code is not 2015...at least that is what I would do.
|
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 11 Dec 2008 at 7:18am |
Hi
if you need to show only 2015 and null deductions then the below code should work..... why you need to reterive all the records and then suppress.......
What happens when you use the code are you getting incorrect results
isnull({Table.deductioncode) or {Table.deductioncode} = 2015
cheers
rahul
Edited by rahulwalawalkar - 11 Dec 2008 at 7:22am
|
|
IP Logged |
|
bshfdan
Newbie
Joined: 11 Dec 2008
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 11 Dec 2008 at 7:34am |
|
That works but I get two records for each emp if they have the deduction. One with the deduction and one with the null reocrd. I should have clarified that the employee can have many different deductions of the deduction table. Thank you for your help.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 11 Dec 2008 at 7:40am |
I'm sure that Rahul will respond, but I would create a group by the person then suppress the group header, and details and show the summary for the person. You could sum the deduction code and amount and supress the 0 amounts, this should give the results you are after.
At least that is what I would do, and you did mention the multiple deductions, I just missed it....A report can take so many iterations to get right.
Hope it helps.
|
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 11 Dec 2008 at 7:48am |
Hi
bshfdan Can you post the sample data which gives us clear picture i.e. current results and expected results which will help to resolve....
cheers
rahul
Edited by rahulwalawalkar - 11 Dec 2008 at 7:49am
|
|
IP Logged |
|
|
|
