Skip to Content
0
Former Member
Jul 31, 2008 at 09:29 PM

Summarize from two data sources

170 Views

I am not sure how to summarize data from two data sources (SQL Server and Access). I know how to do it from each data source one at a time (by using grouping and hiding the details), but my internal customer wants it all in one report linked by username. My trick does not extend to two data sources because there is not a one-to-one relationship between the records.

Here's a simplification:

Data source 1: SQL Server

Username, Time Interval, Quantity, Time

Bob, 7/1/2008 12:30pm, 5, 10

Bob, 7/1/2008 1:00pm, 1, 2

Data source 2: Access

Username, Date, Quantity, Time

Bob, 7/1/2008, 500, 5

Bob, 7/1/2008, 400, 4

Bob, 7/2/2008, 100, 1

Output

Username, AVG Time/Unit 1, AVG Time/Unit 2

Bob, 2, 1

I use Crystal Reports Advanced 10.0 to embed reports in [CIC|http://inin.com] 2.3.1.