Access query

[leangross]

tblRATE ---> rate table
fields:
1. rate
2. fromRANGE
3. toRANGE

tblCustomer
fields:
1. ac_no
2. category

I want to make a query that will get the rate value from tblRATE of all the customer from tblCUSTOMER. The category field in tblcustomer should be between or equal to the fromRANGE and toRANGE in tblRATE. My problem is how to do that in queries.

It's easy to make a code on that but i need a query in access.

Hope you can help me guys. Thanks in advance!!!

 

[johnnymac43]

Not very elegant, but this will do the job.

SELECT tblCustomer.ac_no, tblRATE.rate
FROM tblCustomer, tblRATE
WHERE ((([tblCustomer]![category]=[tblRATE]![fromRANGE] Or [tblCustomer]![category]>[tblRATE]![fromRANGE]) And ([tblCustomer]![category]=[tblRATE]![toRANGE] Or [tblCustomer]![category]<[tblRATE]![toRANGE])));

Cheers

John

 

[JeremyNYC]

SELECT tblCustomer.ac_no, tblRATE.rate
FROM tblCustomer, tblRATE
WHERE (([tblCustomer]![category]>=[tblRATE]![fromRANGE]) And ([tblCustomer]![category]<=[tblRATE]![toRANGE]));

should do it.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[leangross]

THANKS!!!!!!