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.

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-----

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.

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.