If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query to join records form 2 databases
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? -- Brian |
#2
|
|||
|
|||
Query to join records form 2 databases
"bdehning" wrote: 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). |
#3
|
|||
|
|||
Query to join records form 2 databases
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? "John Vinson" wrote: "bdehning" wrote: 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). |
#4
|
|||
|
|||
Query to join records form 2 databases
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. "bdehning" wrote: 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? "John Vinson" wrote: "bdehning" wrote: 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). |
#5
|
|||
|
|||
Query to join records form 2 databases
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. -----Original Message----- 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. "bdehning" wrote: 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? "John Vinson" wrote: "bdehning" wrote: 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). . |
#6
|
|||
|
|||
Query to join records form 2 databases
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. "Ernie" wrote: 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. -----Original Message----- 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. "bdehning" wrote: 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? "John Vinson" wrote: "bdehning" wrote: 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). . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query by Form adds records | DaveN | Running & Setting Up Queries | 1 | July 12th, 2004 04:15 PM |
Use Query to Sort Records in Input Form | Karl Burrows | Using Forms | 3 | June 14th, 2004 04:52 PM |
Query Form: Print Report | Dennis | Running & Setting Up Queries | 1 | June 6th, 2004 01:08 PM |
surely a form with a ListBox can be used in a query? | 1.156 | Running & Setting Up Queries | 14 | June 2nd, 2004 04:54 PM |
Eliminating Duplicate Records in a Form or Query | RJL | New Users | 1 | May 12th, 2004 12:31 AM |