Query by Range Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been unsucessful in being able to set up a query that can filter by a
range. Let me describe the problem by eample:

Set up tblWageRate as follows:

ID_1 Company Trade from_date to_date ST_Wage OT_Wage

(Note: ST = Straight Time, OT = Overtime)

Set up tblLabor as follows:

ID_2 Company Person Trade Date_Worked ST_Hours

I want to create a query from the above tables that has the following results:

Company Person Trade Date ST_Hours ST_Wage

Now I have managed to get

tblLabor.Company = tblWageRate.Company,
tbLabor.Trade = tblWageRate.Trade

by joints in the query statement.

But getting a value for query.ST_Wage from tblWageRate.ST_Wage when

((tblWageRate.from_date <= tblLabor.Date_Worked) and (tblLabor.Date_Worked
<= tblWageRate.to_date))

has remained elusive.

I'm sure I'm close somehow, but I could use some suggestions.

Thanks,

Ray Pixley
 
Ray, can I suggest a slightly different structure for tblWageRate? Store a
From_Date only. This makes it easier to find the right record, and avoids
the problems of overlapping dates, missed dates, and not knowing when the
current value will change.

Now you can use a subquery to retrieve the rate into your main query. That
means your main query does not need the tblWageRate table. Just type
something like this into the Field row of your query:

ST_Rate: (SELECT TOP 1 tblWageRate.ST_Wage
FROM tblWageRate
WHERE (tblWageRate.Company = tblLabor.Company)
AND (tblWageRate.Trade = tblLabour.Trade)
AND (tblWageRate.From_Date >= tblLabour.Date_Worked)
ORDER BY tblWageRate.From_Date DESC,
tblWageRate.WageRateID)

A similar subquery will retrieve the OT_Wage value.
 
I typed what you suggested into the field row of my query, but no joy. I
used the build editor to modify it, which inserted a lots of square brackets
"[]" and exclamation points "!", but got complaints about syntax when I tried
executing the query. I'm just about ready to deem Access a "User Hostile"
program in that the error messages are not very helpful. This effort has
consumed a few hours of my time with little to show, so I took a time out and
put it aside for a while.

Unfortunately when I got you post, I answered "Yes" to the "Did this post
answer the question?" in http://support.microsoft.com/newsgroups/?pr=1139.
At the time, it looked like the posting did answer the question; now I
realize it didn't. Is there some way of changing that response?

I need another way to do this than SQL; perhaps a visual basic function. Is
there a simple way I can have a visual basic function accomplish what I want?
 
The more recent versions of Access run user-defined functions very poorly in
queries, so achieving the result with SQL will be much more efficient.

If you want to mess with doing it through code, the code will need to open a
recordset to get the desired value. This article might give you an example
to work from. It contains the code you can use to replace DLookup() if
that's any help:
http://members.iinet.net.au/~allenbrowne/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ray Pixley said:
I typed what you suggested into the field row of my query, but no joy. I
used the build editor to modify it, which inserted a lots of square
brackets
"[]" and exclamation points "!", but got complaints about syntax when I
tried
executing the query. I'm just about ready to deem Access a "User Hostile"
program in that the error messages are not very helpful. This effort has
consumed a few hours of my time with little to show, so I took a time out
and
put it aside for a while.

Unfortunately when I got you post, I answered "Yes" to the "Did this post
answer the question?" in
http://support.microsoft.com/newsgroups/?pr=1139.
At the time, it looked like the posting did answer the question; now I
realize it didn't. Is there some way of changing that response?

I need another way to do this than SQL; perhaps a visual basic function.
Is
there a simple way I can have a visual basic function accomplish what I
want?

Allen Browne said:
Ray, can I suggest a slightly different structure for tblWageRate? Store
a
From_Date only. This makes it easier to find the right record, and avoids
the problems of overlapping dates, missed dates, and not knowing when the
current value will change.

Now you can use a subquery to retrieve the rate into your main query.
That
means your main query does not need the tblWageRate table. Just type
something like this into the Field row of your query:

ST_Rate: (SELECT TOP 1 tblWageRate.ST_Wage
FROM tblWageRate
WHERE (tblWageRate.Company = tblLabor.Company)
AND (tblWageRate.Trade = tblLabour.Trade)
AND (tblWageRate.From_Date >= tblLabour.Date_Worked)
ORDER BY tblWageRate.From_Date DESC,
tblWageRate.WageRateID)

A similar subquery will retrieve the OT_Wage value.
Click to expand...
Click to expand...
 
Back
Top