Query help

  • Thread starter Maurice Samueks
  • Start date
M

Maurice Samueks

I was wondering I have created 2 tables and I want to compare the
tables but only list the data that they have in common from 2 fields.
So each table has 2 fields if I sort them in a order I want the query
to take the first row and table the field1 and field2 and compare it to
table2 field1 and field2 at the same time if it finds a match then
store it in a table. if it does not then go to the next row.
 
K

Ken Snell [MVP]

Can you tell us the table structure? and some examples of the data? and what
the result should be based on the sample data?
 
M

Maurice Samueks

ok the 2 columns in the table are very similar to the first 6 digits of
a telephone number including your area and this is the same for both
tables what I want is the exact matches of what 6 numbers that both
tables have in common.
 
K

Ken Snell [MVP]

I still don't know if I'm following you, but here is a generic SQL statement
that joins two tables on two fields and returns records from both tables
where the data match:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1 AND
Table1.Field2 = Table2.Field2;
 
J

John Vinson

I was wondering I have created 2 tables and I want to compare the
tables but only list the data that they have in common from 2 fields.
So each table has 2 fields if I sort them in a order I want the query
to take the first row and table the field1 and field2 and compare it to
table2 field1 and field2 at the same time if it finds a match then
store it in a table. if it does not then go to the next row.
Click to expand...

No sorting and no step-through are necessary. That's not how queries
work! Access (like other relational databases) works with "sets" of
data, and lets you join tables using Queries.

Just create a Query by adding the two tables to the query design
window. Drag the first joining field from one table to the
corresponding field in the other table, then drag the second in the
same way. Select whichever fields you want to see from the two tables.

Open the query as a datasheet by clicking the leftmost icon in the
toolbar.

It's probably neither necessary nor appropriate to store the result
redundantly in another table; this query can be used as the source for
a Form or Report, or can be exported. If you do need to store the data
(again, redundantly, usually a Very Bad Idea) you can change the query
to a Make Table query using the query type icon.


John W. Vinson[MVP]
 
M

Maurice Samueks

i think that might work, can I type that statement into the sql view of
access.
 
K

Ken Snell [MVP]

Yes, but you will need to change the table and field names to the correct
ones.
 
Top