Queries

[Samy]

I need to add 1 field from table 1 and merge it into another table. Both
these tables have 1 field in common (ID number).

How can i do this?

I have attempted to create a query but get stuck, as I am unsure. I want
data extracted from both tables so that I can choose the fields I require and
put them into a new table.

In my attempt (assuming I need to make a query for both tables) I get to the
'show table' dialogue box but this displays only one table for me to choose
from, from where I want data extracted!

I am using MS Access 2003

Please help, stuck and annoyed....

 

[OssieMac]

Hi Samy,

If both tables are in the same database then the Show Table dialog box will
show both tables. Select each one in turn and click Add and it will place
them in the top of the Query builder. You then close the Show table dialog
box.

To link the tables (If not already linked by the Id number) click the Id
number on one table and drag it to the Id number of the other table.

Drag just the required fields from either or both tables to the matrix.
Set any criteria in the criteria area. (If required)
Set Sorts (if required.)

If you want to create a separate new table from the query, click the little
drop down arrow against the toolbar button Query Type and select Make-Table
Query and follow the instructions from there.

To run the query, click Run (Red Exclamation mark).

 

[Scott Lichtenberg]

Samy,

If I understand you correctly, you have two tables that have a common field.
You have a field in one table, and you would like to update a field in the
second table with the value from the first table.

This is relatively easy to do, but before you do so, have you thought about
whether you really want to do this. Access is a relational database
product. It is designed to take data from two tables that can be linked on
a common field and display it all together. For instance, if you have an
employee table with SSN and Name fields, and a timeclock table with SSN and
working hours, you would not want to copy the name into the timeclock table.
Rather, you would create a select query that joins the two tables together
and shows the fields you want to see.

If you do need to copy data from one table to another, use an update query.
Show both tables and make sure you join them on the common fields. In the
query grid, select the target field (the one you want to update. On the
update line fill in the field that has the data. Use the following format:
[MyTable.MyField]. Then run your query.