Finding matching records

  • Thread starter Thread starter SeRene
  • Start date Start date
S

SeRene

Hi, I notice that Access Queries allow me to find
unmatched records.
However, i need to find matching fields. Not unmatched
records.

I need to match a field in one table against another field
in another table.
Is there any way i can do this??

I need help!!
Thanks lotZzzzZZz.
 
SeRene,

SELECT M1.*
,M2.*
FROM MyTable1 AS M1
INNER JOIN
MyTable2 AS M2
ON M1.MatchingColumn = M2.MatchingColumn

On your own Query, alter the table names and the column names from this
example as needed, and trim down the SELECT clause to project only the
columns you need (instead of all of them from both tables, as I've done here
generically)


Sincerely,

Chris O.
 
POSTING NOTE: I posted this, received it back in my newsreader, and selected
it. It returned "message no longer available". Then, the post vanished.
So I've sent it again. My apologies to those who receive it twice.


SeRene,

SELECT M1.*
,M2.*
FROM MyTable1 AS M1
INNER JOIN
MyTable2 AS M2
ON M1.MatchingColumn = M2.MatchingColumn

On your own Query, alter the table names and the column names from this
example as needed, and trim down the SELECT clause to project only the
columns you need (instead of all of them from both tables, as I've done here
generically)


Sincerely,

Chris O.
 
Given:
Table1
Col1 (key)
Col2

Table2
Col1 (key)
Col2 (FKey matches key in Table1)
Col3

SELECT T1.Col2, T2.Col3
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 = T1.Col1
 
Given:
Table1
Col1 (key)
Col2

Table2
Col1 (key)
Col2 (FKey matches key in Table1)
Col3

SELECT T1.Col2, T2.Col3
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 = T1.Col
 
Given:
Table1
Col1 (key)
Col2

Table2
Col1 (key)
Col2 (FKey matches key in Table1)
Col3

SELECT T1.Col2, T2.Col3
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 = T1.Col
 
Given:
Table1
Col1 (key)
Col2

Table2
Col1 (key)
Col2 (FKey matches key in Table1)
Col3

SELECT T1.Col2, T2.Col3
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 = T1.Col
 
Given:
Table1
Col1 (key)
Col2

Table2
Col1 (key)
Col2 (FKey matches key in Table1)
Col3

SELECT T1.Col2, T2.Col3
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 = T1.Col
 
Hi Chris,

Thanks for the help!
However, i cant seem to get the SQL statement to work.

SELECT M1.*
,M2.*
FROM MyTable1 AS M1
INNER JOIN
MyTable2 AS M2
ON M1.MatchingColumn = M2.MatchingColumn

I've replaced M1 and M2 as the fields which i want to
match and MyTable1 and 2 to the 2 different tables.
Did i do it correctly??
Sorry for i'm not so IT-savvy!! n tHanks againnnnn
 
SeRene,

M1 & M2 are not field names. They are aliases for the tables named in the
FROM clause. They "substitute" for the full-table-name.column-name
qualified name convention that makes SQL code such a mess to read.

Instead of typing MyTable1.MatchingColumn, we type M1.MatchingColumn.

M1.* is a testing and example-only shortcut that means "show all columns
from the table", saving you from having to write them out (but only in
testing and examples). I mentioned replacing them with an exact list of the
actual columns you plan to use in your query.

Simplified: (Show the ShippingDate for every Product ever ordered).

SELECT P1.ProductName
,O1.ShippingDate
FROM Products AS P1
INNER JOIN
Orders AS O1
ON P1.ProductID = O1.ProductID


Or, a bit more complicated
(Show products that haven't been shipped)

SELECT P1.ProductName
,O1.PromisedShippingDate
,Date() - 01.PromisedShippingDate AS DaysOverdue
FROM Products AS P1
INNER JOIN
Orders AS O1
ON P1.ProductID = O1.ProductID
WHERE O1.PromisedShippingDate < Date()
 
Hi, i've tried using the SQL statement you gave. However,
i couldnt get the results i wanted. I think i must have
went wrong somewhere.

For Table1, i only have 1 col which i need to match
against Table2
Therefore, my Col2 in Table1 is empty.

For Table2, i only need to show 1 col too. Therefore, for
Table2, i've entered the col i entered in Col1 of Table1,
and for Col2, i've entered the col which i want to match
against Col1 of Table1.

Your Select statement did include Col3. So what can i use
to replace Col3? Or is it not necessary to include Col3?
 
Back
Top