Report from multiple databases

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 PATwo ways:
Create linked tables in Access. Write your queries and reports.
Create linked servers in SQL and use reporting services.
Either is yucky architecturally speaking.
Get the data out of access whenever phesible.
Deki wrote:
> 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 PA|||Thanks Frisco!
--
Deki PA
"FriscoSoxFan" wrote:
> Two ways:
> Create linked tables in Access. Write your queries and reports.
> Create linked servers in SQL and use reporting services.
> Either is yucky architecturally speaking.
> Get the data out of access whenever phesible.
>
> Deki wrote:
> > 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 PA
>