Help with SQL

  • Thread starter Thread starter TeeSee
  • Start date Start date
T

TeeSee

The following SQL is in the RowSource of a combo. The majority seems
to work (I get all contacts in the table) until I add the WHERE clause
in order to limit the contacts to only those that are JOINED to the
Company by way of one of my new JOIN tables.

SELECT tblContacts.pkContactID, tblContacts.txtFirstName,
tblContacts.txtLastName, tblCompanyContacts.fpkContactID,
tblCompanyContacts.fpkCompanyID
FROM tblContacts
INNER JOIN tblCompanyContacts
ON tblContacts.pkContactID=tblCompanyContacts.fpkContactID
WHERE tblCompanyContacts.fpkCompanyID=tblProjectCompany.fpkCompanyID

frmProjectCompany is parent to frmCompanyContacts

If someone could steer me in the right direction I'd sure appreciate
it.
 
You cannot use tblProjectCompany.fpkCompanyID in the SQL statement because
the table tblProjectCompany is not one of the tables in the FROM clause.

Try this instead:

SELECT tblContacts.pkContactID, tblContacts.txtFirstName,
tblContacts.txtLastName, tblCompanyContacts.fpkContactID,
tblCompanyContacts.fpkCompanyID
FROM (tblContacts
INNER JOIN tblCompanyContacts
ON tblContacts.pkContactID=tblCompanyContacts.fpkContactID)
INNER JOIN tblProjectCompany ON
tblCompanyContacts.fpkCompanyID=tblProjectCompany.fpkCompanyID;
 
You cannot use tblProjectCompany.fpkCompanyID in the SQL statement because
the table tblProjectCompany is not one of the tables in the FROM clause.

Try this instead:

SELECT tblContacts.pkContactID, tblContacts.txtFirstName,
tblContacts.txtLastName, tblCompanyContacts.fpkContactID,
tblCompanyContacts.fpkCompanyID
FROM (tblContacts
INNER JOIN tblCompanyContacts
ON tblContacts.pkContactID=tblCompanyContacts.fpkContactID)
INNER JOIN tblProjectCompany ON
tblCompanyContacts.fpkCompanyID=tblProjectCompany.fpkCompanyID;

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/









- Show quoted text -
Click to expand...

Thanks Ken .... I know I don't really have to tell you that that did
the trick but just one or more further question(s) on the same
topic ........

I was trying to use the WHERE clause .... If I had been using WHERE
something AND something would I just add another INNER JOIN if it was
another table that was not in the FROM clause.
If that is the case then is the WHERE AND clause(s) used only when
there is a more simple SQL based on one table? Or does it DEPEND?
 
WHERE clause can be used for two different reasons:

1) As a replacement for an INNER JOIN between two tables; instead of joining
two tables, you do a CROSS JOIN between the two tables and put the "join" in
the WHERE clause.

2) As a filter for one or more of the tables so that the returned data set
includes/excludes records based on the WHERE clause.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


You cannot use tblProjectCompany.fpkCompanyID in the SQL statement because
the table tblProjectCompany is not one of the tables in the FROM clause.

Try this instead:

SELECT tblContacts.pkContactID, tblContacts.txtFirstName,
tblContacts.txtLastName, tblCompanyContacts.fpkContactID,
tblCompanyContacts.fpkCompanyID
FROM (tblContacts
INNER JOIN tblCompanyContacts
ON tblContacts.pkContactID=tblCompanyContacts.fpkContactID)
INNER JOIN tblProjectCompany ON
tblCompanyContacts.fpkCompanyID=tblProjectCompany.fpkCompanyID;

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/









- Show quoted text -
Click to expand...

Thanks Ken .... I know I don't really have to tell you that that did
the trick but just one or more further question(s) on the same
topic ........

I was trying to use the WHERE clause .... If I had been using WHERE
something AND something would I just add another INNER JOIN if it was
another table that was not in the FROM clause.
If that is the case then is the WHERE AND clause(s) used only when
there is a more simple SQL based on one table? Or does it DEPEND?
 
Back
Top