Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

joins using 'Between' logic 3

Status
Not open for further replies.

1DMF

Programmer
Jan 18, 2005
8,795
GB
Is it possible to create a join using a 'between' operator?

I had the following...

Code:
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....
Code:
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
 


hi,

How about...
Code:
SELECT
  SUM(Rating) / COUNT(Rating) AS iCnt
, Val 
FROM
  Compliance_Audit
, RAG_Rating
WHERE iCnt BETWEEN MinAvg AND MaxAvg
assuming that MinAvg and MaxAvg are table fields.

BTW, This will perform a CARTESIAN JOIN. Is that what you want?

BTW, storing aggregations in tables is not a particularly good practice.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
You can't JOIN on aggregate functions.
You can't use aggregate functions in a WHERE clause but in a HAVING clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Wow , CARTESIAN JOIN, never heard of them before, but I bet I soon will in the next section of my OU course.

We are currently learning about PREDICATE CALCULUS (Logic), and Cartesian Products (x-tuples) were at the beggining of the course and I know Databases is coming up.

Looking forward to that part of the course then [thumbsup]

Though I'm not sure it's what I want, I omitted the WHERE clause that I actually need which is against the Audit table!

Here is the full query
Code:
SELECT SUM(Rating) / COUNT(Rating) AS iCnt, Val FROM Compliance_Audit INNER JOIN RAG_Rating ON (iCnt >= MinAvg AND iCnt <= MaxAvg) WHERE [CDate Visited] >= DateAdd('d', -183, Now()) AND ContactID= " & CID & " AND Overridden Is Null

Oh and don't worry it's not an aggregation table, it's a range to provide a value based on the aggregation of the SUM / COUNT ;-)

"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
 
You use two aggregate functions: SUM and COUNT.
You omit the GROUP BY clause.
You can't use iCnt in a JOIN nor in a WHERE clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
My guess about what you want:
Code:
SELECT SUM(Rating) / COUNT(Rating) AS iCnt, Val
FROM Compliance_Audit, RAG_Rating
WHERE [CDate Visited] >= DateAdd('d', -183, Now()) AND ContactID= " & CID & " AND Overridden Is Null
GROUP BY Val
HAVING  (SUM(Rating) / COUNT(Rating)) BETWEEN MinAvg AND MaxAvg

BTW, what is CID ?

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


Where do MinAvg & MaxAvg come from?

You need to Google Cartesian Join. It is when there is no join between two tables. It results in every row or table A joined to every row in table B

So if I had this
[tt]
Table A
Name
Al
Bob

Table B
Name
Al
Bob
Cal
[/tt]
then
Code:
Select *
From [Table A], [Table B]
result
[tt]
Name Name
Al Al
Al Bob
Al Cal
Bob Al
Bob Bob
Bob Cal
[/tt]
Your Between is not really a Join. It is a Criteria.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Min / Max are colums in a table that stores some ranges so when the calcualtion of the agregation is performed, it is then evaluated to provide a single integer value (and no rounding won't do the job!).

Sorry PHV forgot I need to use the expression not the alias!

Though I'm still unsure about the HAVING clause, isn't that similar to a WHERE, i.e it forms part of the criteria of the records that will be selected?

I don't want records selected based on this aggegation, I want to join the tables so I can look up the value I want after the records are selected and the calculation is performed.

Or am I not understanding the HAVING clause?

CID , it is an integer value passed to the method and the SQL is for the recordset it will produce.

But I am still doing it wrong and inefficiently, getting my head round the logic for this new report i'm finding to be rather tricky.

I was using an expression in an Access Query that calls the function passing in ContactID for each record retrieved in the query.

I konw it is going to grind Access to a halt, but I was just getting my head round how to link it all together.

I'm thinking that really I need to create a separate query that will be used as a table join in the main report query.

I have a main table File_Checks which the report needs as its recordset, but part of the output needs to obtain the aggregation (and then the actual integer value from RAG_Rating)

As I know aggregation values stored in a table is bad practice, it needs to be calcualted on the fly, so I'm thinking that I need a query that calculates all member's RAG rating (that meet the date criteria), and then join that against the real record selection for the report.

It's going to be quicker than trying to call a query function and calculate it that way!

so what I really want from this query is ...

Code:
SELECT SUM(Compliance_Audit.Rating) / COUNT(Compliance_Audit.Rating) AS iCnt, RAG_Rating.Val, Compliance_Audit.ContactID
FROM Compliance_Audit 
JOIN RAG_Rating 
ON (iCnt BETWEEN RAG_Rating.MinAvg AND RAG_Rating.MaxAvg) 
WHERE Compliance_Audit.[CDate Visited] >= DateAdd('d', -183, Now()) AND Compliance_Audit.Rating > 0 AND Compliance_Audit.Overridden Is Null
GROUP BY Compliance_Audit.ContactID

How would I do this?


"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
 
To put it another way....

Code:
SELECT SUM(Compliance_Audit.Rating) / COUNT(Compliance_Audit.Rating) AS iCnt, Compliance_Audit.ContactID
FROM Compliance_Audit 
WHERE Compliance_Audit.[CDate Visited] >= DateAdd('d', -183, Now()) AND Compliance_Audit.Rating > 0 AND Compliance_Audit.Overridden Is Null
GROUP BY Compliance_Audit.ContactID

But included in that recordset I want Val form Rag_Rating...
Code:
SELECT Val 
FROM RAG_Rating 
WHERE iCnt BETWEEN MinAvg AND MaxAvg

Does that make it any clearer?

"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
 
Code:
SELECT SUM(A.Rating) / COUNT(A.Rating) AS iCnt, A.ContactID, R.Val
FROM Compliance_Audit A, RAG_Rating R
WHERE A.[CDate Visited] >= DateAdd('d', -183, Now()) AND A.Rating > 0 AND A.Overridden Is Null
GROUP BY A.ContactID, R.Val
HAVING  (SUM(A.Rating) / COUNT(A.Rating)) BETWEEN R.MinAvg AND R.MaxAvg

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Awesome PHV!

I won't know if it works till I get in work tomorrow, but I think I get it.

I did a little googling on WHERE vs HAVING and although initially I was correct in beleiving WHERE = HAVING , when using aggregation, this is not the case..


So it will perform the calculation / aggregation then discard those that don't meet the HAVING clause.

Which actually won't be any records, because if there is a record in A, there is a matching one in R!



"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
 
Cannot get it to work?

I have pasted it into the SQL View, but get the following error when run

you tried to execute a query that does not include the specified expression SUM(A.Rating) / COUNT(A.Rating)) BETWEEN R.MinAvg AND R.MaxAvg as part of the aggregate function


