Converting Dependent Queries to SQL?

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi,

Can I convert two dependent queries (i.e. Query1 selects from Query2) to
SQL in VBA code? Can I use a sub query in a FROM clause?

TIA,
Dan
 
Disagree Yes and Probably.

It depends on the naming scheme of the fields. Since the OP didn't give us any
details, on his query structures, I guess he/she is free to experiment.
 
John Spencer (MVP) said:
Disagree Yes and Probably.

It depends on the naming scheme of the fields. Since the OP didn't give us any
details, on his query structures, I guess he/she is free to experiment.
Click to expand...

< g > I was being optimistic!
 
Here are my two queries that I want to convert to VBA SQL code:

Query1: SELECT tblRatings.RatingSessionID, tblRatings.SupplierID,
tblSuppliers.SupplierName, tblRatings.UserName, Sum(([Rating]*[Weight]))
AS Score, Sum((10*[Weight])) AS MaxScore,
Format([Score]/[MaxScore],'Fixed') AS [Score %]
FROM tblSuppliers INNER JOIN (tblCriteria RIGHT JOIN tblRatings ON
tblCriteria.CriteriaNumber = tblRatings.CriteriaNumber) ON
tblSuppliers.SupplierID = tblRatings.SupplierID
GROUP BY tblRatings.RatingSessionID, tblRatings.SupplierID,
tblSuppliers.SupplierName, tblRatings.UserName
HAVING (((Count(tblRatings.Rating))=51));

Query2: SELECT qryCompletedSurveyScores.RatingSessionID,
qryCompletedSurveyScores.SupplierID,
qryCompletedSurveyScores.SupplierName,
Avg(qryCompletedSurveyScores.Score) AS ScoreAvg,
Last(qryCompletedSurveyScores.MaxScore) AS MS,
Format([ScoreAvg]/[MS],'Fixed') AS [Score %],
Count(qryCompletedSurveyScores.Score) AS SC
FROM qryCompletedSurveyScores
GROUP BY qryCompletedSurveyScores.RatingSessionID,
qryCompletedSurveyScores.SupplierID, qryCompletedSurveyScores.SupplierName
HAVING (((qryCompletedSurveyScores.RatingSessionID) Like "*"))
ORDER BY Avg(qryCompletedSurveyScores.Score) DESC;

When I try to put query1's sql into query2's FROM clause I get "error in
FROM clause". Can this be done?

Dan
 
Yes with some modifications. You cannot have any square braces in the subquery,
so you will have to rename some of your fields to get rid of the spaces and
non-standard alpha characters.

[SELECT tblRatings.RatingSessionID, tblRatings.SupplierID,
tblSuppliers.SupplierName, tblRatings.UserName, Sum(Rating*Weight)
AS Score, Sum(10*[Weight) AS MaxScore,
Format(Score/MaxScore,'Fixed') AS ScorePercent
FROM tblSuppliers INNER JOIN (tblCriteria RIGHT JOIN tblRatings ON
tblCriteria.CriteriaNumber = tblRatings.CriteriaNumber) ON
tblSuppliers.SupplierID = tblRatings.SupplierID
GROUP BY tblRatings.RatingSessionID, tblRatings.SupplierID,
tblSuppliers.SupplierName, tblRatings.UserName
HAVING Count(tblRatings.Rating)=51]. As Q

Note the Square Brackets and the period immediately after the closing bracket.
So your query statement ends up looking something like the following.


SELECT ...
FROM tblSuppliers INNER JOIN <TheAbove>
on TblSuppliers.RatingSessionID = Q.RatingSessionID
....

I'm not sure what fields would be involved in your actual join but the above
should give you the idea.
Here are my two queries that I want to convert to VBA SQL code:

Query1: SELECT tblRatings.RatingSessionID, tblRatings.SupplierID,
tblSuppliers.SupplierName, tblRatings.UserName, Sum(([Rating]*[Weight]))
AS Score, Sum((10*[Weight])) AS MaxScore,
Format([Score]/[MaxScore],'Fixed') AS [Score %]
FROM tblSuppliers INNER JOIN (tblCriteria RIGHT JOIN tblRatings ON
tblCriteria.CriteriaNumber = tblRatings.CriteriaNumber) ON
tblSuppliers.SupplierID = tblRatings.SupplierID
GROUP BY tblRatings.RatingSessionID, tblRatings.SupplierID,
tblSuppliers.SupplierName, tblRatings.UserName
HAVING (((Count(tblRatings.Rating))=51));

Query2: SELECT qryCompletedSurveyScores.RatingSessionID,
qryCompletedSurveyScores.SupplierID,
qryCompletedSurveyScores.SupplierName,
Avg(qryCompletedSurveyScores.Score) AS ScoreAvg,
Last(qryCompletedSurveyScores.MaxScore) AS MS,
Format([ScoreAvg]/[MS],'Fixed') AS [Score %],
Count(qryCompletedSurveyScores.Score) AS SC
FROM qryCompletedSurveyScores
GROUP BY qryCompletedSurveyScores.RatingSessionID,
qryCompletedSurveyScores.SupplierID, qryCompletedSurveyScores.SupplierName
HAVING (((qryCompletedSurveyScores.RatingSessionID) Like "*"))
ORDER BY Avg(qryCompletedSurveyScores.Score) DESC;

When I try to put query1's sql into query2's FROM clause I get "error in
FROM clause". Can this be done?

Dan

Disagree Yes and Probably.

It depends on the naming scheme of the fields. Since the OP didn't give us any
details, on his query structures, I guess he/she is free to experiment.
Click to expand...
Click to expand...
 
Back
Top