USING "UNION" STATEMENT IN SQL DESIGNER

[Cherrel]

Hi All!

You have all been terrific in helping me with Crystal Reports 7.0, I thought that I'd pose yet another question for you guru's.

I have created a report using CRYSTAL SQL DESIGNER utilizing the "UNION" function. I am trying to get a report that produces *SIX* separate columns for this report. Currently, I can only produce *five* columns because one of my columns is combined. (**WITH** accurate data.)

I want my report to look like the following:

MODEL NUMBER DESCRIPTION RFI NRFI TOTAL TOTAL VALUE
------------ ----------- --- ---- ----- -----------
ABC-123 PRINTER 1 3 4 $4,000.00

Currently, my report looks like the following:

MODEL NUMBER DESCRIPTION RFI/NRFI TOTAL TOTAL VALUE
------------ ----------- -------- ----- -----------
ABC-123 PRINTER MRFI 3 $3,000
ABC-123 PRINTER RFI 1 $1,000
(GROUPING based on MODEL NUMBER)

I am using the following CRYSTAL SQL DESIGNER code
(and I get the right results, just not in the *six* column layout):

select item.in2 as [Model Number],count(equipment.eqnum),'RFI',item.description,sum(item.in5)
from item,equipment,locations
where (equipment.itemnum = item.itemnum
and equipment.location <> 'DRMO'
and equipment.location = locations.location
and locations.type = 'storeroom'
and equipment.eq6 = 'A')
group by item.in2,item.description
UNION
select item.in2 as [Model Number],count(equipment.eqnum),'NRFI',item.description,sum(item.in5)
from item,equipment,locations
where (equipment.itemnum = item.itemnum
and equipment.location <> 'DRMO'
and equipment.location = locations.location
and locations.type = 'storeroom'
and equipment.eq6 = 'F')
group by item.in2,item.description;

Can anyone help me split that one column up into two?

Thanks so much!!
Cherrel. s-)

 

[Ngolem]

Alias the values for NRFI and RFI in the query

then this is in the footer of a group based on Model number

create formula(s) in the detail line (detail line suppressed)to separate the RFI from NRFI and do your sum calcs there as well.

then create display formulas for the results of the calcs