If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subquery Help
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; |
#2
|
|||
|
|||
Subquery Help
"Confused" wrote in message
... 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? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
|
|||
|
|||
Subquery Help
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 "Dirk Goldgar" wrote: "Confused" wrote in message ... 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) |
#4
|
|||
|
|||
Subquery Help
"Confused" wrote in message
... 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. 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) |
#5
|
|||
|
|||
Subquery Help
Yes! First one worked. Thank you!
"Dirk Goldgar" wrote: "Confused" wrote in message ... 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. 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) |
Thread Tools | |
Display Modes | |
|
|