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?
 
mbizupConnect With a Mentor Commented:
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
jbakestullAuthor Commented:
SQL for query 3 is

SELECT tblVulnerabilityIndex.intCSPIdentifier, Max(tblVulnerabilityIndex.Lastupdate) AS MaxOfLastupdate
FROM tblVulnerabilityIndex
GROUP BY tblVulnerabilityIndex.intCSPIdentifier
0
 
jbakestullAuthor Commented:
wow,, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.