?
Solved

Problem with query

Posted on 2011-10-21
16
Medium Priority
?
229 Views
Last Modified: 2012-06-27
Experts,
In my tblEmployeeType we can have multiple different types of employees. Stores are designated different types of employees, e.g. District Manager, Area Support Manager, Store Manager and Assistant store manger. My query belows relates to one type, Area Support Manager. I need to evaluate such when a store has an Area Manager = 4 disregard the employee type 5 which represents unassigned. Right now I'm only looking a 5, none assigned.  I tried incorporating a or but that failed.
AreaSupportManager: (Select [tblEmployee.EmployeeFirstName]
&" "&[tblEmployee.EmployeeLastName] AS ASMName
FROM tblEmployee INNER JOIN tblDesignatedEmployee  
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=5)

Open in new window


===========
Prior related question: http:/Q_27392794.html
0
Comment
Question by:Frank Freese
  • 7
  • 6
  • 2
15 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 37008603
Maybe adding a NOT EXISTS:

AreaSupportManager: (Select [tblEmployee.EmployeeFirstName]
&" "&[tblEmployee.EmployeeLastName] AS ASMName
FROM tblEmployee INNER JOIN tblDesignatedEmployee  
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=5
And Not Exists (
   Select 1 From tblDesignatedEmployee lkup
   Where lkup.StoreID = tblDesignatedEmployee.StoreID
   And lkup.EmployeeTypeID = 4
)
)

Basically, ensure that the same store ID does not have a record with EmployeeTypeID = 4.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37009014
Perhaps it would be simpler if you posted a DB with some sample data, then create a simple table of the expected output (based on the sample data)
0
 

Author Comment

by:Frank Freese
ID: 37011822
I was not aware of the automatic link - thanks and I'll pay more attention to that going forward.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Frank Freese
ID: 37011879
StoreBonus.mdb
The attached db will open to the form in question.
Start by:
Select Month: September
Select Year: 2011:
Select Store: # 2
Note that in the Area Support Manager text box there is a None and that is correct becuase there is no Area Support Manager assigned to that store
Repeat the above process but select store # 7. In the Area Support Manager text box it is blank becuase this store does have someone assigned to it as an Area Support Manager.
Therefore, two conditions can exists for a store: An Area Support Manager of none = 5 or Area Support Manager (=4) as employee type.

Although I am applying this only to the Area Support Manager, the same condition can exist for a store at the District Manager, Store Manage or Assistant Manager level. I did not ask for that solution also since I was going to use the solution provided for Area Support Manager and incorporate that into the other three myself. I didn't see a need to ask the experts to do everything, and besides, I learn more that way.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37014051
There are a lot of confusing aspects to this design.

1. Please try to avoid using Lookup Fields in your tables.
See here:
http://access.mvps.org/access/lookupfields.htm.
Another reason is that, no other database system uses these fields, so when it comes time to upgrade you will be forced to remove them anyway.
Use lookups in your *Forms* only where there is also a wizard to help create them.

2. <Therefore, two conditions can exists for a store: An Area Support Manager of none = 5 or Area Support Manager (=4) as employee type.>
It is not inherently clear what the exacts value is (probably because of the lookup fields)
The only thing that is clear to me is that:
"two conditions can exists for a store: None, and BLANK

3. In your "Lookup" there are actually 3 "None" values.
Can you explain how this came to be? (are you saying that within "None" there are 3 distinct values?)

4. Finally, it is not clear what you one direct question is...?
<when a store has an Area Manager = 4 disregard the employee type 5 which represents unassigned.>

I'm sure an Experts can find a quick "Workarond" for this, but the the underlying design issues will still be present.


JeffCoachman
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 37014163
Jeff,

I did not look at the Access database and defer to someone like you on that front. Therefore, I am pretty sure I would agree with you on the design. As far as the question, though, does it not sound to you as if there can be multiple records by store ID -- 1 tied to the area manager (=4) and 1 to unassigned (=5) -- so do you not think http:#37008603 answers this, i.e., check for not existence of the former when selecting rows for the latter ??

Just making sure I am not missing something that in your Access expertise will not work.

Respectfully yours,

Kevin
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 37014211
Okay, I tested in the database myself. In the sample data there were no instances of 'None Assigned' and 'Area Support Manager'; therefore, I added a row for storeID = 4 into tblDesignatedEmployee with Lillie as employee and 'Area Support Manager' as role, leaving the original record with 'None Assigned'.

Running this query:
Select [tblEmployee].[EmployeeFirstName]
&" "&[tblEmployee].[EmployeeLastName] AS ASMName
FROM tblEmployee INNER JOIN tblDesignatedEmployee  
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = 4
And tblDesignatedEmployee.EmployeeTypeID=5
;

Open in new window

I get ASMName = 'None Assigned'

