Trying to query, select and sum multiple fields from same record

[Guest]

Here' s what the data looks like on one record from the table I'm trying to
query to compute payroll. Each record has over 30 fields, but I'm only
concerned with a basic strategy for now. Each word represents a field on a
record.

Vin#
DriverID1 Description1 Rate1 Date1
DriverID2 Description2 Rate2 Date2
DriverID3 Description3 Rate3 Date3

I want to be able to query a Rate if the corresponding DriverID and Date
fall within the parameters of my search. I want to show desired rates in a
pay report specific to a DriverID similar to the field layout that is shown
above with all 4 fields in a row. The problem I'm running into is this:
When in Query design mode, how can I say in field 'Rate1' [Select rate if
field 'DriverID1' = DriverID 'X' and field 'Date1' = Between these dates.]
while simultaneously running the same equation for Rate2 and Rate3, all of
which are in the same query design? It seems like I'm looking for a way to
link fields within a query based on a compound conditional statement. Not
sure if this is possible. Any suggestions, perhaps for alternate table
designs that would make this easier. I want to Eventually create a macro
that will allow me to create a report for a specific driver with one click
and then entering the DriverID, the Start Date, and the End Date.
Mastering Access one post at a time

 

[Allen Browne]

Presumably you have another table that has the driver's activities, and you
need to look up the table you described to get the rate to use on that date?
If so, a subquery should give you the desired rate.

This example assumes you have a query into a table named tblTimeSheet, that
has records of the driver's trips, and you need to lookup the pay rate for
the driver from your original table. You could do that by typing something
like this into the Field row of the query:

Rate: (SELECT TOP 1 Rate FROM tblDriverRate
WHERE (tblDriverRate.DriverID = tblTimeSheet.DriverID)
AND (tblDriverRate.RateDate < tblTimeSheet.WorkDate)
ORDER BY tblDriverRate.RateDate DESC, tblDriverRate.ID DESC)

Note that your original table (tblDriverRate) is not in the upper pane of
the query: it exists only in the subquery. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

The results will be read-only. It will be very much slower, but if you do
need an updatable query, you could use the ELookup() function from this
link:
http://allenbrowne.com/ser-42.html

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

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

message

Here' s what the data looks like on one record from the table I'm trying
to
query to compute payroll. Each record has over 30 fields, but I'm only
concerned with a basic strategy for now. Each word represents a field on a
record.

Vin#
DriverID1 Description1 Rate1 Date1
DriverID2 Description2 Rate2 Date2
DriverID3 Description3 Rate3 Date3

I want to be able to query a Rate if the corresponding DriverID and Date
fall within the parameters of my search. I want to show desired rates in a
pay report specific to a DriverID similar to the field layout that is
shown
above with all 4 fields in a row. The problem I'm running into is this:
When in Query design mode, how can I say in field 'Rate1' [Select rate if
field 'DriverID1' = DriverID 'X' and field 'Date1' = Between these dates.]
while simultaneously running the same equation for Rate2 and Rate3, all of
which are in the same query design? It seems like I'm looking for a way to
link fields within a query based on a compound conditional statement. Not
sure if this is possible. Any suggestions, perhaps for alternate table
designs that would make this easier. I want to Eventually create a macro
that will allow me to create a report for a specific driver with one click
and then entering the DriverID, the Start Date, and the End Date.
Mastering Access one post at a time