"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
 
Try this:
Code:
SELECT SUM(A.Rating) / COUNT(A.Rating) AS iCnt, A.ContactID, R.Val
FROM Compliance_Audit A, RAG_Rating R
WHERE A.[CDate Visited] >= DateAdd('d', -183, Now()) AND A.Rating > 0 AND A.Overridden Is Null
GROUP BY A.ContactID, R.Val, R.MinAvg, R.MaxAvg
HAVING  (SUM(A.Rating) / COUNT(A.Rating)) BETWEEN R.MinAvg AND R.MaxAvg

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I've tried direct in SQL Studio, same error.

So I added the aggregartion 'SUM(A.Rating) / COUNT(A.Rating) BETWEEN R.MinAvg AND R.MaxAvg'to the group by clause and now it errors with
Incorrect syntax near the keyword 'Between'.





"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
 
sorry, PHV , was messing about and didn't see that post.

Just tried it and BINGO!

you're one smart cookie! No wonder you've just been voted tip master of the week!

Many, Many thanks!

"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
 
I had to come back and say how freaking Awesome you are PHV.

I've just integrated the code to a separate query to then join with the main reporting tables.

It's lightening fast, does exactly what I want and I cannot thank you enough.

You've made me and my boss very happy indeed!

You are a
star.gif
star.gif
star.gif
star.gif
star.gif
star.gif
star.gif
star.gif
^1,000,000

"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
 
Dang, I thought it was working but it isn't.

I'm not getting the correct rating from Rag_Rating?

The result from the query for a specific contact is...

iCnt ContactID Val RAG
2.41176470588235 12368 1 Green

Yet the lookup table for Rag_Rating is...
RAG MinAvg MaxAvg Val
Amber 2.1 3.5 3
Green 1 2 1
Red 3.6 5 5

So therefore I should get a 3 (Amber) returned?



"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
 
Note : If I run the query direct in SQL Studio , iCnt even gets rounded?

ContactID Val RAG iCnt
12368 1 Green 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!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta
 
I seem to have resolved it in Access by using
Code:
iCnt: Format(Sum([Rating])/Count([Rating]),"Standard")

I will get this report working by the end of the day or that open window is looking very inviting!

"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
 
You should round your results to a single decimal based on your table setup. I think Standard will use 2 decimals and a value like 3.55 will not fit into any of your ranges and the record will be excluded from the report. Rounding insteading of using Format() will keep the value a number and Access won't have to convert back for the Between comparison with your range numbers.

Round( Sum([Rating])/Count([Rating]), 1 )
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top