Combine Data From Multiple Access Databases - CR 8 1

[CAPiTA]

Is it possible to combine data from several Access databases (with identical tables and fields, but with different data) into one report?

Example:

Database1
Products
ID Offset
#001 0.1
#002 0.2
#003 0.3
...

Database2
Products
ID Offset
#001 0.4
#005 0.5
#008 0.6
...

Can I grab all of the Offset data corresponing to product ID #001 lump it together in a report?

 

[kwalters110]

Perhaps you could link the table from Database2 into Database1. Then create a new query that will UNION ALL the records together. This will allow you to create a crystal report on the combined records

#001 0.1
#002 0.2
#003 0.3
#001 0.4
#002 0.5
#003 0.6

This can be accomplished by:
SELECT [TABLE1].[ID], [TABLE1].[OFFSET] FROM [TABLE1] UNIONALL SELECT [TABLE2].[ID], [TABLE2].[OFFSET] FROM [TABLE2];

To view specifically all data for #001, you can use crystal reports record selection:
{query.id} = 1

Does that help?

 

[CAPiTA]

Can you be more specific about what exactly to do with this:

SELECT [TABLE1].[ID], [TABLE1].[OFFSET] FROM [TABLE1] UNIONALL SELECT [TABLE2].[ID], [TABLE2].[OFFSET] FROM [TABLE2];

 

[kwalters110]

I'm sorry about that, it is kinda vague.

Open up one of the access databases and create a link to point to the table in the other data base (File Menu -> Get exertnal Data -> Link Tables).

Once you added the link I'm going to assume you called it Table2. And the table that already existed in this database was called Table1. Create a new query and add Table1 and Table2 to it. Then on the View menu select SQL View. Here you can create the query manually with the syntax I gave above. Basically you want to select each field from Table1 that you want to show, then UNION ALL with a select query of the same fields from Table2.

SELECT [TABLE1].[ID], [TABLE1].[OFFSET] FROM [TABLE1]
UNION ALL
SELECT [TABLE2].[ID], [TABLE2].[OFFSET] FROM [TABLE2];

Think of it as 2 select queries in one.

Once you save this query, execute it to make sure it works properly. Then open crystal and select the database that has your new query as the data source. You should see the name of the query as a table you can add to the report. If not, then click the options button on the Data Explorer window (the window where you choose tables to add) and make sure there is a checkmark next to Views.