Find a value using a lookup expression

[Beeker]

I hope someone can help me! I'm frustrated!

I have a database that collects production data. Employees are rated by
performance and have standards to guide them. I need to create an
expression in a query to lookup the standard based on the area worked.
But the standard can change and should be pulled based on an effective
date.


My tables and fields are:

'tblStandards'
---------------
Type (Text)
Category (Text)
Standard (Number, Integer)
Effective Date (Date/Time)

'RawDataInput'
---------------
EmployeeID (AutoNumber)
Date (Date/Time)
Hours (Number)
Pieces (Number)

I created a query which totals all the Hours and Pieces. I need to make
calculated field that looks up the standard and compares the date for
each record and returns the associated standard.

If the Raw data is:
RecordID EmployeeID Date Category Hrs Pieces
-------- ---------- -------- -------- --- --------
3 56 6/5/2006 Consumer 4 695
4 99 6/6/2006 Consumer 2 280
5 56 6/6/2006 Consumer 4 587

And the Standards data is:
Type Category Standard EffectiveDate
------ -------- -------- -------------
Spares Consumer 90 6/1/2006
Spares Small 30 6/1/2006
Spares Consumer 80 6/6/2006

For record 3, the query should return a standard of 90 and record 5
should return 80.
How do I compare the dates in the Raw data to the Standards table to
find the correct Standard?

Any help will be greatly appreciated.

 

[Svetlana]

Try this?
CurrentStandard: DLast("Standard";"tblStandards";"[Effective Date]<=#"
& [Date] & "# And [Category]=""" & [Category] & """")

 

[Douglas J. Steele]

Note that if the user's Short Date format has been set to dd/mm/yyyy through
Regional Settings, that won't work properly.

To be safe, it's best to use:

CurrentStandard: DLast("Standard";"tblStandards";"[Effective Date]<="
& Format([Date], "\#mm\/dd\/yyyy\#") & "# And [Category]=""" & [Category] &
"""")

 

[Beeker]

Try this?
CurrentStandard: DLast("Standard";"tblStandards";"[Effective Date]<=#"
& [Date] & "# And [Category]=""" & [Category] & """")

Okay, I tried the formula above and get the following results:

RecordID EmployeeID Date Category Hrs Pieces Standard
-------- ---------- -------- -------- --- ------ --------
3 56 6/5/2006 Consumer 4 695 90
3 56 6/5/2006 Consumer 4 695 80
4 99 6/6/2006 Consumer 2 280 90
4 99 6/6/2006 Consumer 2 280 80
5 56 6/6/2006 Consumer 4 587 90
5 56 6/6/2006 Consumer 4 587 80

It's just as if I added the field to the query grid without a join to
the other table. I believe that is a Cartesian product. I have the
table 'tblStandards' in the grid to filter for another field. The query
asks for criteria if it's not there.

 

[Svetlana]

What is the sql for the query?

 

[Beeker]

What is the sql for the query?

I feel so stupid. The table 'RawDataInput' did not have the field
'Category'. I changed the table layout to include this field. My table
layout was not in accordance witht the basis table setup rules. The
fields were:

EmployeeID
Date (now 'ProdDate')
ConsumerHrs
ConsumerPcs
SmallHrs
SmallPcs
LargeHrs
LargePcs

I re-organized them into basically:

EmployeeID
Date (now 'ProdDate')
Category (Consumer, Small, Large)
Hrs
Pcs

Now I can join the two tables and use this DLookup expression:

CurrentStandard: DLookUp("Standard","tblStandards","[EffectiveDate]<=#"
& [ProdDate] & "# And [Category]=""" & [RAW Data INPUT]![Category] &
"""")

DMax was returning the greatest value of the Standard. I wanted to
lookup the greatest value of the 'EffectiveDate' that was before the
record's date.

I have split the table in two with main data info (EmployeeID and
ProdDate), and detail info (Category,Hrs,Pcs) and am still refining the
data tables now that I have discovered this and studied the data
further. If anyone would like to see the SQL for the query or my final
outcome, just let me know and I'l post it.

Thanks for all the help.

 

[Svetlana]

As you said in your first post the standard can change and should be
pulled based on an effective date. So you maybe need the DLast and not
the DLookup function as DLookup returns the first occurrence if more
than one field meets criteria.
CurrentStandard: DLast("Standard";"tblStandards";"EffectiveDate<=" &
Format(ProdDate, "\#mm\/dd\/yyyy\#")& "# And [Category]=""" &
[Category] & """")
Also it could be helpfull for you to read about topvalues property in
access help topics.