Access 2007 SubQuery Setup

How would I rewrite this query into VBA SQL statement?
query3 = tblVulnerabilityIndex

SELECT DISTINCT Query3.MaxOfLastupdate, tblVulnerabilityIndex.intCSPIdentifier, tblVulnerabilityIndex.intTotalScore
FROM tblVulnerabilityIndex INNER JOIN tblVulnerabilityIndex ON (tblVulnerabilityIndex.intCSPIdentifier = Query3.intCSPIdentifier) AND (tblVulnerabilityIndex.Lastupdate = Query3.MaxOfLastupdate)
GROUP BY Query3.MaxOfLastupdate, tblVulnerabilityIndex.intCSPIdentifier, tblVulnerabilityIndex.intTotalScore
HAVING (((tblVulnerabilityIndex.intCSPIdentifier)="105"));
jbakestullAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
I think this is what you are trying to do:

SELECT DISTINCT q3.MaxOfLastupdate, tblVulnerabilityIndex.intCSPIdentifier, tblVulnerabilityIndex.intTotalScore
FROM 
(SELECT MaxOfLastUpdate,intCSPIdentifier
FROM tblVulnerabilityIndex 
GROUP BY intCSPIdentifier) AS q3 
INNER JOIN tblVulnerabilityIndex ON (tblVulnerabilityIndex.intCSPIdentifier = q3.intCSPIdentifier) AND (tblVulnerabilityIndex.Lastupdate = q3.MaxOfLastupdate)
GROUP BY q3.MaxOfLastupdate, tblVulnerabilityIndex.intCSPIdentifier, tblVulnerabilityIndex.intTotalScore
HAVING (((tblVulnerabilityIndex.intCSPIdentifier)="105"));

Open in new window


In vba, the query string would be defined as:

strSQL = "SELECT DISTINCT q3.MaxOfLastupdate, " & _
"tblVulnerabilityIndex.intCSPIdentifier, tblVulnerabilityIndex.intTotalScore " & _
"FROM " & _
"(SELECT MaxOfLastUpdate,intCSPIdentifier " & _
"FROM tblVulnerabilityIndex " & _
"GROUP BY intCSPIdentifier) AS q3 " & _
"INNER JOIN tblVulnerabilityIndex ON " & _
"(tblVulnerabilityIndex.intCSPIdentifier = q3.intCSPIdentifier) AND  " & _
"(tblVulnerabilityIndex.Lastupdate = q3.MaxOfLastupdate) " & _
"GROUP BY q3.MaxOfLastupdate, tblVulnerabilityIndex.intCSPIdentifier, " & _ 
"tblVulnerabilityIndex.intTotalScore " & _
"HAVING (((tblVulnerabilityIndex.intCSPIdentifier)='105'));" 

Open in new window

0
jbakestullAuthor Commented:
Thanks for your help,, this is first time writing an SQL subquery statement.

from website http://allenbrowne.com/subquery-02.html
 
Im trying to write a subquery that uses the same tables as the main query. The problem with subquery is that its using the same fields in the main query, and the fields (from the same table) have the same names. The brief article mentions utlizaing an alais.

I redid the query to a simpler format

SELECT DISTINCT tblVulnerabilityIndex.intCSPIdentifier, tblVulnerabilityIndex.intTotalScore
FROM tblVulnerabilityIndex INNER JOIN Query3 ON (tblVulnerabilityIndex.intCSPIdentifier = Query3.intCSPIdentifier) AND (tblVulnerabilityIndex.Lastupdate = Query3.MaxOfLastupdate)
GROUP BY tblVulnerabilityIndex.intCSPIdentifier, tblVulnerabilityIndex.intTotalScore;

should query3 be replaced with dup.tblVulnerabilityIndex?

0
mbizupCommented:
Is your goal to rewrite the syntax for query3 as a true subquery?  If so, that Dupe example is not exactly what you are looking for.  That is for cases where you are joining a table or query on itself (ie: truly duplicating it).

Your case here sounds a little different in that you are not quite joining a table on itself... you are joining a table on a query that is based on the same table.

The syntax you posted looks good - as is, because you are using prefixes to indicate which table  or query fields are coming from.  Does it give you the results you are expecting?

In order to include the SQL for query3 as a subquery, you would have to do something like what I posted.  Did you test my suggestion?

Using the same table or query multiple times in a query is indeed feasible.  You do have to use aliases as the article said (and table name prefixes) to keep things straight and clear about which instances of the fields are coming from which instance of the tables.  The alias names can be anything you want (in my post, I used q3).

By the way, what is the SQL for query3? I made a guess based on the rest of the query.



0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

jbakestullAuthor Commented:
SQL for query 3 is

SELECT tblVulnerabilityIndex.intCSPIdentifier, Max(tblVulnerabilityIndex.Lastupdate) AS MaxOfLastupdate
FROM tblVulnerabilityIndex
GROUP BY tblVulnerabilityIndex.intCSPIdentifier
0
mbizupCommented:
This should do it...


SELECT DISTINCT q3.MaxOfLastupdate, tblVulnerabilityIndex.intCSPIdentifier, tblVulnerabilityIndex.intTotalScore
FROM 
(SELECT Max(Lastupdate) AS MaxOfLastUpdate,intCSPIdentifier
FROM tblVulnerabilityIndex 
GROUP BY intCSPIdentifier) AS q3 
INNER JOIN tblVulnerabilityIndex ON (tblVulnerabilityIndex.intCSPIdentifier = q3.intCSPIdentifier) AND (tblVulnerabilityIndex.Lastupdate = q3.MaxOfLastupdate)
GROUP BY q3.MaxOfLastupdate, tblVulnerabilityIndex.intCSPIdentifier, tblVulnerabilityIndex.intTotalScore
HAVING (((tblVulnerabilityIndex.intCSPIdentifier)="105")); 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jbakestullAuthor Commented:
wow,, thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.