How Can I Create a Query Based on an Access Crosstab Query and Handle the Varying Column Headings in the Crosstab?
I have an Access query that is based on a crosstab query. The query "breaks" whenever the columns in the crosstab query change. I have tried using asterisk, but I do not want all the columns in the crosstab query to appear in the result of the main query.
In the figure, tblMatrix is the result of a Crosstab query that I have written out to a table. I want the PartNumber and PubDateMonday to appear first, and I want the data in order by those fields. I then want the QtyReceived and Overdue expressions to appear. I do not want the row number to appear in the query output.
In the figure, tblMatrix is the result of a Crosstab query that I have written out to a table. I want the PartNumber and PubDateMonday to appear first, and I want the data in order by those fields. I then want the QtyReceived and Overdue expressions to appear. I do not want the row number to appear in the query output.
In design view of the crosstab, go to Properties and specify under Shown Column Headers (option 3) the columns you wish to retrieve.
/gustav
/gustav
Alison,
What does the CrossTab query look like?
Specifically, where does the RowNumber column in tblMatrix come from? If that Column is one of the values in the field you are pivoting on in your cross-tab query, then you could eliminate the column from the result set by including a where clause in the CrossTab.
If it is not coming from the pivot column, just exclude that column from the cross-tab query design.
What does the CrossTab query look like?
Specifically, where does the RowNumber column in tblMatrix come from? If that Column is one of the values in the field you are pivoting on in your cross-tab query, then you could eliminate the column from the result set by including a where clause in the CrossTab.
If it is not coming from the pivot column, just exclude that column from the cross-tab query design.
ASKER
This crosstab query is the result of some fairly complex logic that imports several text files and applies a series of variable criteria. I use a make table query as the final step to output the result of the crosstab query so that I can join it to two other tables and finally export it out to Excel. The users need the data in Excel in the format of the crosstab query. PartNumber, PubDateMonday, QtyReceived, Overdue, and the Date headers. The columns of the crosstab query vary each time the code is run. That's why I'm stuck as how to handle this problem. I can't "modify" the crosstab query because it is generated programmatically and the columns vary each time it is run. I don't see how I can include tblMatrix in in qryMatrixFinal when the field names vary each time that it is run.
Then you will have to open the crosstab as a recordset. Then:
Dim fld As DAO.Field
Dim rst As DAO.Recordset
For Each fld in rs.Fields
' Build list of field names.
Debug.print fld.Name
' Construct part of SQL for further processing.
strSQL = "," & strSQL & fld.Name
Next
Debug.Print strSQL
' Finish construction of full SQL ...
/gustav
Dim fld As DAO.Field
Dim rst As DAO.Recordset
For Each fld in rs.Fields
' Build list of field names.
Debug.print fld.Name
' Construct part of SQL for further processing.
strSQL = "," & strSQL & fld.Name
Next
Debug.Print strSQL
' Finish construction of full SQL ...
/gustav
I would agree with Gustav. I do this type of logic with my forms and reports that are based off of crosstab queries, although I would modify his SQL string slightly to:
strSQL = ", [" & strSQL & fld.Name & "]"
since it doesn't appear that you will actually have any control over what the column names will look like.
strSQL = ", [" & strSQL & fld.Name & "]"
since it doesn't appear that you will actually have any control over what the column names will look like.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for TechMommy's comment #a40324588
for the following reason:
Although Gustav and Dale offered good suggestions, it was ultimately less code and easier for me to simply add queries to the foundation query for the crosstab query as described above.
Accepted answer: 0 points for TechMommy's comment #a40324588
for the following reason:
Although Gustav and Dale offered good suggestions, it was ultimately less code and easier for me to simply add queries to the foundation query for the crosstab query as described above.
The first line in my response was "Querying a cross table query does not sound like a good idea".
Looks like you have done part of what I recommended., which was to query baseline data and don't query a crosstab query.
Looks like you have done part of what I recommended., which was to query baseline data and don't query a crosstab query.
Objection overrruled; the comment is an opinion, not a solution.
Netminder
Senior Admin
Netminder
Senior Admin
The question is why should you need to resort to such gymnastics. My gut feeling is that the best way to resolve this issue is to relook at you table design. People new to MS Access often try to use excel spreadsheet concept when designing their table and this only leads to a complete nightmare.
Send a screen shot of you table relationship and we can continue from there