?
Solved

If statement in Query

Posted on 2011-09-30
15
Medium Priority
?
233 Views
Last Modified: 2012-05-12
Experts,
I've attached my db so you can better "see" what my objective is.

In the form frmBonusEarned I use 4 unbounded text controls that read from the combo box called StoreID. This form is my input form from which my calculations will be based upon.

Here's what can happen. An employee may no longer be an active employee and therefore would not be eligible for any store bonuses. In the table tblEmployee there is a Y/N field called Active. In my unbounded text controls I need to evaluate to see if the Employee is Active and if not the display some message such as "No Manager Assigned".
StoreBonus.mdb
0
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36893706
which employee are you referring to, after populating all the unbound controls ?
0
 

Author Comment

by:Frank Freese
ID: 36893742
A store can have 4 employees that can participate in a store bonus:
District Manager
Assistant Support Manager (if there is one)
Store Manaager
Assistant Store Manager

If any of those are not active then they ate tagged as No in the tblEmployee - Active field, and would not particiapate in any bonuses. The control would could read None in the form frmBonusEarned
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36893761
so, you are saying that before showing the names to the corresponding textbox,
check first if they are active or not ?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 16

Expert Comment

by:Sheils
ID: 36893812
Can you enter some bonus so that we can see what you are trying to achieve.

Also you db would be much easier to navigate an query if you tblDesignatedEmployee was set as:

DesignatedEmployeeID,StoreID,employeeID,DesignationID

The designationID is a lookup field to a designation table which would have the name of all you positions
0
 

Author Comment

by:Frank Freese
ID: 36893814
that's correct. If they are no longer active no bonus
0
 

Author Comment

by:Frank Freese
ID: 36893840
sb9:
I haven't got that far on data entry - I'm sure a whole new set of problems will come up there. I'm not sure I understand the second part on the structure - if you all think my structure needs to be different I'm open to that also
0
 

Author Comment

by:Frank Freese
ID: 36893863
sb9:
I could changed the tblDesignatedEmployee as suggested. I'd have to change the form frmDesignate Employee. Cap, what do you think?
0
 
LVL 16

Expert Comment

by:Sheils
ID: 36893905
Just use some dummy data otherwise it will be impossible to figure out if what we are doing is really working. The table are relational and since you do not have employeeID in tblBonusEntered which is the recordsource of the subject form you would need a query to find the corresponding employeeid. If there are no data in the tables involved the query is not going to work properly.

RE: My second statement:

It is best not to "hard code" the position in the table field. The will make is easier to add more position or reduce position. Also queries will be much simpler if your are not pointing to 4 employeeid in a single record.
0
 

Author Comment

by:Frank Freese
ID: 36893934
there is data already except for the dollars - I plan on first captuting the data then create the queries and reports I will need. I do understand the change in the table structure now. I would need to throw the baby out with the bath water, close this question down and resubmitt if necessary. I know capricorn has responded and I don't want him to be going in one direction and me in a total different one.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 36894221
Fair comment. I have started to work on an approach that will keep the look of the current frmDesignate and work with the new structure. Just open a new related question and we will work from it there. You may proceed with the if query component of you question in the current thread.

@ Capricorn: What's your views
0
 

Author Comment

by:Frank Freese
ID: 36894249
thank - capricorn, are you ok with this?
0
 
LVL 16

Expert Comment

by:Sheils
ID: 36894621
0
 
LVL 16

Expert Comment

by:Sheils
ID: 36894662
Opps, missed the comments and compact and repair to reduce file size.

Check out the new frmDesignate Employee. NB recordsource for the mainform is tblStore and each designation is actually a subform based on 4 new queries. I have also use an onclick event in the control of the subforms to add the designation.
StoreBonus-1-.mdb
0
 
LVL 16

Accepted Solution

by:
Sheils earned 2000 total points
ID: 36894713
also set the recordtpye of  frmDesignate Employee and its subforms to Dynaset (Inconsistent Updates)
0
 

Author Closing Comment

by:Frank Freese
ID: 36897032
you sure went the extra mile on this - thanks and GREAT job. I truly appreciate the Experts!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.
Suggested Courses

762 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