Grouping on a field from 2 dataset (separate databases)

[kcalixtro]

I have a report that currently has 2 connections to 2 separate databases on 2 separate servers that are physically on 2 different cities. (that works fine!!)

The data pull from each server is structurally the same (have the same field names and types). It's just that one gives me data from one location, and the other from the other location. (as I said, the data is being pulled into crystal fine)

THE PROBLEM: The Crystal report I am working on, is supposed to combine the data from this 2 datasets and group them by a given field (let's call it "WantedField"). But even though the name of the field is the same on both queries, I CAN'T find a way to JOIN the data within Crystal so I can group by the field name("WantedField"). I can see that Crystal prequalifies field names with the command name, so WantedField from CommandONE is CommandONE.WantedField while WantedField from CommandTWO is CommandTWO.WantedField making them different names within crystal.

Does anyone know a way around this so I can group by the WantedField regardless of which command brought the data over?

 

[IanWaterman]

Can you get Database in location 1 to query database in Location 2.

You could then build a union query command on database location 1 and save yourself the trouble of getting Crystal to merge data. It will probably execute a lot faster too.

Ian

 

[kcalixtro]

I wish I could!!! These are 2 separate Symposium Databases. If they were SQL servers I would have done exactly what you said from the start.
Any other Ideas?
Thanks,