merge fields

[daniel hayden]

i have two databases with one primary id key (social security number of
clients). Each database contains different information that I want to merge
into one database that is keyed by the social security number.

 

[John Vinson]

i have two databases with one primary id key (social security number of
clients). Each database contains different information that I want to merge
into one database that is keyed by the social security number.

Two DATABASES - two separate .mdb files containing multiple tables,
forms, reports, etc.? or two TABLES within a .mdb file?

And do you want to permanently convert the two tables into one table,
or just combine the information for a report or display?

More details please!

John W. Vinson[MVP]

 

[daniel hayden]

Thanks. It worked fine. But now I have to generate a report with the queried
data, but when i go to the report the append query does not appear as an
option (just the linked tables that do not contain the query data together).
How do I get around that?

 

[KARL DEWEY]

Link the tables and then append.

 

[KARL DEWEY]

Open the report in design view and click on menu VIEW - Properties. Type in
the name of the query as record source.

 

[KARL DEWEY]

Open the report in design view and click on menu VIEW - Properties. Type in
the name of the query in the record source.

 

[daniel hayden]

I tried that but it says it can't create a field list based on an action
query.??

 

[John Vinson]

I tried that but it says it can't create a field list based on an action
query.??

An Action query such as an Append query DOES SOMETHING - it adds new
records to a table when you run it.

It does not provide a list of records which can be used on a Form. If
you want to see the records that the Append query appended, base the
Form on the table that you appended the records into.

John W. Vinson[MVP]

 

[daniel hayden]

okay, sorry about the terminology....its actually two tables within one
database. I want to be able to report out data from both tables:

Table 1 (Fields): ss# of customer, order, date of order, total amount,
salesman
Table 2( Fields): ss# of customer, name, address, city, state, zip, referral
source

We need a report by customer (social security number) that includes all the
fields from both tables. Thus, the report should look like:

John Smith (customer)
333-00-9999 (social security)
Order 1 (1/1/05) $100.00 (Fred Jones, Salesman)
Order 2 (5/5/05) $300.00 (Sally Orth, Salesman)
111 Main Street, Portland, OR 66666
Orion Industries (referral source)

Thanks!

 

[KARL DEWEY]

Build a query with both table with join on SS#. Use the query as source in
your report.