My understanding is I should not, since Lillie is assigned as the 'Area Support Manager'; therefore, I then ran with my query:
Select [tblEmployee].[EmployeeFirstName]
&" "&[tblEmployee].[EmployeeLastName] AS ASMName
FROM tblEmployee INNER JOIN tblDesignatedEmployee  
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = 4
And tblDesignatedEmployee.EmployeeTypeID=5
And Not Exists (
   Select 1 From tblDesignatedEmployee lkup
   Where lkup.StoreID = tblDesignatedEmployee.StoreID
   And lkup.EmployeeTypeID = 4
)
;

Open in new window

And there are no results. The 'None Assigned' is successfully filtered. If instead, you want to find the rows of 'None Assigned' that need to be deleted because there is now an ASM, then you would just change Not Exists to Exists.

So in short, the solution is to add this code to what you already have:
And Not Exists (
   Select 1 From tblDesignatedEmployee lkup
   Where lkup.StoreID = tblDesignatedEmployee.StoreID
   And lkup.EmployeeTypeID = 4
)

Open in new window


Which makes the final subquery from your original look like this:
AreaSupportManager: (Select [tblEmployee].[EmployeeFirstName]
&" "&[tblEmployee].[EmployeeLastName] AS ASMName
FROM tblEmployee INNER JOIN tblDesignatedEmployee  
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=5
And Not Exists (
   Select 1 From tblDesignatedEmployee lkup
   Where lkup.StoreID = tblDesignatedEmployee.StoreID
   And lkup.EmployeeTypeID = 4
))

Open in new window


Hope that helps!

Kevin
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 37014232
I forgot to attach the database. Here is what I tested with. I updated your original query based on what I stated above. The second row illustrates my added code working. It is a bit strange you are not just querying for =4 for ASM and then using NZ((query goes here), 'None Assigned') but that is a different discussion.
StoreBonus.mdb
0
 

Author Comment

by:Frank Freese
ID: 37014747
the two conditions that can exist is 5 =  "None Assigned" as an Area Store Manager or 4 = Area Support Manager.
This code work when there is no on assigned as an Area Manager:
And tblDesignatedEmployee.EmployeeTypeID=5
And Not Exists (
   Select 1 From tblDesignatedEmployee lkup
   Where lkup.StoreID = tblDesignatedEmployee.StoreID
   And lkup.EmployeeTypeID = 4
)

Open in new window

This code work when there is an Area Manager Assigned:
And tblDesignatedEmployee.EmployeeTypeID=4
And Not Exists (
   Select 1 From tblDesignatedEmployee lkup
   Where lkup.StoreID = tblDesignatedEmployee.StoreID
   And lkup.EmployeeTypeID = 5

Open in new window

I tried to use OR but that it not work.  Now, can we combine the two somehow?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 37014785
I am sorry, combine WHY? i.e., what are you trying to do with that exactly -- please provide example with data. As I said, you have the choice also to do one query where tblDesignatedEmployee.EmployeeTypeID=4 and then wrap that with NZ() so that if the result is NULL, you display 'None Assigned'. That is actually better than having a row to simply be unassigned anyway.
0
 

Author Comment

by:Frank Freese
ID: 37014923
mwvisa1:
Let's see if this helps. I've attached a word document that shows the sql and output for when EmployeeTypeID = 5 and then the sql and output when the EmployeeID = 4. I've tried to use if statements but fall flat on my face. If EmployeeTypeID = 4 or 5 show data
If we can fill in the blanks for the Column Area Support Manager then that's my objective. Thanks for hanging in. I am very grateful.
Area-Support-Manager.docx
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 37015344
But if your goal is to show the area sales manager, then you should not care if there is an unassigned row or not, right? In other words, if a row exists with EmployeeTypeID = 4, does there really need to be a check to see if there does not exist a row with EmployeeTypeID = 5.

This seems like it would be the simplest in my humble opinion:
AreaSupportManager: NZ((Select [tblEmployee].[EmployeeFirstName]
&" "&[tblEmployee].[EmployeeLastName] AS ASMName
FROM tblEmployee INNER JOIN tblDesignatedEmployee  
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=4), "None Assigned")

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 37015522
Hey there....that got it. I can now carry the same logic to the other three employee types: District Manager, Store Manager and Assistant Store Manager.
This addressed this question - - well done. However, it introduced another problem and I would like to open a follow-up question for points. Can I do this, and if so, how?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 37015533
You most certainly can. What you would do is select the correct response(s) in this thread. Once done, you should see an ask related question link right above the comment box. This will allow you to ask a new question based on this thread. It will provide a link to familiarize new participants, gaining you fresh insight, as well as send e-mails to those of us participating here, increasing your chance of someone familiar with your database responded also. Best of both worlds for you.

We will look out for that. For now, I am glad we got you working. Best regards and happy coding,

Kevin
0
 

Author Closing Comment

by:Frank Freese
ID: 37015548
thank you so very much...tomorrow I'll ask my related question once I figure a way to correctly fram it. appreciate it!
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 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