grabbing a rate field

[Guest]

I have a table that has a client id, delivery yard#(can be more than one of
the same #, deliv yd name,pickup yard#(can be more than one of the same #),
and type of
material(2choices). Based on that combination of what is in those three
fields there is a given RATE. On the form based on this table I would like
the rate to automatically fill based on the combination of the 3 fields
chosen. Seems like it should be easy not sure where I go with this.



Thanks,
Barb

 

[Guest]

Hi

Can you post an example of the fields containing the rate. Also how you
equate the given rate for the job - ie.do you have a routing rate (cost per
mile, etc).

Doesn't look too difficult but would need a little more information to give
a good answer. Hope this is OK.

 

[Guest]

I have 3 tables

CLIENTTABLE
clientid-PK
clientnumb
clientname
address
state
etc

TBLTANKRATES2
rateid
clientid-pk
pickupyd-pk
pickupydname
delivyd-pk
delivydname
typeofmat-pk
rate

TANKTICKETENTRY
clientid-pk
ticket-pk
servicedate
typeofmat
pickupdyd
delivyd
truck#
gross weight
comment

I need to grab the rate value from the tanksrate2 table where the
clientid,pickupyd, delivyd, and typeofmat are the same.

Joins for query clienttable (clientid-clientid)tankticketentry
tankrate2 (clientid-clientid)tankticketntry
tankrate2 (pickupyd-pickupyd)tankticketntry
tankrate2 (delivyd-delivyd)tankticketntry
tankrate2 (typeofmat-typeofmat)tankticketntry

The query worked fine except there may be a situation where there may be a
pickup yd with the same # but different pickupydname and same for deliv. so
I made the pickupydname and delivydname primary keys also in the tankrate2
table and now the query is not updatable. not sure how to lay this out. The
rate is not a calculated field just based on the clientid,
pickupyd,pickupydname,delivyd,delivydname, and typeofmat. want on the form
based on this query for the rate to automatically fill in based on all of
these items.

Thanks for your help,
Barb