joins using 'Between' logic 3

[1DMF]

Is it possible to create a join using a 'between' operator?

I had the following...

SELECT SUM(Rating) / COUNT(Rating) AS iCnt, Val FROM Compliance_Audit INNER JOIN RAG_Rating ON (iCnt BETWEEN MinAvg AND MaxAvg)

But that seemed to error, so I did a bit of Googling and found suggestions to change it to a range, so I tried this....

SELECT SUM(Rating) / COUNT(Rating) AS iCnt, Val FROM Compliance_Audit INNER JOIN RAG_Rating ON (iCnt >= MinAvg AND iCnt <= MaxAvg)

But that is erroring as the other syntax with

join expression not supported.

Does this mean it is a limitation of the JET driver and if I created a stored procedure in SQL it would be fine?

Or have I got the syntax wrong?

Thanks,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

Correct, Standard uses 2 decimal places and it now seems to be working fine.

In fact it was originally working in Access without any formating, then I started work on the actual report, complied the DB and suddenly it stopped giving correct results.

Which is why I thanked PHV, then came back when it broke, it was bizzare, why work one minute but not the next?

It also doesn't explain why when run direct in SQL I get total truncation?

Looking into Round, it doesn't make sense...

Round (12.55, 1) would return 12.6 (rounds up) 
Round (12.65, 1) would return 12.6 (rounds down) 
Round (12.75, 1) would return 12.8 (rounds up)

12.65 Rounded, should be 12.7?

I guess currently the old addage is true - if it aint broke!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta
[url=http://d

 

[PHV]

Access use Bankers rounding.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

You mean access likes to rob your pocket?

Any idea why SQL is truncating and why Access worked at first then stopped?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta
[url=http://d

 

[1DMF]

JonFer , to be on the safe side and as it was the simplest fix, I changed the scale in SQL to 2 ;-)

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads