Auto rating a client with collected points base in a rating table

[Simon W]

Hi,
I have access tables collected points from my client and want to auto auto
rate them base on a rating table, but rating field I can manual over write
it if I want to. How to set the code?
2 table are: ClientInfo table and Rating table
ClientInfo table with 3 fields:
ClientID Point Rating
1 10 A
2 05 A
3 11 B
4 50 C <----- over writed from F
5 32 D

Rating table with 3 fields: MinRate and MaxRate from 1 to 100 may change
value from times, field SetRate values from A to Z
SetRate MinRate MaxRate
A 01 10
B 11 20
C 21 30
D 31 35
E 36 40
F 41 50

Thanks in advance.
Simon

 

[Rob Oldfield]

You don't need code to do it.... a query will do the job....

SELECT ClientInfo.ClientID, Min(Rating.SetRate) AS Rating
FROM ClientInfo, Rating
WHERE (((ClientInfo.Point)<=[MaxRate]))
GROUP BY ClientInfo.ClientID;

is the straight select version. You could turn it into a make table or use
the select to run an update query very easily. Note that the field
MinRating is superfluous.

 

[Simon]

This is great feature but not the answer that I expect. what I need is:
To list all clients with a colume list all customers rating automaticaly
when open a form base on the tRating table, but Rate field I can manually
individual over
write them if need by other special factor. would this need a modules,
macro, or queries in my form? How to set the code?

2 table are: tClientInfo table and tRating table
tClientInfo table with 3 fields:
ClientID Point Rate
1 10 F
2 05 F
3 11 E
4 50 C <----- should be A but I want to over writed
to C
5 32 C

tRating table with 3 fields: MinRate and MaxRate from 1 to 100 may change
value from times, field SetRate values from A to Z
SetRate MinRate MaxRate
F 01 10
E 11 20
D 21 30
C 31 35
B 36 40
A 41 50

Thanks You very much.
Simon

You don't need code to do it.... a query will do the job....

SELECT ClientInfo.ClientID, Min(Rating.SetRate) AS Rating
FROM ClientInfo, Rating
WHERE (((ClientInfo.Point)<=[MaxRate]))
GROUP BY ClientInfo.ClientID;

is the straight select version. You could turn it into a make table or use
the select to run an update query very easily. Note that the field
MinRating is superfluous.

Hi,
I have access tables collected points from my client and want to auto auto
rate them base on a rating table, but rating field I can manual over write
it if I want to. How to set the code?
2 table are: ClientInfo table and Rating table
ClientInfo table with 3 fields:
ClientID Point Rating
1 10 A
2 05 A
3 11 B
4 50 C <----- over writed from F
5 32 D

Rating table with 3 fields: MinRate and MaxRate from 1 to 100 may change
value from times, field SetRate values from A to Z
SetRate MinRate MaxRate
A 01 10
B 11 20
C 21 30
D 31 35
E 36 40
F 41 50

Thanks in advance.
Simon