INNER JOIN with AND?

[Drew]

I have a situation I need to overcome, I am working on a records database
for the facility I work for. Each Client has a register number assigned to
them, although now they have changed, instead of going through a great deal
of problems changing each one, I added a column in the main information
database (CliCore) called OldRegNo, I want to now use this column to link to
the old numbers, like this,

RegNo FName LName OldRegNo
100150 John Doe 223

Now I need to edit 1 report so that it pulls everything for 100150 and 223.
How can I do this?

I have tried,

SELECT fields,fields,fields,
FROM table t1 INNER JOIN table2 t2 ON t1.RegNo = t2.RegNo AND t1.OldRegNo =
t2.RegNo.

and it doesn't pull any records, although there are records in the database
that meet that criteria.

Thanks,
Drew

 

[Ken Snell [MVP]]

What you are asking the query to do is to return records where RegNo and
OldRegNo are the same number.

If you want to return records based on either the value of RegNo or the
value of OldRegNo, you can use the OR operator in the join clause, although
you wont' be able to "see" this query in the QBE window (design view) of
ACCESS:

SELECT fields,fields,fields,
FROM table t1 INNER JOIN table2 t2
ON t1.RegNo = t2.RegNo OR t1.OldRegNo = t2.RegNo.;

An alternative method might be this:

SELECT fields,fields,fields,
FROM table t1, table2 t2
WHERE t1.RegNo = t2.RegNo OR t1.OldRegNo = t2.RegNo.;

 

[Drew]

Thanks for your response. I have done this, and it works! Now I am having
a problem with one of my reports, but I will try to nail that down.

Thanks,
Drew