Base a query on a query

#1

I have a query qry2 which is based on query qry1. Both qry1 and qry2
are defined in Access. I would like to use parameters for both
queries. I indicate below a simple verson of the queries as they
appear in Access:
qry1:
SELECT * FROM tblX
WHERE (tblX.[fldName]) Is Not Null);

qry2:
INSERT INTO tblY ( fld1, fld2, fld3)
SELECT qry1.[fld1], qry1.[fld2], qry1.[fld3] FROM qry1;"

Is there a simple way to incorporate qry1 into qry2 so that I can use
DoCmd.RunSQL to run qry2?

Thanks,
John

#2

Per JohnP:

Quote:

I have a query qry2 which is based on query qry1. Both qry1 and qry2
are defined in Access. I would like to use parameters for both
queries. I indicate below a simple verson of the queries as they
appear in Access:
qry1:
SELECT * FROM tblX
WHERE (tblX.[fldName]) Is Not Null);

qry2:
INSERT INTO tblY ( fld1, fld2, fld3)
SELECT qry1.[fld1], qry1.[fld2], qry1.[fld3] FROM qry1;"

Is there a simple way to incorporate qry1 into qry2 so that I can use
DoCmd.RunSQL to run qry2?

Yes, by using the UI's query builder screens, specifying the
parameters via Query | Parameters and saving the resulting
queries.

If you run Qry2 "by hand", it will then prompt for the parms.

If you run qry2 in VBA, you will:

-----------------------------------
Dim myRS as DAO.Recordset
Dim myQury As DAO.QueryDef

Set myQuery = CurrentDB.QueryDefs("qry2"
With myQuery
.Parameters("theParm1") = Whatever
.Parameters("theParm2") = Whatever
set myRS = .OpenRecordset(dbOpenSnapshot, dbForwardonly)
End With
-----------------------------------
--
PeteCresswell

#3

Pete,

Many thanks for your reply. I include the actual queries. Would like to include Query5 in Query6 and use VBA to run. The numbers used for tables and fields represent dates (it's a long story)

Query5:
SELECT * FROM 11030204
WHERE ((([11030204].[000111022001]) Is Not Null And ([11030204].[000111022001])<=nz([11030204].[001011022402]) And ([11030204].[000111022001])<=nz([11030204].[000211022301])));

Query6:
INSERT INTO tblLC ( strDescription, strAC, sngLCPrice ) SELECT Query5.strDescription, Query5.strAC, Query5.[000111022001] FROM Query5;"

Would your suggestion work in this case? Do you have something in ADO?

Thanks,
John

#4

One thing I forgot to mention is that the table in Query 5 (in his case) 11030204 (will be different name in another run), can have a variable number of fields.

#5

Also the checks you see above would need to be carried out dynamically for all fields. Basically Query5 filters all records where field 000111022001 has the smallest value of the 3 fields (the other two fields being 001011022402, 000211022301)

#6

Why do you need qry 1 at all?

INSERT INTO tblY ( fld1, fld2, fld3)
SELECT fld1, fld2, fld3 FROM tblX
WHERE tblX.[fldName] Is Not Null

That being said, reading your other comments that the table name will be
different in another run and that it will have a variable number of fields,
coupled with table and field names which represent dates makes me suspect
that your database isn't properly normalized.

"JohnP" wrote in message
news:d4fe9a2b-464b-475b-9173-53b94bad9056 (AT) v16g2000vbq (DOT) googlegroups.com...

I have a query qry2 which is based on query qry1. Both qry1 and qry2
are defined in Access. I would like to use parameters for both
queries. I indicate below a simple verson of the queries as they
appear in Access:
qry1:
SELECT * FROM tblX
WHERE (tblX.[fldName]) Is Not Null);

qry2:
INSERT INTO tblY ( fld1, fld2, fld3)
SELECT qry1.[fld1], qry1.[fld2], qry1.[fld3] FROM qry1;"

Is there a simple way to incorporate qry1 into qry2 so that I can use
DoCmd.RunSQL to run qry2?

Thanks,
John

#7

Also the checks you see above would need to be carried out dynamically for all fields. Basically Query5 filters all records where field 000111022001 has the smallest value of the 3 fields (the other two fields being 001011022402, 000211022301)

To summarize, Query5 is not fixed, with a variable number of fields. That is why I need to handle matters in VBA.

#8

JohnP wrote:

Quote:

Also the checks you see above would need to be carried out dynamically for all fields. Basically Query5 filters all records where field 000111022001 has the smallest value of the 3 fields (the other two fields being 001011022402, 000211022301)

You could modify the query5's SQL especially since you are changing
tables, maybe fields. Make a copy of Query5 to test. Ex:
Sub QSQL()
Dim q As QueryDef
Set q = CurrentDb.QueryDefs("Query5Copy")
MsgBox q.SQL
q.SQL = "Select * From Junk"
MsgBox "The new SQL statement is " & q.SQL
Set q = Nothing
End Sub
If you attempt to open Query5 now it has the updated SQL statement.

#9

The checks you see above would need to be carried out dynamically for all fields. Basically Query5 filters all records where field 000111022001 has the smallest value of the 3 fields (the other two fields being 001011022402, 000211022301)

To summarize, Query5 is not fixed and has a variable number of fields. That is why I need to handle matters in VBA.

#10

Salad,
Apologies for the various messages, I seem to have experienced some delays. Maybe I can redefine the problem in a simpler manner.

Query5 is not a query with a fixed number of fields. It contains a Description field, a telephone code field and an N number of Price fields. If we are comparing 5 providers then there will be 5 extra fields. Deciphering
[11030204].[000111022001]
[11030204] = The 4th run (04) on 02-March-11 (11030204)
[000111022001] => 0001 = Provider #1 11022001 = Pricelist 01 on 20-Feb-11

In Query6 I invoke "...SELECT Query5.strDescription ...". If Query5 was fixed there would be no problem, but it is not. I basically need to dynamically describe Query5 in VBA depending on the situation and refer to it in Query6.

Hope this helps,
John