Subquery Help

[Confused]

I'm trying to open a form and filter the records based on a value in
CboEmployees. The Where Clause is what I added to SQL Statement and it asks
me for the parameter when I try to run it instead of filtering based on the
value of CboEmployees. I had it set up to where I added
tblEmployeeAssignments and added EMployeeID to the query grid and added
[Forms]![CLECS2WContacts]![cboemployees]. This works but it pulls too many
duplicates, which is what I'm trying to avoid by using a subquery.

Here is the statement if anyone knows how to modify it to select the records
that match only that employee:

SELECT CLECS2.[CLEC Name], Contacts.[First Name], Contacts.[Last Name],
Contacts.EmailAddress, Contacts.[Send to?], Contacts.[CLEC ID],
Contacts.[Contact ID]
FROM CLECS2 RIGHT JOIN Contacts ON CLECS2.CLECID = Contacts.[CLEC ID] where
tblEmployeeAssignments.employeeID = Forms!CLECS2wContacts!cboemployees;

 

[Dirk Goldgar]

I'm trying to open a form and filter the records based on a value in
CboEmployees. The Where Clause is what I added to SQL Statement and it
asks
me for the parameter when I try to run it instead of filtering based on
the
value of CboEmployees. I had it set up to where I added
tblEmployeeAssignments and added EMployeeID to the query grid and added
[Forms]![CLECS2WContacts]![cboemployees]. This works but it pulls too
many
duplicates, which is what I'm trying to avoid by using a subquery.

Here is the statement if anyone knows how to modify it to select the
records
that match only that employee:

SELECT CLECS2.[CLEC Name], Contacts.[First Name], Contacts.[Last Name],
Contacts.EmailAddress, Contacts.[Send to?], Contacts.[CLEC ID],
Contacts.[Contact ID]
FROM CLECS2 RIGHT JOIN Contacts ON CLECS2.CLECID = Contacts.[CLEC ID]
where
tblEmployeeAssignments.employeeID = Forms!CLECS2wContacts!cboemployees;

What parameter are you being prompted for,
"tblEmployeeAssignments.employeeID"? That's what I'd expect, because
tblEmployeeAssignments isn't participating in that query at all. Which
field in CLECS2 or Contacts should match the EmployeeID selected in
cboEmployees?

 

[Confused]

Yes, EmployeeID. Your right TBLEmployeeAssignments is not added to query
grid. It was and I made it work as described. But the problem was that I
was getting so many duplicates. I then read some info about subqueries where
you could filter information when a table was not even included in the query,
if that makes sense and thought it might work to eliminate the duplicates.

Which

field in CLECS2 or Contacts should match the EmployeeID selected in
cboEmployees?

For this question I don't think there is a field per say that matches
EmployeeID in CLECS2 or Contacts. In tblEmployeeAssignments I have fields
CLECID, EmployeeID. In the forms CLECS2wContacts is a form/subform based on
tables CLECS2 and Contacts which are linked by CLECID. I'm wanting to open
a new form (that is based on CLECS2 and Contacts) after I select
CBOEmployeeName and have it show records that only match that employee.

Thanks

I'm trying to open a form and filter the records based on a value in
CboEmployees. The Where Clause is what I added to SQL Statement and it
asks
me for the parameter when I try to run it instead of filtering based on
the
value of CboEmployees. I had it set up to where I added
tblEmployeeAssignments and added EMployeeID to the query grid and added
[Forms]![CLECS2WContacts]![cboemployees] in the criteria. This works but it pulls too
many
duplicates, which is what I'm trying to avoid by using a subquery.

Here is the statement if anyone knows how to modify it to select the
records
that match only that employee:

SELECT CLECS2.[CLEC Name], Contacts.[First Name], Contacts.[Last Name],
Contacts.EmailAddress, Contacts.[Send to?], Contacts.[CLEC ID],
Contacts.[Contact ID]
FROM CLECS2 RIGHT JOIN Contacts ON CLECS2.CLECID = Contacts.[CLEC ID]
where
tblEmployeeAssignments.employeeID = Forms!CLECS2wContacts!cboemployees;

What parameter are you being prompted for,
"tblEmployeeAssignments.employeeID"? That's what I'd expect, because
tblEmployeeAssignments isn't participating in that query at all. Which
field in CLECS2 or Contacts should match the EmployeeID selected in
cboEmployees?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Yes, EmployeeID. Your right TBLEmployeeAssignments is not added to query
grid. It was and I made it work as described. But the problem was that I
was getting so many duplicates. I then read some info about subqueries
where
you could filter information when a table was not even included in the
query,
if that makes sense and thought it might work to eliminate the duplicates.

Which

