Lookup Based on Multiple Criteria

  • Thread starter Thread starter clk
  • Start date Start date
C

clk

I have a database where I have children enrolled at a certain
provider. Each provider has multiple rates based on Traditional and
Non-Traditional Rates. Under each rate type (traditional and non-
traditional) there are a possibility of six different rates based on
age. Also, they can be full time or part time, each with different
rates. I have a query determining which of the six rates they fall
into based on their age. For example, childA is 12 months so they are
considered an infant. Code is IN. So, I have a query that determines
childA, IN, ProviderName and all possible rates for this provider both
traditional and non-traditional.

Now, I have an attendance form. On this form there is a drop-down box
for each day of the month. On the drop-down, the user has the ability
to choose FT, FN, PT, PN or X. For an example, FT means full time
traditional rate.
So for childA if FT is selected on the attendance form, I need a way
for the system to lookup the full time traditional rate...knowing that
the child is "IN" (based on age) and pick that rate.

For some reason, I just can't seem to get this to work. I am not sure
if a dlookup would work or what to use to find the rates per day per
child.

Any help would be greatly appreciated. Been spinning my wheels on
this one.
 
You should be able to use the DLookup() function. Or you could write a custom
function to return the correct rate.

Are the rates date sensitive?

Would you post the SQL of the query? And the DLookup() you tried? And a
sample of the data in the query and an example of what you expect to see for
a given child?
 
Thank you for your response. Here is the sql code to determine child
code and possible rates per provider.

