Hi. This is a newbie question.
I have a database with several tables that contains phone numbers for
the same persons. That database keep getting more and more different
tables from different sources. These tables are obviously different in
name and the name of the phone number field is also different from one
table to another. For example:

Table name Person Id field name phone number field name
---------- -------------------- -----------------------
customers customerId customerPhone
Addressbook PersonId PhoneNumber
employees ID phone

I know that the ID field has the same data for each person on all the
tables(i.e. person 1 in customers table is person 1 and is person 1 in
employees in addressbook etc.)
I also have a table of persons in advance.
I want to create a query that each time will create (or add to) a
table that has all the phones for each person from all the tables.
I think I should build a table that will be used as a cursor, but I
don't know how to put it in a query, since the QBE needs a table name
to run on, and that is not the case.
Is there a way to do it (in sql query or with a combination of VBA
code)?
Thanks.
Doron.

Hi Doron,

You don't have a database, you have a mess!

In relational database design, you will have tables designed to represent
the entities in your database. Each entity can be referenced from many
places but will exist exactly once in the database. In this case, there
should be only one table for Person. An individual should appear in that
one table exactly once.

HTH
--
-Larry-
--

"Doron Hadar" <hadardo (AT) bezeqint (DOT) net> wrote

Thanks Larry, I know it's a mess, but that is exactly the problem!
I have many tables from different sources and I need to combine them
to one table, with the data they carry (as in the example I gave).
Now, if it was an action that needed to be carried only once, I
wouldn't mind doing it manualy. But since I know that such data about
the same persons will keep coming, I need an automated procedure, that
will read the data from any table and add it to the single table.
Doron.

"Larry Daugherty" <ladaugherty (AT) NoSpam (DOT) earthlink.net> wrote

You need to explain why you have so many different sources of data for the
same people or simply different sources and formats for the same kind of
data. ?? Your problem may be technical, in which case you'll get a
technical solution. On the other hand, it may be political - in that case,
a technically elegant solution won't help a bit.

If the data from a given source is always in the same form then it's
possible to create an update query to add the data from the source table to
the destination table. A separate query for each source table. The queries
can all be run behind a command button. When done adding in the new data you
can weed out any duplicates. On the other hand, if the format of the data
varies from the same source, you're inescapably stuck with continual manual
intervention. Alternatively, you could provide your sources with access to
your database via data entry forms. That way, once their data is entered,
you're already done.

HTH

--
-Larry-
--

"Doron Hadar" <hadardo (AT) bezeqint (DOT) net> wrote

Hi Larry.
As I showed in the example, I collect data about persons from
different sources such as office address books, customers services of
different companies, etc. (Don't worry, it's all legal. I'm not a
spammer and I don't spy on people). I get the tables in the same
format. Now, you're right about the update procedure of tables from
the same source, but I keep getting data from new sources all the
time.
one solution is to pour the data I need from all these tables to a
single table, so I'll have two tables: persons and persons multiple
data (simple one-to-many). However, It's not an ellegant solution;
I'll have to manually add records each time a get a new table. I
thought of solution that allows me to keep the original tables, create
a table whice will be used as a cursor table in which I'll have the
tables names and field names (with other information such as date of
recieving, etc.), and run a VBA code that will run the same SQL query
on all the tables, using the data from the cursor table in the query
(i.e. in the SELECT statement, the fields names arrguments and the
table name arrgument in the FROM subclause will be variables and not
the actual fields and tables names). And that is something I do not
know how to do in VBA (I do know how to do it in other languages and
older databases formats, but it's time to move on with the rest of the
world). I'm only using MS-ACCESS as an interface with the data servers
I have because I'm not a programmer and I won't start building heavy
applications.
Hope that will straighten things up.
Doron.

"Larry Daugherty" <ladaugherty (AT) NoSpam (DOT) earthlink.net> wrote