merging 2 tables that have identical fields except one table has an extra column for a primary key

[aemara]

Hi guys,



I have 2 tables with fields that look like this:

Table1: ID name age gender (where ID is the primary key) Table2: name age gender


These tables have thousands of rows. Now I am trying add the info in table2 to table1. Table2 has the age and gender fields filled up while the name field is empty. I thought adding the name field would help with the merging.

I tried using a union query, but the result was a merged table with no ID field.
Could someone help point me in the right direction? I would very much appreciate that.

aemara

[ssanfu]

How does table 2 relate to table1? If table 2 name field is empty, what is the point of merging the two tables?

BTW, storing the age is not a good idea. Every year people age. It is better to store the birth date and calculate the age.


And welcome to the Access forums..

[aemara]

Thank you for your reply and your welcoming ssanfu,

Well both tables have their age and gender fields filled. I want to add these ages and genders from table2 into table1 even if it would mean that some of the fields in the resulting table will be empty for those records ( they will be filled later on).

You're right I should be using birth date :P thanks

Sorry for the late reply

Ahmed

[ssanfu]

So how do you know which record in table 2 is related to which record in table 1?

Table1: ID name age gender (where ID is the primary key)
Table2: name age gender

It looks like the only common field is the name field.
You can try using an update query, but the problem is if there are two records with the same name, how will you know which record would/should be updated?

BTW, "Name" is a reserved word in Access and shouldn't be used for object names.

[June7]

I am equally confused. Of what value are records with only age and gender data? Therefore a UNION is meaningless.

Agree with Steve about updating based on name link. Names are very poor unique identifiers. And if the name field is empty, there is no data to join on anyway.

[aemara]

The tables are not related, and for simplicity I removed the 'name' field from table 2. All I was trying to do was add the ages and genders to table 1, even if that left ID and name in table 1 empty.
I looked more online and I found that an append query might do the trick, but so far everytime I fill in the details for the append query and view it in datasheet view, it doesn't show me the appended data.

[June7]

Did you RUN the append query?

[aemara]

Shouldn't I wait until the datasheet view shows me the correct info? I could probably run it on a copy just in case it messes up my tables

[aemara]

Ok so I ran it and it seems to work, but I don't understand why. I mean whats the point of checking different views of the query before running it, if checking it doesnt show you the correct results?

[June7]

I'm not sure of the answer. I haven't looked at an append query object in so long, don't remember.

How many records were involved? Maybe a copy/paste would have served.

[aemara]

We're talking hundreds of thousands..I tried copy pasting but there was a problem with file sharing lock count being exceeded

[June7]

I just tested an INSERT SELECT query written in SQL view and in datasheet view I do see records. However, trying to build APPEND query to pull data from a table with the design view is just frustrating. I don't have any databases that use saved action query objects (DELETE, INSERT, UPDATE). I do all action SQLs in VBA.

[aemara]

What exactly is VBA? From what Ive read online I'm not sure what it is

[June7]

Visual Basic for Applications - VBA.

It is programming language packaged with Microsoft Office applications. http://office.microsoft.com/en-us/ac...010341717.aspx

[aemara]

Thanks for the link! I might actually need this.