Report from multiple databases

[Guest]

Hello,
I'm trying to create 2 reports. I have SQL Server 2000 and Access 2003.
Report should be done in Access. There are 4 different databases on same SQL
Server.
I need report showing:

CustomerID
AgencyName
State
TotalDVI
TotalDRI
TotalVI
......

Totals are counts of all files in corresponding tables for all 4 databases.
To add to this problem, one database has different names of tables and
fields in them then other 3 databases.
Databases are definitely a mess and I can't do anything about it at this time.
Is there a way to accomplish this?

Deki

 

[Bill Edwards]

Option 1:
Link the tables from the different databases to your access mdb. Access the
data in the tables as you would any other linked table.

Option 2:
Create a view or stored procedure in one of the databases that uses fully
qualified table names to summarize/gather/whatever information from the
tables in the other three databases. e.g. SELECT blah, blah, blah FROM
MyServer.Northwind.dbo.Customers as NWCust INNER JOIN
MyServer.Pubs.dbo.Client as PC on NwCust.CustomerKey = PC.CustomerKey
INNER JOIN MyServer.Library.dbo.Patrons AS P ON NwCust.CustomerKey =
P.CustomerKey

 

[Guest]

Thanks Bill, that will be good to start me up. I think I would go with view
or stored procedure because they will be too complicated for Access to
execute.