Avatar of rogerdjr
rogerdjr
Flag for United States of America asked on

Creating cascading queries in vba

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?
VBA* Access

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon
Daniel Pineault

How exactly are you wanting to use the query?

ASKER CERTIFIED SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Daniel Pineault

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

ASKER
I want to export the crosstab query to a spreadsheet
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mark Edwards

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.