471,337 Members | 1,303 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

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

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!

Jan 11 '07 #1
1 3927
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!
Jan 11 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Marco Simone | last post: by
1 post views Thread by Zachary Turner | last post: by
2 posts views Thread by =?Utf-8?B?VEQgaXMgUFNQ?= | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.