Link to home
Create AccountLog in
Avatar of rogerdjr
rogerdjrFlag 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?
Avatar of Daniel Pineault
Daniel Pineault

How exactly are you wanting to use the query?

ASKER CERTIFIED SOLUTION
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

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.

Avatar of rogerdjr

ASKER

I want to export the crosstab query to a spreadsheet
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.