MAKE TABLE QUERY

[jo.stanley]

I have two tables in a database, one holds 26,000 rows of data and the other holds 1,000. I want to run a make a table query by seraching for the data in the larger table that matchs the smaller table.

i.e. smaller table contains email addresses and i would like to extract the data from the larger table that have matching email addresses and put this data in a new table . Can you help with what I would need to do in the query. I have only ever run simple queries.

 

[bat17]

You should be able to do it the same as a simple query.
create a new query with the two tables in it. Join the tables by the Email field.
The goto Query>Make Table Query... and give a name for the table.
Hit the Exclamation mark on the toolbar to run it and make the table.

HTH

Peter

 

[Norie]

Start off by making a normal (select) query that gives you the correct result.

Go to design view and add the two tables.

Drag the field in the small table containing the e-mails on to the corresponding field in the large table.

Double click on the join between the two tables and chose
show all from small table and only those that match from larger table.

Now add the fields you want from the larger table.

Then goto Datasheet view and see if you are getting the result you want.

Then return to design view and goto Query>Make-Table Query...

You will then be prompted for the name for the new table.

Next save the query with a suitable name.

You can now run the query by double-clicking it.

To alter the query right click and goto Design.

The SQL for the Select Query would look something like this:

SELECT [Large Table].Email, [Large Table].Field1, [Large Table].Field2, [Large Table].Field3, [Large Table].Field4, [Large Table].Field5, [Large Table].Field6
FROM [Large Table] RIGHT JOIN [Small Table] ON [Large Table].Email = [Small Table].Email;

and for the Nake-Table Query

SELECT [Large Table].Email, [Large Table].Field1, [Large Table].Field2, [Large Table].Field3, [Large Table].Field4, [Large Table].Field5, [Large Table].Field6 INTO [Make New Table]
FROM [Large Table] RIGHT JOIN [Small Table] ON [Large Table].Email = [Small Table].Email;

 

[gbc123]

Jo,

Go into Queries. You want a new query. Probably best in design view.

Now add the two table you need then close the popup.

You'll need to link the common field together for the tables e.g reference <-> reference .. simply drag from one to the other with your left mouse button.

Hold down the CTRL key and select the fields you want to see on the output (from which you'll make a table). When all highlighted drag them with left mouse button into first empty box below. Repeat for the other table (you might only want email address from 2nd one).

Now select the "Query" menu and choose "Make Table". Give the table a name and choose the database to put it in.

Now if you run your query the table will be created. It will overwrite each time you run it. If you just want to see the records first, change it back to a "select" query, then back to "make table" when you are happy with the results.

Hope this helps... :wink:

Gary.