• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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 Freese
Frank Freese
  • 7
3 Solutions
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()
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Jeffrey CoachmanCommented:
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:
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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now