Reporting across databases

[Karina M ;)]

How can I enable reporting across databases to render one report (vs 3
indivdual reports with calculations: total counts, avg days, total $)? Simple
language, please, as I'm still a novice. Let me know if you need more info
before replying.
Thanks.

 

[Klatuu]

Let's be sure we are using the correct terminology. When you say 3
databases, that means 3 mdb files. If you actually mean 3 tables in 1 mdb
file, it is a different answer.

If it is 3 databases, then your front end will need to link to the tables in
the back end mdb files that you want to use.

In either case, you will need to construct a query using all 3 tables to use
as the recordset of the report.

 

[Karina M ;)]

There are 3 mdbs. I'm not sure what you mean by 'front/back end'. Also, can
you provide more detail as to linking (like tables are linked in a query?)?
Thanks.

 

[Klatuu]

A split database is when the backend has only the tables and relationships
and the frontend has all the forms, reports, queries, macros, and code
modules. This is the proper way to deploy an application. To link to tables
in other mdbs, use the Linked Table Manager in the Database Utilities menu.

 

[Karina M ;)]

Is the process so simple that a novice could do it? I don't know SQL but am
very fluent in tables, reports and linking across those. Please try to write
in simplest terms.
Thanks.

 

[Klatuu]

Very simple. From the main menu, Tools, Database Utilities, Linked Table
Manager.

 

[Karina M ;)]

For clarification, my tables are not linked across the databases. Are there
instructions I can look up to accomplish this? Would I do this before
splitting the database? When I try to split one of the databases I get an
error message of "subscritpt out of range". What does this mean and how do I
address/fix it?
Thanks.

 

[Klatuu]

Sorry, Karina, but I am getting confused. I asked earlier if you have 3
tables or 3 mdbs. This last post has me wondering.

Now, help me out here.
You have 3 tables to include in the report (Yes/No)
Are all 3 tables in the same mdb file (Yes/No)

 

[Karina M ;)]

To below: Yes/No.
I have 3 separate mdb files and each pertains to a separate (no linked
fields between mdbs) but similar (same structure) group of records. Also,
there may be redundancy between mdbs for the primary key field.

As instructed previously, I created a back end version of one of the mdbs
but I see that my only options to incorporate data from other mdbs is to link
tables (which I can't do) or to import. Does this mean I would have to create
back end versions of every mdb and then place the tables in one mdb in order
to create one report (or would I create 1 report with several subreports?)?
Thanks.

 

[Klatuu]

Why can't you link to the other mdbs? There is no restriction on the number
of backend mdb's you can link to. I have one app that links to two different
mdbs and a SQL Server database the routinely moves data between the 3.

If it is because the other mdbs are not split and live on users computers,
then yes, you will need to split them also so the back end can be on a shared
server you have access to.

I don't know what you mean by linked fields between mdbs, there is no such
thing.

 

[Karina M ;)]

I believe I can't link because each mdb's primary keys are different.
It seems I may be misunderstanding the process. Is there a link you can
provide to walk me through the steps? I know the first step is to create back
end mdbs. Do I then proceed to MOVE DATA or do I link or do I import? If it
makes a difference, I want to be able to report from all 3 mdbs every month
or as needed.
Thanks.

 

[Klatuu]

The primary keys in the other databases is not relavent to linking to the
tables in them. Certainly, each table is unique. If you are linking to the
tables for reporting purposes and won't be making any changes to the data, it
doesn't matter at all. The only issue is that if multiple databases have the
same table name. You would have to link it with a different table name.

Now, it doesn't matter whether the mdbs are split or not, even though they
should be, but if your objective is to split the current mdbs so you can put
the back end on a server where you have access to them, then good, but it
isn't that big of a deal.

You use the database splitter wizard. It will create two mdb files. The
Front End (fe) will retain the original name of the mdb file and the Back End
(be) will have _be appended to the file name. For example.
WorldsGreatestApp.mdb will become
WorldsGreatestApp.mdb (fe)
WorldsGreatestApp_be.mdb (be)

The fe will contain all the forms, reports, queries, macros, and code modules.
The be will contain only tables and relationships
The fe will be linked to the be in the location you specified in the
splitter wizard. You can change it either using the Linked Table Manager or
using code when necessary.

You don't have to move any data or do anthing else.
Now each month to do your reporting, I would suggest using the
TransferDatabase method to do the link. This way, you can easily specify the
name by which you will refer to the table. That is in case some of the table
names are the same.