For this question I don't think there is a field per say that matches
EmployeeID in CLECS2 or Contacts. In tblEmployeeAssignments I have
fields
CLECID, EmployeeID. In the forms CLECS2wContacts is a form/subform based
on
tables CLECS2 and Contacts which are linked by CLECID. I'm wanting to
open
a new form (that is based on CLECS2 and Contacts) after I select
CBOEmployeeName and have it show records that only match that employee.

Ah, okay, I guess I see now. So you need a WHERE clause that restricts the
records to only those with CLECIDs that appear in tblEmployeeAssignments
combined with the selected EmployeeID. If that's right, this SQL might
work:

SELECT
CLECS2.[CLEC Name],
Contacts.[First Name],
Contacts.[Last Name],
Contacts.EmailAddress,
Contacts.[Send to?],
Contacts.[CLEC ID],
Contacts.[Contact ID]
FROM
CLECS2
RIGHT JOIN
Contacts
ON CLECS2.CLECID = Contacts.[CLEC ID]
WHERE CLECS2.CLECID IN
(
SELECT CLECID FROM tblEmployeeAssignments
WHERE tblEmployeeAssignments.EmployeeID =
[Forms]![CLECS2wContacts]![cboEmployees]
)

Now, I don't know the relationships of these tables, or whether there would
be more than one record in tblEmployeeAssignments for a given employee
and/or CLECID. It could well be that the following query would work,
without any subquery:

SELECT
CLECS2.[CLEC Name],
Contacts.[First Name],
Contacts.[Last Name],
Contacts.EmailAddress,
Contacts.[Send to?],
Contacts.[CLEC ID],
Contacts.[Contact ID]
FROM
CLECS2
INNER JOIN
tblEmployeeAssignments
ON CLECS2.CLECID = tblEmployeeAssignments.CLECID
RIGHT JOIN
Contacts
ON CLECS2.CLECID = Contacts.[CLEC ID]
WHERE tblEmployeeAssignments.EmployeeID =
[Forms]![CLECS2wContacts]![cboEmployees]

However, that may be the same as the query you tried earlier that gave you
"too many duplicates". Still, it would be worth trying out.

 

[Confused]

Yes! First one worked. Thank you!

Yes, EmployeeID. Your right TBLEmployeeAssignments is not added to query
grid. It was and I made it work as described. But the problem was that I
was getting so many duplicates. I then read some info about subqueries
where
you could filter information when a table was not even included in the
query,
if that makes sense and thought it might work to eliminate the duplicates.

Which

For this question I don't think there is a field per say that matches
EmployeeID in CLECS2 or Contacts. In tblEmployeeAssignments I have
fields
CLECID, EmployeeID. In the forms CLECS2wContacts is a form/subform based
on
tables CLECS2 and Contacts which are linked by CLECID. I'm wanting to
open
a new form (that is based on CLECS2 and Contacts) after I select
CBOEmployeeName and have it show records that only match that employee.

Ah, okay, I guess I see now. So you need a WHERE clause that restricts the
records to only those with CLECIDs that appear in tblEmployeeAssignments
combined with the selected EmployeeID. If that's right, this SQL might
work:

SELECT
CLECS2.[CLEC Name],
Contacts.[First Name],
Contacts.[Last Name],
Contacts.EmailAddress,
Contacts.[Send to?],
Contacts.[CLEC ID],
Contacts.[Contact ID]
FROM
CLECS2
RIGHT JOIN
Contacts
ON CLECS2.CLECID = Contacts.[CLEC ID]
WHERE CLECS2.CLECID IN
(
SELECT CLECID FROM tblEmployeeAssignments
WHERE tblEmployeeAssignments.EmployeeID =
[Forms]![CLECS2wContacts]![cboEmployees]
)

Now, I don't know the relationships of these tables, or whether there would
be more than one record in tblEmployeeAssignments for a given employee
and/or CLECID. It could well be that the following query would work,
without any subquery:

SELECT
CLECS2.[CLEC Name],
Contacts.[First Name],
Contacts.[Last Name],
Contacts.EmailAddress,
Contacts.[Send to?],
Contacts.[CLEC ID],
Contacts.[Contact ID]
FROM
CLECS2
INNER JOIN
tblEmployeeAssignments
ON CLECS2.CLECID = tblEmployeeAssignments.CLECID
RIGHT JOIN
Contacts
ON CLECS2.CLECID = Contacts.[CLEC ID]
WHERE tblEmployeeAssignments.EmployeeID =
[Forms]![CLECS2wContacts]![cboEmployees]

However, that may be the same as the query you tried earlier that gave you
"too many duplicates". Still, it would be worth trying out.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)