Solved

How Can I Create a Query Based on an Access  Crosstab Query and Handle the Varying Column Headings in the Crosstab?

Posted on 2014-09-14
10
302 Views
Last Modified: 2014-09-16
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.
Query Based on Crosstab QueryIn 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.
0
Comment
Question by:TechMommy
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 40322622
Querying a cross table query does not sound like a good idea. The heading and number of column in the cross table are likely to change when the date in the tables use to create the crosstable changes.

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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40322733
In design view of the crosstab, go to Properties and specify under Shown Column Headers (option 3) the columns you wish to retrieve.

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40322922
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.
0
 
LVL 11

Author Comment

by:TechMommy
ID: 40324065
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40324080
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
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40324392
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.
0
 
LVL 11

Accepted Solution

by:
TechMommy earned 0 total points
ID: 40324588
Thanks everyone for your suggestions. I was ultimately able to solve the problem by linking the two queries that contained the QtyReceived and OverDue columns to the query that is used as the foundation for the crosstab query. In that way I was able to obtain the field values and have them properly appear in the crosstab query which I used to create the result. I then simply send the result of the crosstab query to Excel without needing the extra query.
0
 
LVL 11

Author Comment

by:TechMommy
ID: 40326333
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.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40326334
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.
0
 
LVL 5

Expert Comment

by:Netminder
ID: 40326599
Objection overrruled; the comment is an opinion, not a solution.

Netminder
Senior Admin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
subtract 1 in Access 2003 query 7 39
Sub Reports 8 23
Error: Operation must use an updateable query 2 11
MS Access Hiden Columns 19 14
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now