Query too complex

[quonzilla]

i have created a table that has employee data and assignments. an employee
may have a single assignment and some may have over a hundred. each
assignment is a field. when i run a query to tell me an employee's name and
all of their assignments, i get a message that reads "Query too complex".
the formula that i am using to yield the info is "Is Not Null" in a different
record line using the "OR" coding.
does anyone know why it is telling me that the query is too complex. to me
it seems like an easy query, but i am a newb at Access and am probably
writing the query wrong.
thanks in advance.

 

[Jason Lepack]

You're new, so you will get a lesson...

You have created a spreadsheet, not a database.

You have a many-to-many relationship between employees and assignments
based on this:
"an employee may have a single assignment and some may have over a
hundred"

So you should have one table that describes employees and a table that
describes assignments.
You will then need a third table that links the two tables together.

Employees:
employeeID - autonumber
first_name
last_name
etc.

Assignments:
assignmentID - autonumber
description
location
etc.

employee_assignments:
assignmentID
employeeID
start_date
end_date

*Note: the next part contatins SQL. You can insert it by going into
Query Designer and choosing SQL View.

Then if you wanted a list of assignments for a given employee you
would use this statement:
SELECT
a.description,
a.location,
ea.start_date,
ea.end_date
FROM
employees AS e
JOIN employee_assignments AS ea
ON ea.employeeID = e.employeeID
JOIN assignments AS a
ON a.assignmentID = ea.assignmentID
WHERE
e.firstName = [First Name]
AND e.lastName = [Last Name]

Cheers,
Jason Lepack