Additional evaluation in query

This query worked because I had a 4 or 5 outstanding.
AreaSM: (Select [tblEmployee.EmployeeID] 
 FROM tblEmployee INNER JOIN
ON tblEmployee.EmployeeID = 
Where tblDesignatedEmployee.StoreID
 = tblStore.StoreNumberID 
And tblDesignatedEmployee.EmployeeTypeID In (4,5)) 

Open in new window

However, that may not always be the case. Right now I do have this condition exisiting. But what about when this is not true. For example, today Store # 1 has a District Manager. Tomorrow they may not. When that happens I need to designated a District Manager type of 5. Later on the store is assigned a DM. I changed the above query to read (1,5) and it gave me this error:
"An unhandled win32 exception occurred in MSAccess.EXE [6064]
and Aceess quit.
Frank FreeseAsked:
Who is Participating?
Jeffrey CoachmanMIS LiasonCommented:
This is why I suggested you first re-evaluate the design,...
(Dump the lookup fields, ...etc)
Things like this just keep requiring more and more workarounds, (and get more and more complex) for each new "Condition"...
Frank FreeseAuthor Commented:
could my overall query be too complex?
Frank FreeseAuthor Commented:
now access gives me "library has detected a fatal error in MSACCESS.ECE. and I get a call stack location msvcr90.dll 67031661()
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Frank FreeseAuthor Commented:
my error is gone
now, when I changed my query to read
And tblDesignatedEmployee.EmployeeTypeID In (1,5))
I get this message:
At most one record can be returned to this subquery.
Frank FreeseAuthor Commented:
ok...let's say I dump the lookups. I can live with that. then what type of design do I move to? My initial design was different and then it was suggested to changed to what I am doing now. In the beginning my tblDesignatedEmployee had fields for Store, District Manager, Area Support Manager and Assistant Store Manger as a record. Options appreciated - suggestions welcomed.
I may suggest not to use Select Statement to display a value in a field within a query. It will make your query unstable and complicated.

It would be good if you can find any other method/solution rather the one you started.

Frank FreeseAuthor Commented:
I'm in the process of considering other designs. In some ways, I wish I never changed my orginal design - but that was yesterdays. Thanks....I think I'll close this thread down.
Frank FreeseAuthor Commented:
the value to this thread served only to reinforce a redesign
Frank FreeseAuthor Commented:
I did not plan to award any points to myself and thought that to be the case
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.