Subquery Range Lookup

[John Keith]

I could do this in VBA easily, however I want to know the pure-access query
syntax for accomplishing this (to allow the range table to be maintained by
the end-user) I have looked at http://allenbrowne.com\ser-58.html as a
start, but I'm having trouble converting this to my needs.

I need to lookup the meals per manhour using the Average Daily Participation

MMHFactorOLD w/ two fields
MinimumADP, MperMH
0, 11
151, 12
251, 13
501, 14
901, 15

Input Query "Staffing Input" has Period, Site and ADP

Top of new query references the InputQuery all fields (*)
Im adding a new field to pull the looked up MperMH

?? Do I need to "Show Table" and add the tblMMH?
Or create the field with MperMH:Select DISTINCT MPerMH From tblMMH

And In the Criteria of the new MMH field...
(SELECT Min(MinimumADP) FROM tblMMH WHERE MinimumADP > ADP)

I've tried several different versions and it crashes access.

The end result I'm trying for is:
Period Site ADP MMH
2009/10 100 103 11
2009/10 200 275 13
2009/10 300 501 14
2009/10 400 900 14
2009/10 500 999 15

 

[Daryl S]

John -

Yes, show both tables in your query, but they won't have any joins between
them.

Add both columns from tblMMH to your query. You can hide the MinimumADP
field after you test it out. Put this in the criteria for the MinimumADP
field:

(SELECT Max([MinimumADP]) FROM tblMMH WHERE [MinimumADP] <= [ADP])

Hope this helps!

 

[John Keith]

I tried that and it crashes Access with the error report below

The real lookup table is called MMHFactorOLD
StaffingInput contains the field [ADP]
I added the lookup table to the top pane (no joins)
then added the SELECT MAX... to the "Criteria" line under MinimumADP field.

Here is the SQL verion of the attempted query parsed a bit to make it more
readable here:

SELECT StaffingInput.*,
MMHFactorOLD.MinimumADP,
MMHFactorOLD.MealsPerManHour
FROM StaffingInput, MMHFactorOLD
WHERE (((MMHFactorOLD.MinimumADP)=
(SELECT Max([MinimumADP])
FROM MMHFactorOLD
WHERE [MinimumADP] <= [ADP])));

AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msjtes40.dll ModVer: 4.0.9502.0 ModStamp:44859905
fDebug: 0 Offset: 00007711


--
Regards,
John

John -

Yes, show both tables in your query, but they won't have any joins between
them.

Add both columns from tblMMH to your query. You can hide the MinimumADP
field after you test it out. Put this in the criteria for the MinimumADP
field:

(SELECT Max([MinimumADP]) FROM tblMMH WHERE [MinimumADP] <= [ADP])

Hope this helps!
--
Daryl S

I could do this in VBA easily, however I want to know the pure-access query
syntax for accomplishing this (to allow the range table to be maintained by
the end-user) I have looked at http://allenbrowne.com\ser-58.html as a
start, but I'm having trouble converting this to my needs.

I need to lookup the meals per manhour using the Average Daily Participation

MMHFactorOLD w/ two fields
MinimumADP, MperMH
0, 11
151, 12
251, 13
501, 14
901, 15

Input Query "Staffing Input" has Period, Site and ADP

Top of new query references the InputQuery all fields (*)
Im adding a new field to pull the looked up MperMH

?? Do I need to "Show Table" and add the tblMMH?
Or create the field with MperMH:Select DISTINCT MPerMH From tblMMH

And In the Criteria of the new MMH field...
(SELECT Min(MinimumADP) FROM tblMMH WHERE MinimumADP > ADP)

I've tried several different versions and it crashes access.

The end result I'm trying for is:
Period Site ADP MMH
2009/10 100 103 11
2009/10 200 275 13
2009/10 300 501 14
2009/10 400 900 14
2009/10 500 999 15

 

[John Keith]

I tried the sub-query style out on Office2007 and it reported the SQL as too
complex. Instead of crashing.

I found alternate syntax that works (and does not require any supporting
stacked queries too) I think that having the Initial recordset having to be
both the data and the rate tables combined with a subsequent criteria
selection was too much for Access2003 to figure out. The DLookup using the
DMin as the where portion was able to directly read the necessary field from
the chosen row gets past the Access limitation.

MpMH: DLookUp("[MealsPerManHour]","MMHFactorOLD","[MinimumADP]=" &
DMax("[MinimumADP]","MMHFactorOLD","[MinimumADP]<" & [ADP]))


--
Regards,
John

I tried that and it crashes Access with the error report below

The real lookup table is called MMHFactorOLD
StaffingInput contains the field [ADP]
I added the lookup table to the top pane (no joins)
then added the SELECT MAX... to the "Criteria" line under MinimumADP field.

Here is the SQL verion of the attempted query parsed a bit to make it more
readable here:

SELECT StaffingInput.*,
MMHFactorOLD.MinimumADP,
MMHFactorOLD.MealsPerManHour
FROM StaffingInput, MMHFactorOLD
WHERE (((MMHFactorOLD.MinimumADP)=
(SELECT Max([MinimumADP])
FROM MMHFactorOLD
WHERE [MinimumADP] <= [ADP])));

AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msjtes40.dll ModVer: 4.0.9502.0 ModStamp:44859905
fDebug: 0 Offset: 00007711


--
Regards,
John

John -

Yes, show both tables in your query, but they won't have any joins between
them.

Add both columns from tblMMH to your query. You can hide the MinimumADP
field after you test it out. Put this in the criteria for the MinimumADP
field:

(SELECT Max([MinimumADP]) FROM tblMMH WHERE [MinimumADP] <= [ADP])

Hope this helps!
--
Daryl S

I could do this in VBA easily, however I want to know the pure-access query
syntax for accomplishing this (to allow the range table to be maintained by
the end-user) I have looked at http://allenbrowne.com\ser-58.html as a
start, but I'm having trouble converting this to my needs.

I need to lookup the meals per manhour using the Average Daily Participation

MMHFactorOLD w/ two fields
MinimumADP, MperMH
0, 11
151, 12
251, 13
501, 14
901, 15

Input Query "Staffing Input" has Period, Site and ADP

Top of new query references the InputQuery all fields (*)
Im adding a new field to pull the looked up MperMH

?? Do I need to "Show Table" and add the tblMMH?
Or create the field with MperMH:Select DISTINCT MPerMH From tblMMH

And In the Criteria of the new MMH field...
(SELECT Min(MinimumADP) FROM tblMMH WHERE MinimumADP > ADP)

I've tried several different versions and it crashes access.

The end result I'm trying for is:
Period Site ADP MMH
2009/10 100 103 11
2009/10 200 275 13
2009/10 300 501 14
2009/10 400 900 14
2009/10 500 999 15