combining like tables from different databases

[rspinella]

Ok here is the problem.

I have created a database for someone and it contains x amount of records on one table. There are many fields that were left blank to fill in as we go. So they have been actively entering info into some fields for certain records at their office, but i have also been entering info into different records at my home. Therefore, the databases have exactly the same table and records on both computers and the only difference is the information that has been entered in fields like phone#, e-mail, etc.

How can I combine these databases into one that exactly resembles the original with x records and one table, but including all the entered info?

 

[r937]

import one of the tables into the database of the other, so that you have them defined as table1 and table2

then run this maketable query --

select aliasid     as id
     , max(alias1) as col1
     , max(alias2) as col2
     , max(aliasN) as colN
  into newtable   
  from (
       select id   as aliasid
            , col1 as alias1
            , col2 as alias2
            , colN as aliasN
         from table1
       union all                 
       select id
            , col1
            , col2
            , colN
         from table2
       ) as combined        
group
    by aliasid

newtable should have what you want, and then you can delete table1 and table1 and rename newtable

of course, if the two tables have conflicting information in the same row/column, then you're going to get the max value, and never know it

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[rspinella]

r937,

That is a great idea and I totally understand the logic, but I haven't been at this for long and maybe I typed it incorrectly. When I try to run or save it comes back saying
"Syntax Error in date in query expression 'Regist #'."

I will paste some of my sql in here so you can see if it's structured correctly. It won't all be here because I have a bunch of field names. Regist # is the primary key for both tables named All Info and All Info1. The new table is All Info2.

select Regist # as Regist #
, max(LastName) as Last Name
, max(FirstName/MI) as FirstName/MI
, max(E-Mail) as E-Mail
, max(Home Phone) as Home Phone
, max(Cell Phone) as Cell Phone
, max(Office Phone) as Office Phone
, max(Organization) as Organization
, max(Title) as Title
, max(Address #) as Address #
, max(Apt #) as Apt #
, max(Street/Apt #) as Street/Apt #
, max(City) as City
, max(State) as State
, max(Zip) as Zip
into All Info2
from (
select Regist # as Regist #
, (LastName) as Last Name
, (FirstName/MI) as FirstName/MI
, (E-Mail) as E-Mail
, (Home Phone) as Home Phone
, (Cell Phone) as Cell Phone
, (Office Phone) as Office Phone
, (Organization) as Organization
, (Title) as Title
, (Address #) as Address #
, (Apt #) as Apt #
, (Street/Apt #) as Street/Apt #
, (City) as City
, (State) as State
, (Zip) as Zip
from All Info
union all
select Regist #
select Regist # as Regist #
, (LastName) as Last Name
, (FirstName/MI) as FirstName/MI
, (E-Mail) as E-Mail
, (Home Phone) as Home Phone
, (Cell Phone) as Cell Phone
, (Office Phone) as Office Phone
, (Organization) as Organization
, (Title) as Title
, (Address #) as Address #
, (Apt #) as Apt #
, (Street/Apt #) as Street/Apt #
, (City) as City
, (State) as State
, (Zip) as Zip
from All Info1
) as combined
group
by Regist #

Thank you for the help!

 

[PHV]

Put ALL your field/table names inside [ ]:
select [Regist #] as [Regist #]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

the reason i gave the columns aliases in the first subquery of the UNION, was so that i could give them another alias in the outer query that just so happens to be the same name as it was before

if that's confusing, it's because access won't let you have a "circular reference"

i.e. you cannot say select foo as foo

so inside the subquery, col1 gets the alias alias1, and then in the outer query, you can say max(alias1) as col1, so that the resulting column is back as col1

hope that wasn't too confusing for ya



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[rspinella]

It took me a minute to figure out the alias thing, but now it totally makes sense. I worked through all the code on my large tables and it all came together just how I wanted it. I can't thank you enough for this solution. I will hale you as a god to my friends.

 

[rspinella]

Oh, and I'm sorry for the late reply. I just don't get a lot of time to work on this.