We help IT Professionals succeed at work.

Creating cascading queries in vba

rogerdjr
rogerdjr asked
on
I created two queries in Access

The first is

SELECT CASp_StandardChecklistIndexTbl.CheckListIndexRecId, CASp_StandardChecklistIndexTbl.CheckListTitle, CASp_StandardChecklistRefCodeSectTbl.CheckListRedId, CASp_StandardChecklistTbl.CheckListIndexRecId, CASp_StandardChecklistTbl.CheckListItemSeqNo, CASp_StandardChecklistTbl.CheckListItemBriefDescr, CASp_StandardChecklistRefCodeSectTbl.CheckListRefCodeSectRefRecNo, CASp_StandardChecklistRefCodeSectTbl.CodeSectRefRecNo, CASp_CodeSectListTbl.Code, CASp_CodeSectListTbl.CodeSect, CASp_CodeSectListTbl.CodeSubSectId, CASp_StandardChecklistRefCodeSectTbl.DateEntered, CASp_StandardChecklistRefCodeSectTbl.DateRevised, CASp_StandardChecklistRefCodeSectTbl.RevisedByUserId
FROM CASp_StandardChecklistIndexTbl INNER JOIN ((CASp_StandardChecklistRefCodeSectTbl LEFT JOIN CASp_CodeSectListTbl ON CASp_StandardChecklistRefCodeSectTbl.CodeSectRefRecNo = CASp_CodeSectListTbl.CodeSectRefRecNo) INNER JOIN CASp_StandardChecklistTbl ON CASp_StandardChecklistRefCodeSectTbl.CheckListRedId = CASp_StandardChecklistTbl.CheckListRecId) ON CASp_StandardChecklistIndexTbl.CheckListIndexRecId = CASp_StandardChecklistTbl.CheckListIndexRecId
ORDER BY CASp_StandardChecklistIndexTbl.CheckListIndexRecId, CASp_StandardChecklistTbl.CheckListItemSeqNo;

The second is a cross tab query based on the first

TRANSFORM Count([_CheckListCodeRefListQry].CodeSectRefRecNo) AS CountOfCodeSectRefRecNo
SELECT [_CheckListCodeRefListQry].CASp_StandardChecklistIndexTbl.CheckListIndexRecId, [_CheckListCodeRefListQry].CheckListTitle, [_CheckListCodeRefListQry].CheckListRedId, [_CheckListCodeRefListQry].CheckListItemSeqNo, [_CheckListCodeRefListQry].CheckListItemBriefDescr, Count([_CheckListCodeRefListQry].CodeSectRefRecNo) AS [Total Of CodeSectRefRecNo]
FROM _CheckListCodeRefListQry
GROUP BY [_CheckListCodeRefListQry].CASp_StandardChecklistIndexTbl.CheckListIndexRecId, [_CheckListCodeRefListQry].CheckListTitle, [_CheckListCodeRefListQry].CheckListRedId, [_CheckListCodeRefListQry].CheckListItemSeqNo, [_CheckListCodeRefListQry].CheckListItemBriefDescr
PIVOT [_CheckListCodeRefListQry].Code;

I want to use vba to create these queries to make a report - is ther a way to create the cascading queries in vba so I don';t have to rely on the saved queries?
Comment
Watch Question

View Solution Only

Daniel PineaultPresident / Owner CARDA Consultants Inc.
BRONZE EXPERT
Distinguished Expert 2018

Commented:

How exactly are you wanting to use the query?

Chief Technology Officer
BRONZE EXPERT
Commented:
We need to nail down just what it is you want to do.  Here are the options based on the info you've given:
1.  Build the query SQL strings  in VBA code, using the 1st SQL query string as the source in the FROM statement of the 2nd query:
"FROM (" & strSQL1 & ") AS [_CheckListCodeRefListQry]"

Open in new window

and set the report's Recordsource property to the completed SQL string (you may have to use alias on your table names to get the string length to one below the Recordsource max length).
2.  Build the query SQL strings in code and create/modify a querydef SQL property with the SQL strings:
Currentdb.Tabledefs("qryDefNameHere").SQL = strSQL1

Open in new window

and do the same for the crosstab query, and use the querydef as the report Recordsource.
3.  You could put the combined SQL from #1 in its own querydef and use that querydef as the recordsource.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
BRONZE EXPERT
Distinguished Expert 2018

Commented:

I was thinking the same thing as Mark, but then this goes against your initial requirement " so I don';t have to rely on the saved queries ".  This is why I'm trying to understand your end game, how you need to use the results exactly to try and best guide you.

rogerdjr

Author

Commented:
I want to export the crosstab query to a spreadsheet
Mark EdwardsChief Technology Officer
BRONZE EXPERT

Commented:
If you are going to export the query data to a spreadsheet using Transferspreadsheet, then you'll need to use a querydef as the datasource.
Another option is to use the Excel "CopyFromRecordset() function, but that only exports the data, not the column headings, and uses a recordset, not a querydef as the data source.