Additional evaluation in query

Posted on 2011-10-24
Last Modified: 2012-05-12
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.
Question by:Frank Freese

    Author Comment

    by:Frank Freese
    could my overall query be too complex?

    Author Comment

    by:Frank Freese
    now access gives me "library has detected a fatal error in MSACCESS.ECE. and I get a call stack location msvcr90.dll 67031661()

    Author Comment

    by:Frank Freese
    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.
    LVL 74

    Accepted Solution

    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"...

    Author Comment

    by:Frank Freese
    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.
    LVL 19

    Assisted Solution

    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.


    Assisted Solution

    by:Frank Freese
    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.

    Author Closing Comment

    by:Frank Freese
    the value to this thread served only to reinforce a redesign

    Author Comment

    by:Frank Freese
    I did not plan to award any points to myself and thought that to be the case

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now