beginner help

[dieselx]

If someone can guide me in the right direction it would be great. I am currently trying to merge two "student" tables.

Table 1
-DOB
-First 3 initials of last name
-First 2 intitlas of first name
-Other data

Table 2
-DOB
-First Name
-Last Name
-Other data

thanks,

Phillip

 

[Joe4]

In Access's help, take a look at "Create an append query". This will show you how to append the records from one table to another via a query.

 

[dieselx]

I think i didn't explain myself very good. My problem is in joining the tables. I need an sql statement that can match the DOB from the two tables then match the initials of the first and last names to get an accurate report.

Table 1
First Name
Last Name
DOB
Address
Phone

Table 2
First 3 initials of last Name
First 2 initials of First Name
DOB
Start Date
End Date

Goal: To have a table with the First Name, Last Name, DOB, Start Date, and End Date

 

[Joe4]

You don't need to know SQL, Access will do it for you. All you have to do is create a simple query.

Open up a query in Design View and select your two table. Then create a link between the two DOB fields by clicking on one and dragging over to the other one and releasing the mouse button. Now these two tables are linked together and you can select which fields you want to display from the tables in your resulting query.

You may want to consider picking up a beginning Access book to learn more about queries. They are kind of the driving force behind Access, and its most powerful tool.

 

[dieselx]

thanks JM for your responses. The problem is their can be more then one "student" withe the same date of birth (DOB). So I also need to match up the initials from one table with the full name in the other table.

 

[Joe4]

Oh sorry, I missed that part of your post. Here is what you do.

-Create a new query based on Table1.
-Display all your fields in this query.
-Add a calculated field to return the first three characters of the last name, something like:
LastName2:Left([LastName],3)
-Likewise, add a calculated field for the first name, i.e.
FirstName2:Left([FirstName],2)
-Save your query.

Now, do another query between the query you just created above and Table2. Now you can link them based on the calculated fields in the query with the corresponding fields in Table2. You can also link on DOB, so you will have a total of three relationships.

HTH!

 

[dieselx]

great thanks....thats what I was looking for.