Merge two tables

[Bala]

Gurus,

I got two tables e.g Table A & B. Both got the 4 fields and the data type also same (i.e TEXT). The number of records in table A is 569 and number of records in table B is 681.

I want to execute a query to list-out all the records in Table A i.e 569 records and corresponsding records in Table B ( all the table A fields must match with table B ). But I can't do it. pls. help.


FYI, I have tried design query join properties by selecting # 2( i.e include all the records from table A etc ) but it doesn't work for me.

Pls. hlep.

With regards,
Bala

 

[Andrew Fergus]

Hi Bala,
I'm not sure if I am reading your question correctly. Are all of the fields in both tables, on which you to do your match, set to text format? And you want all four fields in table A to match all four fields in Table B, such that field 1 from Table A = field 1 from Table B and so forth up to field 4. And you then want to show only the records from Table A that have a matching record in Table B? Is that what you are looking for?
Andrew

 

[Bala]

Andrew

Thanks for your reply

I want all four fields in table A to match all four fields in Table B, such that field 1 from Table A = field 1 from Table B and so forth up to field 4. And I then want to show ALL THE records from Table A that have a matching OR NOT MATCHING record in Table B.


With regards,
Bala

 

[Andrew Fergus]

Hi Bala

Create a new query -> design view -> ok -> Table A -> add -> Table B -> add -> close.

Link field 1 from Table A to field 1 from table B by dragging and dropping in the upper half of the screen. Repeat for fields 2, 3 and 4. Double click the line linking the two field 1's and change the join properties to "Include all records from Table A and only those records from Table B where the joined fields are equal" -> Ok. Repeat for the links for fields 2, 3 & 4.

Include the fields you want in the query from both tables. If you view the query results you will get all records from Table A and only the matching records from Table B.

HTH, Andrew.

 

[Bala]

Andrew

Thanks for your reply.

I did exactly the same but somehow the (query's output) number of recors more than Table A record.

'confused' Bala

 

[Andrew Fergus]

Do some of the records in table A have more than one match in table B?

Andrew

 

[Bala]

Andrew

I agree with you that for some records it works. As you mentioned, Table B got more than one record match. But still I expect the query result (i.e number of records ) should be equal to Table A records. Is there any other way to achieve this ?


Pls. help

-Bala

 

[SydneyGeek]

Bala

Have you tried a Union query? Basically you need to write the SQL yourself because Union queries won't work in the query Design view. It merges the data in 2 or more tables, removing duplicates on the way -- and you can then use a MakeTable query to turn that into a new, combined table.
Check this post ... http://support.microsoft.com/default.aspx?scid=kb;en-us;209062 .. to see how to create a Union query, and this post ... http://support.microsoft.com/default.aspx?scid=kb;en-us;208819 ... to see how make a new table from a Union query.

Denis

 

[Andrew Fergus]

Hi Bala

In response to this :

But still I expect the query result (i.e number of records ) should be equal to Table A records.

... the query is returning exactly what we set it up to do. If there are multiple matches do you know which record you want to return from Table B? What you want to do is possible (via a total query) but you have to know what you want first - so if there is more than one match, which record do you want to show? (I expect you will also need to know this before you try using a union query)

Andrew.