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
|
|||
|
|||
not working correctly in query, unable to separate data
I have two tables. The Contact table contains has a single record for each
employee: first name, last name, phone, etc. The SemesterWorked table has multiple entries for each employee since it lists the semester and year work: ID Semester Year smith fall 2009 smith spring 2009 young spring 2009 young fall 2008 I want to create reports for current employees and former employees. I've been able to create a query for current employees with semester="fall" and year="2009. I can't extract the records for former employees. A query with semester"fall" and year"2009" returns no data. When I used a calculated field of Currently_Employed: IIf([tblSemesterWorked]![Semester]="Fall" And [tblSemesterWorked]![Year_Worked]="2009","Yes","No") I have multiple results, one for each record in the SemesterWorked table. I would think this would be a simple query but I am having problems. I have little experience with Access so I am looking for something simple. Thanks. |
#2
|
|||
|
|||
not working correctly in query, unable to separate data
SCC wrote:
I have two tables. The Contact table contains has a single record for each employee: first name, last name, phone, etc. The SemesterWorked table has multiple entries for each employee since it lists the semester and year work: ID Semester Year smith fall 2009 smith spring 2009 young spring 2009 young fall 2008 I want to create reports for current employees and former employees. I've been able to create a query for current employees with semester="fall" and year="2009. I can't extract the records for former employees. A query with semester"fall" and year"2009" returns no data. When I used a calculated field of Currently_Employed: IIf([tblSemesterWorked]![Semester]="Fall" And [tblSemesterWorked]![Year_Worked]="2009","Yes","No") I have multiple results, one for each record in the SemesterWorked table. I would think this would be a simple query but I am having problems. I have little experience with Access so I am looking for something simple. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hopefully, the data type for [Year] is an Integer. Possibly this for current: SELECT C.ID, S.Semester, S.[Year] FROM Contact As C INNER JOIN SemesterWorked As S ON C.ID = S.ID WHERE S.Semester = "Fall" AND S.[Year] = 2009 and this for past: SELECT C.ID, S.Semester, S.[Year] FROM Contact As C INNER JOIN SemesterWorked As S ON C.ID = S.ID WHERE S.Semester "Fall" AND S.[Year] 2009 -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) ** Respond only to this newsgroup. I DO NOT respond to emails ** -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBSrEeTYechKqOuFEgEQJMhgCdFi4yn/wcWbdhBWNwScM7DII7sVYAn1sl wCUfZJjZ4D2tOC7Cdw0H1kZG =Qiov -----END PGP SIGNATURE----- |
#3
|
|||
|
|||
not working correctly in query, unable to separate data
Since you want all records from semester/year *before* Fall 2009, it
would mean your result should be just 3 row; smith spring 2009 young spring 2009 young fall 2008 But your criteria is "semester'Fall' AND year2009" Since Young worked in Fall 2008, this fails the test "semester'Fall'" even though the year portion succeed. On same token, Smith and Young worked on Spring 2009, which fails the "Year2009" test. AND operator requires both operand to evaluate to true to return a true result. Thus you got no results. See if this works: WHERE NOT(semester="Fall" AND year=2009) OR Year 2009 This will exclude the Fall 2009 and because we're evaluating the NOT outside of AND, it will succeed. The OR is there to allow you to include Spring 2009. That said, a concern. Is your column actually named Year? You may have problems becuase Year is a reserved word and Access may confuse your column "Year" for a function "Year" HTH SCC wrote: I have two tables. The Contact table contains has a single record for each employee: first name, last name, phone, etc. The SemesterWorked table has multiple entries for each employee since it lists the semester and year work: ID Semester Year smith fall 2009 smith spring 2009 young spring 2009 young fall 2008 I want to create reports for current employees and former employees. I've been able to create a query for current employees with semester="fall" and year="2009. I can't extract the records for former employees. A query with semester"fall" and year"2009" returns no data. When I used a calculated field of Currently_Employed: IIf([tblSemesterWorked]![Semester]="Fall" And [tblSemesterWorked]![Year_Worked]="2009","Yes","No") I have multiple results, one for each record in the SemesterWorked table. I would think this would be a simple query but I am having problems. I have little experience with Access so I am looking for something simple. Thanks. |
#4
|
|||
|
|||
not working correctly in query, unable to separate data
The field name is YearWorked. Since I work for a college we do not use a
calendar year. I combined the fields into Fall2009, Spring2010, etc. I know what you are now talking about regrading inner join. At this time I need something simple. I changed the query to use only the contact table. I'll have to wait to use fields from both tables in a query when I'm more familiar with Access. Thanks for your help. "Banana" wrote: Since you want all records from semester/year *before* Fall 2009, it would mean your result should be just 3 row; smith spring 2009 young spring 2009 young fall 2008 But your criteria is "semester'Fall' AND year2009" Since Young worked in Fall 2008, this fails the test "semester'Fall'" even though the year portion succeed. On same token, Smith and Young worked on Spring 2009, which fails the "Year2009" test. AND operator requires both operand to evaluate to true to return a true result. Thus you got no results. See if this works: WHERE NOT(semester="Fall" AND year=2009) OR Year 2009 This will exclude the Fall 2009 and because we're evaluating the NOT outside of AND, it will succeed. The OR is there to allow you to include Spring 2009. That said, a concern. Is your column actually named Year? You may have problems becuase Year is a reserved word and Access may confuse your column "Year" for a function "Year" HTH SCC wrote: I have two tables. The Contact table contains has a single record for each employee: first name, last name, phone, etc. The SemesterWorked table has multiple entries for each employee since it lists the semester and year work: ID Semester Year smith fall 2009 smith spring 2009 young spring 2009 young fall 2008 I want to create reports for current employees and former employees. I've been able to create a query for current employees with semester="fall" and year="2009. I can't extract the records for former employees. A query with semester"fall" and year"2009" returns no data. When I used a calculated field of Currently_Employed: IIf([tblSemesterWorked]![Semester]="Fall" And [tblSemesterWorked]![Year_Worked]="2009","Yes","No") I have multiple results, one for each record in the SemesterWorked table. I would think this would be a simple query but I am having problems. I have little experience with Access so I am looking for something simple. Thanks. |
Thread Tools | |
Display Modes | |
|
|