<> not working correctly in query, unable to separate data

[SCC]

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.

 

[Banana]

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 year<>2009"

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 "Year<>2009" 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]

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.

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 year<>2009"

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 "Year<>2009" 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

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.