Queries using Calculations

[Guest]

This is the problem that I am running into. I have different facilities that
I send staff to, and each of these facilities have different pay rates per
shift per licensure. What I need is a calculation that grabs the right
licensure with the right shift and displays the right amount of pay.
What I am currently using is:
ex. RNRate1A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="1",
[RNRate1],0))
ex. RNRate2A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="2",
[RNRate2],0))

The two tables I am using for this query are:

tblFacility:
FacilityName,RNRate1, RNRate2, RNRate3, LPNRate1, LPNRate2, LPNRate3 etc
tblRequest:
FacilityName, Licensure, Shift, TherapistName, RequestFilled, etc.

If anyone has any Ideas where I am going wrong with this I would really
appreciate your input. Thank you in advance!

 

[[MVP] S.Clark]

Where you're headed is a lifetime of painful IIF() statements in queries.
Can you store this data in a table, or several tables, such that the query
engine and/or some VBA can do the work for you?

 

[Guest]

Every iif need to have

iif(where , then , else)
In the both iif in the first iif you are missing the else

IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="1",
[RNRate1],0))
IIF (Where , IIF (Where , Then , Else) , "Missing here the else what if the
licence is not = RN")

 

[Guest]

S.Clark,
Thank you very much for your response but what exactly are you suggesting
that I do. I have all the information stored in my tables I just need it to
grab the right amount of pay off of tblfacility.

When a request comes in I enter it into my tblRequest, Lets just say it is
for an "RN" "1st Shift" at "ABC company" .
In my tblFacility I can see that ABC Company pays RN's $30 hrly for 1st shift.

Where you're headed is a lifetime of painful IIF() statements in queries.
Can you store this data in a table, or several tables, such that the query
engine and/or some VBA can do the work for you?

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting

This is the problem that I am running into. I have different facilities
that
I send staff to, and each of these facilities have different pay rates per
shift per licensure. What I need is a calculation that grabs the right
licensure with the right shift and displays the right amount of pay.
What I am currently using is:
ex. RNRate1A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="1",
[RNRate1],0))
ex. RNRate2A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="2",
[RNRate2],0))

The two tables I am using for this query are:

tblFacility:
FacilityName,RNRate1, RNRate2, RNRate3, LPNRate1, LPNRate2, LPNRate3 etc
tblRequest:
FacilityName, Licensure, Shift, TherapistName, RequestFilled, etc.

If anyone has any Ideas where I am going wrong with this I would really
appreciate your input. Thank you in advance!

 

[Guest]

If anyone has any Ideas where I am going wrong with this I would really

appreciate your input.

You're having severe problems because your tables aren't normalized. I'd
suggest changing the structure of the two tables and adding a new one, so
that they look like the following.

tblFacility:
FID, AutoNumber, primary key
FacilityName, Text

tblFacilityRates:
FRID, AutoNumber, primary key
FID, Long, foreign key to tblFacility table
Licensure, Text
Rate, Currency
Shift, Text (?) (Perhaps this should be Long if it's always numerical.)

tblRequest:
RID, AutoNumber, primary key
FRID, Long, foreign key to tblFacilityRates table
TherapistName, Text
RequestFilled, Boolean
et cetera

The data would look like:

tblFacility:
FID FacilityName
1 Mercy Hospital
2 MLK Hospital

tblFacilityRates:
FRID FID Licensure Rate Shift
1 1 RN blah 1
2 1 RN blah 2
3 1 RN blah 3
4 1 LVN blah 1
5 1 LVN blah 2
6 1 LVN blah 3
7 2 RN blah 1
8 2 RN blah 2
et cetera

tblRequest:
RID FRID TherapistName RequestFilled
1 1 Hugh Dunnit X
2 4 Justin Case X
3 3 Bill M. Later X
4 7
et cetera

Create a new query and paste the following into the SQL View pane:

SELECT RID, tblRequest.FRID, tblFacilityRates.FID,
Licensure, Shift, Rate, TherapistName, RequestFilled
FROM (tblFacility INNER JOIN tblFacilityRates
ON tblFacility.FID = tblFacilityRates.FID)
INNER JOIN tblRequest ON
tblFacilityRates.FRID = tblRequest.FRID;

Add the other fields in the tblRequest table to this query. Save the query
as qryFacilityRequestRates and run it. The proper rate is automatically
assigned to the existing records, according to which facility, license
required, and shift.

For data input, use a form that uses this autolookup query to automatically
fill in the fields for you. To do so, follow these instructions:

Create a new query. Paste the following SQL into the SQL View pane:

SELECT FRID, FacilityName, Licensure, Rate, Shift
FROM tblFacility INNER JOIN tblFacilityRates
ON tblFacility.FID = tblFacilityRates.FID
ORDER BY FacilityName, Shift, Licensure;

Save it and name it qryFacilityRates.

Create a new form. Make sure that the form is bound to the
qryFacilityRequestRates query and all fields in the query are placed on the
form.

Open your new form in design view and change the text box for FRID into a
combo box. To do this, select the FRID text box and right click for the
pop-up menu and select "Change to" then select "Combo Box." Open the
Properties dialog window for this new combo box. On the "Format" tab, change
the "Column Count" Property from 1 to 5 and change the "Column Widths" to
0";1";0.3";0";0.2"

On the "Data" tab, change the "Row Source" Property to qryFacilityRates.
Save the new form.

To show the autolookup query in action, open the new form in "Form View."
Add a new record and select the item with the correct facility name,
licensure, and shift in the "FRID" combo box. The rate and the rest of the
fields has been filled in automagixly for the other text boxes in the form.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

This is the problem that I am running into. I have different facilities that
I send staff to, and each of these facilities have different pay rates per
shift per licensure. What I need is a calculation that grabs the right
licensure with the right shift and displays the right amount of pay.
What I am currently using is:
ex. RNRate1A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="1",
[RNRate1],0))
ex. RNRate2A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="2",
[RNRate2],0))

The two tables I am using for this query are:

tblFacility:
FacilityName,RNRate1, RNRate2, RNRate3, LPNRate1, LPNRate2, LPNRate3 etc
tblRequest:
FacilityName, Licensure, Shift, TherapistName, RequestFilled, etc.

If anyone has any Ideas where I am going wrong with this I would really
appreciate your input. Thank you in advance!

 

[Guest]

Gunny,
Thank you very much for your help!! This was a very good idea and worked
great. I thought about breaking down the facility table in the begining of
the project and you actually gave me a better way of doing it than what I was
originally thinking. Once again thank you for your help!

If anyone has any Ideas where I am going wrong with this I would really
appreciate your input.

You're having severe problems because your tables aren't normalized. I'd
suggest changing the structure of the two tables and adding a new one, so
that they look like the following.

tblFacility:
FID, AutoNumber, primary key
FacilityName, Text

tblFacilityRates:
FRID, AutoNumber, primary key
FID, Long, foreign key to tblFacility table
Licensure, Text
Rate, Currency
Shift, Text (?) (Perhaps this should be Long if it's always numerical.)

tblRequest:
RID, AutoNumber, primary key
FRID, Long, foreign key to tblFacilityRates table
TherapistName, Text
RequestFilled, Boolean
et cetera

The data would look like:

tblFacility:
FID FacilityName
1 Mercy Hospital
2 MLK Hospital

tblFacilityRates:
FRID FID Licensure Rate Shift
1 1 RN blah 1
2 1 RN blah 2
3 1 RN blah 3
4 1 LVN blah 1
5 1 LVN blah 2
6 1 LVN blah 3
7 2 RN blah 1
8 2 RN blah 2
et cetera

tblRequest:
RID FRID TherapistName RequestFilled
1 1 Hugh Dunnit X
2 4 Justin Case X
3 3 Bill M. Later X
4 7
et cetera

Create a new query and paste the following into the SQL View pane:

SELECT RID, tblRequest.FRID, tblFacilityRates.FID,
Licensure, Shift, Rate, TherapistName, RequestFilled
FROM (tblFacility INNER JOIN tblFacilityRates
ON tblFacility.FID = tblFacilityRates.FID)
INNER JOIN tblRequest ON
tblFacilityRates.FRID = tblRequest.FRID;

Add the other fields in the tblRequest table to this query. Save the query
as qryFacilityRequestRates and run it. The proper rate is automatically
assigned to the existing records, according to which facility, license
required, and shift.

For data input, use a form that uses this autolookup query to automatically
fill in the fields for you. To do so, follow these instructions:

Create a new query. Paste the following SQL into the SQL View pane:

SELECT FRID, FacilityName, Licensure, Rate, Shift
FROM tblFacility INNER JOIN tblFacilityRates
ON tblFacility.FID = tblFacilityRates.FID
ORDER BY FacilityName, Shift, Licensure;

Save it and name it qryFacilityRates.

Create a new form. Make sure that the form is bound to the
qryFacilityRequestRates query and all fields in the query are placed on the
form.

Open your new form in design view and change the text box for FRID into a
combo box. To do this, select the FRID text box and right click for the
pop-up menu and select "Change to" then select "Combo Box." Open the
Properties dialog window for this new combo box. On the "Format" tab, change
the "Column Count" Property from 1 to 5 and change the "Column Widths" to
0";1";0.3";0";0.2"

On the "Data" tab, change the "Row Source" Property to qryFacilityRates.
Save the new form.

To show the autolookup query in action, open the new form in "Form View."
Add a new record and select the item with the correct facility name,
licensure, and shift in the "FRID" combo box. The rate and the rest of the
fields has been filled in automagixly for the other text boxes in the form.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

This is the problem that I am running into. I have different facilities that
I send staff to, and each of these facilities have different pay rates per
shift per licensure. What I need is a calculation that grabs the right
licensure with the right shift and displays the right amount of pay.
What I am currently using is:
ex. RNRate1A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="1",
[RNRate1],0))
ex. RNRate2A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="2",
[RNRate2],0))

The two tables I am using for this query are:

tblFacility:
FacilityName,RNRate1, RNRate2, RNRate3, LPNRate1, LPNRate2, LPNRate3 etc
tblRequest:
FacilityName, Licensure, Shift, TherapistName, RequestFilled, etc.

If anyone has any Ideas where I am going wrong with this I would really
appreciate your input. Thank you in advance!

 

['69 Camaro]

You're very welcome. I didn't want to give you something that was more
complex than you were already working on at the time, but you probably want
to further normalize the suggested the tblRequest table. The TherapistName
field should be replaced by a foreign key, TID, which would come from a
tblTherapists table. The tblTherapists table would be structured like the
following:

tblTherapists
TID, AutoNumber, primary key
FirstName, Text
LastName, Text
MI, Text

Of course, qryFacilityRequestRates would need to have the TherapistName
field replaced with the TID field and this same field changed on the form,
as well. To display the full name instead of the TID on the form, create a
query which concatenates these names into the full name:

SELECT TID, (FirstName & " " & IIF(ISNULL(MI), NULL, MI & ". ") & LastName)
AS FullName
FROM tblTherapists
ORDER BY LastName, FirstName, MI

Save the query and name it qryTherapistNames. On the form, either convert
the TID text box (the former TherapistName text box) to a combo box or
create a new combo box, bound to the TID field. Open the Properties dialog
window for this new combo box. On the "Format" tab, change the "Column
Count" Property from 1 to 2 and change the "Column Widths" to 0";1.5"

On the "Data" tab, change the "Row Source" Property to qryTherapistNames.
Save the form. When the form is in Form View, the user will see and select
the full name of the therapist in this combo box, while the TID is actually
saved to the record.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

Gunny,
Thank you very much for your help!! This was a very good idea and
worked
great. I thought about breaking down the facility table in the begining of
the project and you actually gave me a better way of doing it than what I
was
originally thinking. Once again thank you for your help!

If anyone has any Ideas where I am going wrong with this I would really
appreciate your input.

You're having severe problems because your tables aren't normalized. I'd
suggest changing the structure of the two tables and adding a new one, so
that they look like the following.

tblFacility:
FID, AutoNumber, primary key
FacilityName, Text

tblFacilityRates:
FRID, AutoNumber, primary key
FID, Long, foreign key to tblFacility table
Licensure, Text
Rate, Currency
Shift, Text (?) (Perhaps this should be Long if it's always numerical.)

tblRequest:
RID, AutoNumber, primary key
FRID, Long, foreign key to tblFacilityRates table
TherapistName, Text
RequestFilled, Boolean
et cetera

The data would look like:

tblFacility:
FID FacilityName
1 Mercy Hospital
2 MLK Hospital

tblFacilityRates:
FRID FID Licensure Rate Shift
1 1 RN blah 1
2 1 RN blah 2
3 1 RN blah 3
4 1 LVN blah 1
5 1 LVN blah 2
6 1 LVN blah 3
7 2 RN blah 1
8 2 RN blah 2
et cetera

tblRequest:
RID FRID TherapistName RequestFilled
1 1 Hugh Dunnit X
2 4 Justin Case X
3 3 Bill M. Later X
4 7
et cetera

Create a new query and paste the following into the SQL View pane:

SELECT RID, tblRequest.FRID, tblFacilityRates.FID,
Licensure, Shift, Rate, TherapistName, RequestFilled
FROM (tblFacility INNER JOIN tblFacilityRates
ON tblFacility.FID = tblFacilityRates.FID)
INNER JOIN tblRequest ON
tblFacilityRates.FRID = tblRequest.FRID;

Add the other fields in the tblRequest table to this query. Save the
query
as qryFacilityRequestRates and run it. The proper rate is automatically
assigned to the existing records, according to which facility, license
required, and shift.

For data input, use a form that uses this autolookup query to
automatically
fill in the fields for you. To do so, follow these instructions:

Create a new query. Paste the following SQL into the SQL View pane:

SELECT FRID, FacilityName, Licensure, Rate, Shift
FROM tblFacility INNER JOIN tblFacilityRates
ON tblFacility.FID = tblFacilityRates.FID
ORDER BY FacilityName, Shift, Licensure;

Save it and name it qryFacilityRates.

Create a new form. Make sure that the form is bound to the
qryFacilityRequestRates query and all fields in the query are placed on
the
form.

Open your new form in design view and change the text box for FRID into a
combo box. To do this, select the FRID text box and right click for the
pop-up menu and select "Change to" then select "Combo Box." Open the
Properties dialog window for this new combo box. On the "Format" tab,
change
the "Column Count" Property from 1 to 5 and change the "Column Widths" to
0";1";0.3";0";0.2"

On the "Data" tab, change the "Row Source" Property to qryFacilityRates.
Save the new form.

To show the autolookup query in action, open the new form in "Form View."
Add a new record and select the item with the correct facility name,
licensure, and shift in the "FRID" combo box. The rate and the rest of
the
fields has been filled in automagixly for the other text boxes in the
form.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.

This is the problem that I am running into. I have different
facilities that
I send staff to, and each of these facilities have different pay rates
per
shift per licensure. What I need is a calculation that grabs the right
licensure with the right shift and displays the right amount of pay.
What I am currently using is:
ex. RNRate1A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="1",
[RNRate1],0))
ex. RNRate2A: IIF([tblRequest].[Licensure]= "RN", IIF([Shift]="2",
[RNRate2],0))

The two tables I am using for this query are:

tblFacility:
FacilityName,RNRate1, RNRate2, RNRate3, LPNRate1, LPNRate2, LPNRate3
etc
tblRequest:
FacilityName, Licensure, Shift, TherapistName, RequestFilled, etc.

If anyone has any Ideas where I am going wrong with this I would really
appreciate your input. Thank you in advance!