SELECT tblProvider_ChildBridge.P_CH_BridgeID,
tblProvider_ChildBridge.CH_ID, tblProvider_ChildBridge.P_ID,
tblProviders.P_Name, tblProvider_ChildBridge.P_CH_StartDate,
tblProvider_ChildBridge.P_CH_EndDate, tblProvider_ChildBridge.F_ID,
tblProviders.P_Type, DateDiff("m",[CH_DOB],Now()) AS AgeMonths,
DLookUp("[monthcode]","qryselectratemonth",[agemonths] & "Between
[startmonth] and
[endmonth]") AS RateCode, tblProvider_ChildBridge.P_CH_U,
tblProvider_ChildBridge.P_CH_M, tblProvider_ChildBridge.P_CH_T,
tblProvider_ChildBridge.P_CH_W, tblProvider_ChildBridge.P_CH_R,
tblProvider_ChildBridge.P_CH_F, tblProvider_ChildBridge.P_CH_S,
tblProviders.P_U, IIf(Not IsNull([P_CH_Notes]),"Y","") AS [Note],
tblProvider_ChildBridge.P_CH_Rates, tblProvider_ChildBridge.P_CH_Type
FROM tblChildren INNER JOIN (tblProviders INNER JOIN
tblProvider_ChildBridge ON tblProviders.P_ID =
tblProvider_ChildBridge.P_ID) ON tblChildren.CH_ID =
tblProvider_ChildBridge.CH_ID
WHERE (((tblProvider_ChildBridge.P_CH_EndDate) Is Null))
ORDER BY tblProvider_ChildBridge.P_CH_StartDate DESC;


Here is the sql code for the attendance form which determines when
they were at the provider:

SELECT tblAttendance.P_CHBridgeID, tblChildren.CH_ID,
tblAttendance.RT_Invoiced, tblAttendance.RT_Month,
tblAttendance.RT_Year, tblAttendance.RT_InvoiceAmount, tblAttendance.
[01], tblAttendance.[02], tblAttendance.[03], tblAttendance.[04],
tblAttendance.[05], tblAttendance.[06], tblAttendance.[07],
tblAttendance.[08], tblAttendance.[09], tblAttendance.[10],
tblAttendance.[11], tblAttendance.[12], tblAttendance.[13],
tblAttendance.[14], tblAttendance.[15], tblAttendance.[16],
tblAttendance.[17], tblAttendance.[18], tblAttendance.[19],
tblAttendance.[20], tblAttendance.[21], tblAttendance.[22],
tblAttendance.[23], tblAttendance.[24], tblAttendance.[25],
tblAttendance.[26], tblAttendance.[27], tblAttendance.[28],
tblAttendance.[29], tblAttendance.[30], tblAttendance.[31],
tblAttendance.RT_Notes, tblProviders.P_Name, tblChildren!CH_LName & ",
" & tblChildren!CH_FName AS Child, [RT_Month] & "/" & [RT_Year] AS
[Month], MonthName([RT_Month],1) & ", '" & Right([RT_Year],2) AS
Display
FROM tblChildren RIGHT JOIN (tblProviders RIGHT JOIN
(tblProvider_ChildBridge RIGHT JOIN tblAttendance ON
tblProvider_ChildBridge.P_CH_BridgeID = tblAttendance.P_CHBridgeID) ON
tblProviders.P_ID = tblProvider_ChildBridge.P_ID) ON tblChildren.CH_ID
= tblProvider_ChildBridge.CH_ID
WHERE (((tblAttendance.P_CHBridgeID)=[Forms]![frmProviderAttendance]!
[subfrmProviderAttendance]![txtBridgeID]) AND (([RT_Month] & "/" &
[RT_Year])=[Forms]![frmProviderAttendance]![cmbDateRange]));


Now what is I need is based on the RateCode (from top query) and based
on the attendance ([01] for example from bottom code). Determine the
rate. If [01] = FT that would be full time traditional. Now go to
the other query, determine age is IN and grab the full time
traditional rate for infant.

Any help with a dlookup or other solution would be greatly
appreciated.
 
I've been trying different things, but haven't made any progress.

Without the query names and examples of the results of the queries, I don't
think I can provide a solution.

One problem is that the table "tblAttendance" is not normalized. Instead of
31 fields for the days of the month, there should 1 row (record) for each day.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


clk said:
Thank you for your response. Here is the sql code to determine child
code and possible rates per provider.

SELECT tblProvider_ChildBridge.P_CH_BridgeID,
tblProvider_ChildBridge.CH_ID, tblProvider_ChildBridge.P_ID,
tblProviders.P_Name, tblProvider_ChildBridge.P_CH_StartDate,
tblProvider_ChildBridge.P_CH_EndDate, tblProvider_ChildBridge.F_ID,
tblProviders.P_Type, DateDiff("m",[CH_DOB],Now()) AS AgeMonths,
DLookUp("[monthcode]","qryselectratemonth",[agemonths] & "Between
[startmonth] and
[endmonth]") AS RateCode, tblProvider_ChildBridge.P_CH_U,
tblProvider_ChildBridge.P_CH_M, tblProvider_ChildBridge.P_CH_T,
tblProvider_ChildBridge.P_CH_W, tblProvider_ChildBridge.P_CH_R,
tblProvider_ChildBridge.P_CH_F, tblProvider_ChildBridge.P_CH_S,
tblProviders.P_U, IIf(Not IsNull([P_CH_Notes]),"Y","") AS [Note],
tblProvider_ChildBridge.P_CH_Rates, tblProvider_ChildBridge.P_CH_Type
FROM tblChildren INNER JOIN (tblProviders INNER JOIN
tblProvider_ChildBridge ON tblProviders.P_ID =
tblProvider_ChildBridge.P_ID) ON tblChildren.CH_ID =
tblProvider_ChildBridge.CH_ID
WHERE (((tblProvider_ChildBridge.P_CH_EndDate) Is Null))
ORDER BY tblProvider_ChildBridge.P_CH_StartDate DESC;


Here is the sql code for the attendance form which determines when
they were at the provider:

SELECT tblAttendance.P_CHBridgeID, tblChildren.CH_ID,
tblAttendance.RT_Invoiced, tblAttendance.RT_Month,
tblAttendance.RT_Year, tblAttendance.RT_InvoiceAmount, tblAttendance.
[01], tblAttendance.[02], tblAttendance.[03], tblAttendance.[04],
tblAttendance.[05], tblAttendance.[06], tblAttendance.[07],
tblAttendance.[08], tblAttendance.[09], tblAttendance.[10],
tblAttendance.[11], tblAttendance.[12], tblAttendance.[13],
tblAttendance.[14], tblAttendance.[15], tblAttendance.[16],
tblAttendance.[17], tblAttendance.[18], tblAttendance.[19],
tblAttendance.[20], tblAttendance.[21], tblAttendance.[22],
tblAttendance.[23], tblAttendance.[24], tblAttendance.[25],
tblAttendance.[26], tblAttendance.[27], tblAttendance.[28],
tblAttendance.[29], tblAttendance.[30], tblAttendance.[31],
tblAttendance.RT_Notes, tblProviders.P_Name, tblChildren!CH_LName & ",
" & tblChildren!CH_FName AS Child, [RT_Month] & "/" & [RT_Year] AS
[Month], MonthName([RT_Month],1) & ", '" & Right([RT_Year],2) AS
Display
FROM tblChildren RIGHT JOIN (tblProviders RIGHT JOIN
(tblProvider_ChildBridge RIGHT JOIN tblAttendance ON
tblProvider_ChildBridge.P_CH_BridgeID = tblAttendance.P_CHBridgeID) ON
tblProviders.P_ID = tblProvider_ChildBridge.P_ID) ON tblChildren.CH_ID
= tblProvider_ChildBridge.CH_ID
WHERE (((tblAttendance.P_CHBridgeID)=[Forms]![frmProviderAttendance]!
[subfrmProviderAttendance]![txtBridgeID]) AND (([RT_Month] & "/" &
[RT_Year])=[Forms]![frmProviderAttendance]![cmbDateRange]));


Now what is I need is based on the RateCode (from top query) and based
on the attendance ([01] for example from bottom code). Determine the
rate. If [01] = FT that would be full time traditional. Now go to
the other query, determine age is IN and grab the full time
traditional rate for infant.

Any help with a dlookup or other solution would be greatly
appreciated.
Click to expand...
 
Back
Top