Used a Union Query - results displaying twice

[TechUser23]

Very confused on how to make this report work. I have used a Union Query in SQL that returns two rows for each distinct ticket #.

In my crystal report I placed the primary and the secondary information (the 2 rows) in my details section. Now I'm getting back two sections when I preview the report in my application. On the first section, I'm getting back the Primary Information and a blank on the Secondary information. On the Second section, I'm getting a blank on the Primary and information on the secondary.

Is it possible to format this with a formula to combine this based off the ticket number (which is distinct for each service date)?

I hope I explained this ok .. thanks in advance.

 

[IdoMillet]

Why do you need two sections in the report?

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[TechUser23]

Ido,

Unfortunately, the Union SQL in necessary to pull all the necessary information into the report.

The union query is pulling a patients primary insurance and a patients secondary insurance, based off a patient visit (for which the ticket # is distinct to the visit).

In Query Analyzer, I get two rows back for each patient ... one row which contains the patients primary insurance and one row which contains the secondary insurance information. Each row shares the patients name, visit date and ticket number (as it logically should). The other fields are distinct based off my Union.

When I add the fields both into Crystal, Its producing two sections on my report (because of the shared information im thinking). That make sense?

 

[lbass]

It sounds like you have a group on a field that is breaking the info into the two sections. It is unclear how you want the info to display. Since the insurance information is now in one field, it will necessarily appear in two different rows.

-LB

 

[TechUser23]

lbass,

I did have a group and since removed it to no avail.

I'm beginning to believe its where Im pulling my information into. The report is simple in design. Within the "Details" section, I have the following fields:

Patient Name
Date of Service
Ticket Number
Doctor Name
Facility Name
Company Name
Visit Patient Balance
Visit Insurance Balance

Primary Insurance Carrier Name
Primary Insurance Insured ID
Primary Insurance Group ID
Primary Insurance Insured Party Name
Primary Insurance Effective Date
Primary Insurance Termination Date

Secondary Insurance Carrier Name
Secondary Insurance Insured ID
Secondary Insurance Group ID
Secondary Insurance Insured Party Name
Secondary Insurance Effective Date
Secondary Insurance Termination Date

To give you more insight, in Query Analyzer, I get two rows to every ticket number (one for Primary and one for Secondary). They both SHARE the Patient Name, Date of Service, Ticket Number, Doctor Name, Facility Name, Company Name, Visit Patient Balance & Visit Insurance Balance.

Because I'm reporting the Primary and the Secondary both in conjunction with the Shared fields, I think thats why I'm getting it to report wrong. I tried setting up using a second detail section and still got issues. Any thoughts? I wish I could copy and paste or email this report to show what Ive tried to make. At any rate, any help you can offer me is deeply appreciated!!!!!

 

[lbass]

In your union statement add a field to each side of the query that identifies which is which. Something like:
select
'Primary' as type
from table
union
select
'Secondary' as type
from table

Insert an outer group on patient with the patient info displayed in a group#1 header. Then insert a group on type, and place your display fields in only one detail section.

-LB

 

[TechUser23]

lbass,

In my SQL query, I have the Following:

CREATE TABLE #INSURANCE

My Field
My Field
etc ...

INSERT INTO #INSURANCE

Select Distinct
patient name,
ticket number,
Primary Ins Info
etc etc ...

from ...
where ...

Union

Select Distinct
patient name,
ticket number,
Secondary Ins Info
etc etc ...

from ...
where ...

select * from #INSURANCE
ORDER BY PatientName


DROP TABLE #INSURANCE

Would this work?

 

[lbass]

I'm sorry, but I don't know. I thought you were creating the union in a command, and I'm unfamiliar with what you are actually doing.

-LB

 

[ianoctdec]

Hi,

It seems loke you need two fields: PrimaryInfo and SecondaryInfo.
Try:

Select Distinct
patient name,
ticket number,
Primary Ins PrimaryInfo,
'' SecondaryInfo
etc etc ...

from ...
where ...

Union

Select Distinct
patient name,
ticket number,
'' PrimaryInfo
Secondary Ins SecondaryInfo
etc etc ...
from ...
where ...

Daniela