Joining on two fields

[MikeRBS]

I have two tables with names - Firstname and Surname. If I do the following:

SELECT T1.Surname,
People.Surname,
T1.Forename,
People.Firstname
FROM T1 RIGHT JOIN People ON ((T1.Surname = People.Surname) AND (T1.Forename = People.Firstname))

..it runs alright but says it "can't represent the join in Design View". Is there a way to do two-field joins within QBE? The reason is I want to do updating and appending and I don't want to have to write raw SQL unnecessarily.

 

[KenReay]

Hi

In the query designer, just drag between both fields, is that what you mean?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MikeRBS]

If you try to drag two fields, it gives you a not allowed mouse pointer. If you do the two individually, it complains about ambiguous joins. If you do in it SQL, QBE says it can't deal with it.

 

[KenReay]

hi

different col names, same logic

SELECT tblExpensesItems.*, tblProperties.*
FROM tblExpensesItems INNER JOIN tblProperties ON (tblExpensesItems.strHouseNumber = tblProperties.strHouseNumber) AND (tblExpensesItems.strContract = tblProperties.strContract);


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MikeRBS]

Hi Ken.

Does your SQL get accepted by QBE? If so it must be the outer join...

 

[KenReay]

Hi


Yes, I got the SQL by switching from QBE view to SQL view, as you say it is almost certainly the use of the outer join

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK