Querying of tables that are linked

[Terry]

This is going to be winded and confusing as I'm still
unsure of the overal function and objective.

I am employed by an independent phone company. We have
several databases in use by different departments. (This I
can't explain)

Our main billing database is on a Unix system called
Horizon. Workstations access it via an emulator.
PROGRESS is the ODBC driver used. Which brings me to the
main issue.

Our GPS/mapping department has an issue that I'm not sure
can be fixed but here it goes anyways.

In the mapping database (I'm assuming on what I was told
is an Access file) a customer is stored as a single record
based on LOCATION. Whether the customer has one, two or
ten phone numbers the MAP dbase recognizes it as a single
record.

The billing dbase (Horizon on UNIX) stores EACH phone
number as a record.

What we're trying to accomplish is a number of things,
these two are the priority. We would like to create a
query with Access to generate a report using the MAP dbase
and BILLING dbase. From what I was told the naming
convention is consistent/tables/fileds are consistent.

The second issue at hand is that every six months we need
to run a query on our switching dbase that stores all
PHONE numbers and then query our BILLING dbase and compare
the two to filter out any records that are not current.

Basically we need to verify that subscribers aren't
getting free phone service. The Switching dbase and
billing dbase records need to be compared and make sure
that any DISCONNECTS are current in both dbases. If not,
we then manually update the record.

If at all this doesn't make any sense, that's my fault.
Any suggestions would be much appreciated.

 

[Tom Ellison]

Dear Terry:

You're making considerable sense, but gatherint the total information
needed to evaluate the feasibility and specific strategy for your
project is, I would suggest, a small project in itself.

Here's what I think I see:

1. Evaluate the size of the databases and likely complexity of the
work to be done. You might be well off to start with using MSDE
rather than Jet. Both are included in Access.

2. Make sure you can link to any and all tables necessary, and that
this works reliably. Assuming you have some scratch query facilities
in the exisitng database, do some calculations, such as getting the
sum of all the phone numbers. While the answer is meaningless, it
certainly should match, and tests whether things are working.

3. Decide whether live connections to the databases will be the best
approach, or whether you should import the data, then fix it up before
using it.

4. Write queries that determine exceptions within the data. For
example, I think you will be joining the databases according to
customers (names, account numbers, or something). Find out how many
do not match. It may be necessary to construct a table to perform the
join, translating any differences between the two. Whether this is
necessary, and just how much work it will be to create it and maintain
it, can only be determined experimentally.

In fact, every step along the way of assuring you end up with a
reliably functioning system depends on judgement and knowledge of the
principles of database to identify and overcome weaknesses.

The above is based on experience of having done things such as your
situation requires. A careful, step by step process of discovering
and eliminating weaknesses is necessary, and some flexibility in the
existing systems may be necessary to make them compatible or to
eliminate, or at least detect, future incompatibilities, and to
overcome them with minimal effort.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts