Query to join records form 2 databases

[Guest]

Someone mentioned using an update query.

I have a query that I am trying to combine records from 2 forms that were in
two different databases and now have been imported into one database.

I would like to be able to list the records from one on top of the other in
this query. Fields that are the same include Account Name, Assigned
Consultant, Date Written Report Sent and Location Servicing Division. There
are many others but for summing purposes mainly.

Each has its own Primary Key field.

How best to proceed?

 

[Guest]

Someone mentioned using an update query.

I have a query that I am trying to combine records from 2 forms that were in
two different databases and now have been imported into one database.

I would like to be able to list the records from one on top of the other in
this query. Fields that are the same include Account Name, Assigned
Consultant, Date Written Report Sent and Location Servicing Division. There
are many others but for summing purposes mainly.

Each has its own Primary Key field.

Do you mean two different *databases* - separate .mdb files each contining
multiple tables, forms, reports, etc.? or two different *tables*? And what do
you mean by "on top of the other"? Individual records, or what?

Note that the Form which was used to enter the data is totally irrelevant.
Data is stored in Tables, not forms. If you have two tables of identical
structure, an Append query will allow you to add the records from one table
as new records in the other table (which would probably be a good idea!). If
the tables are only partially identical, and you want to keep them separate
and intact but to display some fields as if they all came from one table, a
UNION query is a good option - see UNION in the online help for details. You
can base a Totals query on a UNION query if you wish (but you can't do both
in the same query).

 

[Guest]

John,

The 2 databases being combined have different tables, forms,and reports and
the structure of both are different. One has one table and the other 3 tables
connected by primary keys.

Trying to create a report eventually that will contain similiar data from
each table structure. Each has a AutoNumber which if it didn't was something
I thought might work. I have started to rename the fields that contain
similar data in both so that they are the same which I think will help.

So with the UNION Query I can just bring the fields I need from both and
create one query. Does my structure of tables play any part on if the Union
Query will work?

Can you explain a little more the part about not being able to do both in
the same query? You mean a person can't total and use a Union Query together?

What I meant by stacking was that the queries I was trying was almost
working but for same field names it would list records from both in the same
row instead of 2 rows. Will the Union query help this?

 

[Guest]

John, I seemed to have brought together the data in a Union Query like you
said.

What is the secret then to be able to do count and sum of some of the fields
which I am having trouble with? I do sum and count in each of the queries
brought together by the union query.

 

[Ernie]

Create a simple Totals query and use your Union query as
its source (instead of a table).

Create a new query in design view, choose your union query
from the list of objects, choose which fields to display.
Then right click in the QBE pane and click on "Totals" to
change it to a totals query, make sure that you use
the 'group by' and 'sum' in the proper columns (you can
add selection criteria here if needed). Run it to make
sure it is giving you what you want.

Finally, create a report using this totals query as its
record source.

 

[Guest]

Ernie,

I had created another query and am using total row as needed. The issue now
is that I don't get a count of 1 for one of my fields which I need. I get
counts as high as 57. I need to figure out again how to exclude duplicate
counting and count each record only once.