How to join these queries...

[fl]

I am using Access 2002. Can someone show me some examples of handling
the following situation:

strSQL1 = "SELECT DISTINCT tblSerDef.SERIES FROM tblSerDef GROUP BY
tblSerDef.SERIES " & _
"HAVING (((tblSerDef.SERIES) In (SELECT tblSerDef.SERIES FROM
tblSerDef " & _
"GROUP BY tblSerDef.SERIES, tblSerDef.GroupID HAVING
(((tblSerDef.GroupID) = '" & Me!ListID & "')))))"

strSQL2 = "SELECT DISTINCT tblSerDef.GroupID, tblSerDef.SERIES FROM
tblSerDef " & _
"GROUP BY tblSerDef.GroupID, tblSerDef.SERIES"

strSQL3 = "SELECT DISTINCT [Query1+].GroupID FROM Query1 INNER JOIN
[Query1+] ON Query1.SERIES = [Query1+].SERIES " & _
"GROUP BY [Query1+].GroupID HAVING
(((Count(Query1.SERIES))=(SELECT COUNT(*) FROM Query1)))"

Me!List127.RowSourceType = "Table/Query"
Me!List127.RowSource = strSQL3

The results of strSQL3 will be the RowSource for a list box. strSQL3 is
the result of a joint query from strSQL1 and strSQL2.

I am not clear about ADO vs. DAO and I don't know the syntax to provide
the results. Please advice. Thanks.

Faye

 

[Tim Ferguson]

strSQL1 = _
"SELECT DISTINCT SERIES FROM tblSerDef " & _
"GROUP BY SERIES " & _
"HAVING SERIES IN (" & _
" SELECT SERIES " & _
" FROM tblSerDef " & _
" GROUP BY SERIES, GroupID " & _
" HAVING GroupID = '" & Me!ListID & "'" & _
")"

Have you tested this? I don't think it's a legal query because the inner
reference to tblSerDef should need aliasing. There is no mention of the
GroupID in the column list of the second SELECT but it is mentioned in the
Grouping clause. In addition, the DISTINCT is redundant if you are
GROUPing on the same column (or in this case it's probably the grouping
that is not necessary. I cannot see what the outer SELECT does that this
doesn't:-

select distinct series from tblserdef
where groupid = 'something';

strSQL2 = _
"SELECT DISTINCT GroupID, SERIES " & _
"FROM tblSerDef " & _
"GROUP BY GroupID, SERIES"

Once again, this produces a distinct list of GroupID and Series and the use
of both DISTINCT and GROUP BY is redundant. Legal though, as far as I can
tell.

strSQL3 = "SELECT DISTINCT [Query1+].GroupID FROM Query1 INNER JOIN
[Query1+] ON Query1.SERIES = [Query1+].SERIES " & _
"GROUP BY [Query1+].GroupID HAVING
(((Count(Query1.SERIES))=(SELECT COUNT(*) FROM Query1)))"

hmmm: no. If these things really needed to be split, you'd have to create
QueryDefs to hold them and join those. Whatever this code is doing, it's
almost certainly easier to make a single SQL command and use that. Is there
a design problem?

B Wishes


Tim F