How can I lookup a value that is between dates in another table

[Beeker]

I have a table called 'RawData' that collects production data. We run a
report on this data everyday to see the performance of each employee. I
have another table called 'tblStandards' with standards for each area
and an effective date of the standard. Any production numbers before
the effectivity date will use the older standard and any on or after
the date will use the newer standard. My problem is how to write a
formula in a query that will pull the correct standard for each record.
In 'tblStandards' I have the following fields:

Type (Text) - Defines which department the standard is for.
Category (Text) - Area within department
Standard (Number, Long Integer)
EffectiveDate (Date/Time)

------------------------------------
Example: (there are other fields, I will only include the necessary
ones)

Data for two records in RawData are
Category Date
------------- ---------
Record #1: Consumer 5/5/06
Record #2: Consumer 8/2/06

Standards include the following records:

Type Category Standard Effective Date
------- ------------- ------------- -------------------
Spares Consumer 90 1/1/2006
Spares Small 30 1/1/2006
Spares Large 15 1/1/2006
Spares Consumer 80 6/1/2006
Spares Consumer 70 9/1/2006

Record #1 should return standard of 90
Record #2 should return standard of 80

I tried DMax and DLookup but neither work the way had hoped. Maybe I
don't have it written correctly.
Query fields are:

Field: EmployeeID
Table: RAW Data

Field: Date
Table: RAW Data

Field: Standard: DLookUp("[Standard]","tblStandards","[Category]
='Consumer' " And "[EffectiveDate] <=" & [Date])
Table:

I included 'tblStandards' in my query but of course there are no fields
to create a join. One of the problems I found with DLookup is that it
runs very slow.
Help! This is driving me crazy!

[Allen Browne]

See:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

Tom Ellison explains how to query this efficiently. It is worth the effort
to follow through what he describes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Beeker" <Sc********@bdhhi.comwrote in message
news:11**********************@i56g2000hsf.googlegr oups.com...

>I have a table called 'RawData' that collects production data. We run a
report on this data everyday to see the performance of each employee. I
have another table called 'tblStandards' with standards for each area
and an effective date of the standard. Any production numbers before
the effectivity date will use the older standard and any on or after
the date will use the newer standard. My problem is how to write a
formula in a query that will pull the correct standard for each record.
In 'tblStandards' I have the following fields:

Type (Text) - Defines which department the standard is for.
Category (Text) - Area within department
Standard (Number, Long Integer)
EffectiveDate (Date/Time)

------------------------------------
Example: (there are other fields, I will only include the necessary
ones)

Data for two records in RawData are
Category Date
------------- ---------
Record #1: Consumer 5/5/06
Record #2: Consumer 8/2/06

Standards include the following records:

Type Category Standard Effective Date
------- ------------- ------------- -------------------
Spares Consumer 90 1/1/2006
Spares Small 30 1/1/2006
Spares Large 15 1/1/2006
Spares Consumer 80 6/1/2006
Spares Consumer 70 9/1/2006

Record #1 should return standard of 90
Record #2 should return standard of 80

I tried DMax and DLookup but neither work the way had hoped. Maybe I
don't have it written correctly.
Query fields are:

Field: EmployeeID
Table: RAW Data

Field: Date
Table: RAW Data

Field: Standard: DLookUp("[Standard]","tblStandards","[Category]
='Consumer' " And "[EffectiveDate] <=" & [Date])
Table:

I included 'tblStandards' in my query but of course there are no fields
to create a join. One of the problems I found with DLookup is that it
runs very slow.

Help! This is driving me crazy!