Showing specific data within a query/report

[amoseley]

I have a query with about 20 columns of employee data that shows information for all of my active employees.  I now need to add 2 specific columns for goal amounts for certain deductions.  Problem is, the goal amounts and deduction amounts are with in the same table structure.  I.E. Normally I'd do a report for Deduction=HFSA1 and show the goal amount, and then do another report for Deduction=DFSA1 and show it's goal amount.  I need one report to show all of it, in separate columns, and where an employee doesn't have the deduction, show $0.00.

Here's an example below of what I want it to look like when I'm done.
John Smith has a DFSA1 goal of $1,000 but no HFSA1 goal.
Betty Crocker has a DFSA1 goal of $2,000 and a HFSA1 goal of $5,0000.
Jane Doe doesn't have any goals.

Employee ID	Employee Name	Other Employee Info	HFSA1 Benefit Goal Amount	DFSA1 Benefit Goal Amount
12345	John Smith	Regular Employee	$0.00	$1000.00
54321	Betty Crocker	VIP Employee	$5,000.00	$2,000.00
98765	Jane Doe	Regular Employee	$0.00	$0.00

Any help on this is greatly appreciated.

[raj_aries]

Can you try using a case statement, one for each goal amount...something like this for two columns

Total(Case [Deduction]='DFSA1' then [goal amount] end) [DFSA1 Benefit Goal Amount]
&
Total(Case [Deduction]='HFSA1' then [goal amount] end) [HFSA1 Benefit Goal Amount]

Regards
Raj

[raj_aries]

Can you try using a case statement, one for each goal amount...something like this for two columns

[DFSA1 Benefit Goal Amount]
-----------------------------------

Total(Case when [Deduction]='DFSA1' then [goal amount] end)

[HFSA1 Benefit Goal Amount]
-----------------------------------
Total(Case when [Deduction]='HFSA1' then [goal amount] end) [HFSA1 Benefit Goal Amount]

Regards
Raj