To Join two Tables in a database

Question

I have two tables:

The first table has 160,000 records and the other one has 200000 records.

I want to join these two tables: These two tables have first name, last name and member ID in common.

What is the best solution? Should I join these two tables through common fields? Please advise.

GN

---

GGGGGNNNNN

Answer 1

Do the tables have other fields than First Name, Last Name and Member ID?

Do all records in the first table have a match in the second table, or some, or none?

---

Regards, Hans Vogelaar

Answer 2

Is MemberID the primary key in both tables?  If so then create a union query like this --

SELECT Table1.MemberID

FROM Table1

UNION SELECT Table2.MemberID

FROM Table2; 

Note it is not a 'UNION ALL' query.

Then left join the union query to the 2 tables.

Answer 3

Yes, the tables have other fields other than First Name, Last Name and Member ID. one of the tables has 69 fields and the other table has 116 fields. What We have in the table with more records should have a match in the other table, but we don't know, we need to find it out. In this scenario, I need to find out what are duplications in the table with higher records. I need to join the tables, after joining the tables, I have to do a query and export the result into an Excel workbook and insert a pivot table on the data, because I don't know how to solve it in Access. I can do groupping and counts to find the counts for the fields I need, but the point is that these fields are mapped to other fields as well and at the same time, I have to find the count for the other fields as well. So cross tab query or select query might be the solution, but I need first join the tables. Even if I join the tables, how can I validate that join is done correctly? How can I validate it? Many thanks GN

---

GGGGGNNNNN

Answer 4

Hello,

The member id is not the Primary key? Where should I enter this string.

Thank you for the help and your time. GN

---

GGGGGNNNNN

Answer 5

Could you try to describe in more detail what you want to accomplish?

---

Regards, Hans Vogelaar

Answer 6

>> , but I need first join the tables. Even if I join the tables,  <<

So you mean JOIN as in relate the two tables together so your resultant records are a result of mixture of fields from both tables when common fields equal each other?  Or ... do you mean that you want to combine (stack, append) the records from one table with the other to end up with a resultant set of 360000 records.

---

Brent Spaulding | Access MVP

Answer 7

Hello Hans,

Hope you are doing well,

I have two tables. I need to join these two table (relate the tables) because I need to do some analysis, Table 1 goes through some process and the result is stored in Table 2. The number of the records are not the same. For example if we have 400,000 records in Table 1, after processing, the result has come in a table 2 with 220331 (I am not sure about the exact number of records). These two tables have common fields: ID Number (is not a primary Key), First Name and Last name. I need to join these two tables through (ID Number, First Name, Last Name).

After joining the tables:

1-I need to discover that there are matching records between two tables? to find the number of matching records?

2-How can I make sure the join is performed correctly? Or how can I validate what I have done is correct?

3-I need to find out the duplicates in Table 2? I think I should use duplicate query and unmatched query to discover what duplicates are. Any tips? Any suggestion?

4-How to delete duplicates from Table 2?

5-When join is done, I have to do some counts on some fields. For example how many A I have in column 3 and what is the number of Bs' in column 58 per all As' in column 3?

your help is greatly appreciated.

Respectively. GN

---

GGGGGNNNNN

Answer 8

Hello,  I mean join as in relate the two tables together.

Kindly, GN

---

GGGGGNNNNN

Answer 9

Let's take small steps.

On the Create tab of the ribbon, click Query Design.

Add both tables, then close the Show Table dialog.

Drag the ID Number field from the first table and drop it onto the same field in the second table. This creates a join line between these fields.

Do the same for the First Name field, and also for the Last Name field.

Add some fields from both tables to the query grid.

Switch to datasheet view.

The query will display data from records that match on all three fields. You should be able to see the number of matching records at the bottom of the query window - it may take some time before the total number appears.

---

Regards, Hans Vogelaar

Answer 10

Hello Han,

Thank you for the response, I will do so and I will get back to you.

Many thanks,

GN

---

GGGGGNNNNN