how do I approach this

[nervous2]

I am trying to create a report but I'm stuck on a few steps.

Scenario. I need send a mailer to all our customers who have purchased X amount over Y amount of time.

We have 2 companies one in Canada and another in the Us.
both share separate databases but their databases are labeled fields are labels the same except point to different directories.

I have created the proper report for Canada and I have created the proper report for the US. But they are both separate and I need to combine them in one report, while suppressing the duplicate entries (some customers were entered in one company and then when the second one started they were entered I the second company)

I have tried to enter it into a subreport but I still generate two proper reports, though they are not combined.

When I get my single 1 report listing two companies I will then create another report based on this data which will create labels for the proposed mailer.

How can I Combine the 2 reports into 1 single report, and then push this data into my label report?

any help would be appreciated.

 

[nervous2]

Sorry forgot to mention using CR8.5

 

[FVTrainer]

What is your backend database? I'm assuming it's pc-based since you mention directories. Does it use an ODBC-driver connection or native driver?

 

[nervous2]

Using a Pervassive Database which is ODBC Compliant, on a PC.

 

[FVTrainer]

Well, I tried to address this completely with Crystal using a Pervasive db (PervasiveSQL 2000i) I had as an example and couldn't get the approach I wanted to use to work (using a UNION statement in the Crystal SQL statement), so at this point, the only thing I can suggest is to try the following:

1) Use a tool like Microsoft Access and link to the two tables to an Access MDB.

2) Write a union query in Access to bring the data together.

3) Base your report on the Access query.

Chances are, since you mentioned separate directories, you have a legacy app that is using the old Btrieve approach (rather than the current PervasiveSQL approach) to database design, which is to create separate directories for separate entities. This presents a problem for Crystal; most often you need some sort of intermediary to bring the data together.

Maybe someone else will have a better idea.