• 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
 tblDesignatedEmployee  
ON tblEmployee.EmployeeID = 
tblDesignatedEmployee.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.
0
Frank Freese
Asked:
Frank Freese
  • 7
3 Solutions
 
Frank FreeseAuthor Commented:
could my overall query be too complex?
0
 
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()
0
 
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.
0
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"...
0
 
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.
0
 
MINDSUPERBCommented:
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.

Sincerely,
Ed
0
 
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.
0
 
Frank FreeseAuthor Commented:
the value to this thread served only to reinforce a redesign
0
 
Frank FreeseAuthor Commented:
I did not plan to award any points to myself and thought that to be the case
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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