Query calculated field based on another query

[gmcco]

Hello,
this is probably fairly straighforward but I just can't get the right syntax
in building this query:

I have the following table containing time records
person, rDate, rHours (tRecords)

I have a table with rates (tRates)
person, Rate1, Rate2

I have a table with national holidays (tHolidays)
hDate

I need to have a query which returns the following:
person, rDate, billed: rHours * if (wDate is found in tholidays then Rate2
else Rate1)

any pointers greatly appreciated.
thanks

 

[gmcco]

what I should have specified is that i have difficulty in trying to get the
value calculated for the custom field:
rHours * if (wDate is found in tHolidays then Rate2 else Rate1)

I have tried using select statements to identify if the wDate values exist
but i have difficulty getting the syntax right so that Access will allow me
to do this.

any ideas?

 

[Keith Wilby]

Hello,
this is probably fairly straighforward but I just can't get the right
syntax
in building this query:

I have the following table containing time records
person, rDate, rHours (tRecords)

I have a table with rates (tRates)
person, Rate1, Rate2

I have a table with national holidays (tHolidays)
hDate

I need to have a query which returns the following:
person, rDate, billed: rHours * if (wDate is found in tholidays then Rate2
else Rate1)

any pointers greatly appreciated.
thanks

I think you'll need to supply some information on how those tables relate to
each-other.

Keith.

 

[John Spencer]

The SQL for this should look something like

SELECT Person, rDate
, tHours * IIF(Hdate is Null, TRates.Rate1,tRates.Rate2) as Billed
FROM (tRecords Inner JOIN tRates
On tRecords.Person = tRates.Person)
LEFT JOIN tHolidays
ON tRecords.rDate = tHolidays.hDate

In the query grid
-- Add all three tables
-- Join tRecords to tRates using the Person fields
-- Join tHolidays to tRecords using the date fields
-- double click on the join line and select All tRecords and matching
tHolidays
-- add the fields you want to see
-- in a blank field "cell" enter
Billed: tHours * IIF(Hdate is Null, TRates.Rate1,tRates.Rate2)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[gmcco]

I think you'll need to supply some information on how those tables relate to
each-other.

Keith.

Thanks Keith but I think I have it sorted,
I make two seperate queries for each rate and then just create a Union query.

It's been staring me in the face but I just couldn't get my head around it.

 

[gmcco]

thanks John looks just like what I need.
regards
Gary