Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 9th, 2005, 11:14 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to Merge Tables into one query

Hi Everyone

I'm trying to merge two tables which are links from other databases to form one single query in a third database. The problem comes when I try to link the data, because both tables have the same structure but different data. So the field names are identical in both tables.

So when I try to create a query, it asks for which table the data should come from. But I want it to come from both tables.

Is their anyway I can do this using a access query.

Thanks.

Reply With Quote
  #2 (permalink)  
Old March 9th, 2005, 01:02 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   I would do this with VBA if it has to be dynamic.

   Create a table that has all the fields from both tables in it. If they are the same, then just copy one table structure and leave it empty.

   Then, using a button on a Form or Report event, do the following:

Empty all the data in the table.
Call the first database, and copy all the data into the table, minus the primary key.
Call the second database and copy all the data into the table minus the primary key.
Open the form or report.

This may require two System DSN's, but once you write code for the first table, you already have the code for the second table.

If you just want to do this once to consolidate the tables into one database, then create the table, and do an Insert>Table>Import Table, and import all the data from each table, minus the primary key column, into the existing table.

HTH


mmcdonal
Reply With Quote
  #3 (permalink)  
Old March 10th, 2005, 12:27 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

I'm not clear what you're trying to do. It sounds like you want all of the records from both tables. If that is the case, what you want is a union query.

Since both tables are identical, create a standard select query for one of the tables. Change to SQL view of that query and you'll see the select statement. Something like,

SELECT tbl1.fld1, tbl1.fld2 FROM tbl1;

I would start by removing all of the "tbl1." from the fields. You won't need them.
Copy all of the text (except the semicolon at the end).
Change the Query type to "SQL Specific>" "Union" (or simply type UNION before the semicolon).
Paste the copied text and replace the tbl1 with tbl2 in the FROM. This is why I recommended removing all the "tbl1.". If you didn't you have to change all of the "tbl1." with "tbl2.".

This will give you all the records from both tables in no particular order.

Save the query and you can treat that query as if it was a single table in your third database. If data is updated in either of the other databases your query will automatically pick up the change.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
Reply With Quote
  #4 (permalink)  
Old March 10th, 2005, 04:28 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the help guys I'll try both methods and see which is quicker. I was trying to avoid creating a new table in the database, since I don't really wont the database to grow too large.

But I like the idea of trying to use a UNION query to join to tables of data together. And yes the table would need to be updated, since new data is put into the other database's every few hours.

Reply With Quote
  #5 (permalink)  
Old March 10th, 2005, 03:11 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Yikes,

  This gets more complicated every time I look at it. Did you need to maintain the primary keys in each of the two tables for look up purposes? Is there anyway you can rename the tables in the primary databases to distinguish them?

   Just curious, why two identical database tables that are being updated independently?

Thanks,


mmcdonal
Reply With Quote
  #6 (permalink)  
Old March 11th, 2005, 04:19 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Didn't need to maintain the primary keys in the tables, luckly both tables were called different things in the two databases.

As for the reason, well it's mainly because the two databases do tottally different things but at the end of the day produce the same structure of data. The third database needs the data to calcuate results, but doesn't care which method was used to collect the data.

But anyway think I've sorted it. I cdreated a union query that joined the two tables together, then create a basic select query so that I can put my extra calculations in. So far so good, it appears to be working.

Once again, thanks for the help guys.

Just needed somebody to push me in the right direction.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DataSet - Merge Data Tables srish ADO.NET 0 September 8th, 2007 10:04 AM
Need to merge several tables with unlike structure CORiverRat Access 5 December 14th, 2005 12:58 PM
Whether or not to merge two tables chrislepingwell SQL Language 1 November 24th, 2005 06:18 PM
Word E-Mail Merge using Access Tables Richard Lally Access 2 February 7th, 2005 04:48 AM
How can I merge two databases and tickets tables? drarem Access VBA 2 October 5th, 2004 01:07 PM



All times are GMT -4. The time now is 10:55 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.