By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,365 Members | 3,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,365 IT Pros & Developers. It's quick & easy.

Problem with Crosstab Query in subform

P: 2
Hi, I'm fairly new to access and have a little trouble with a crosstab query I've setup.

I have a main form where the user selects a project name and below in a subform, a crosstab query is displayed in a datasheet view. The info is dependant on the project picked.

What I tried to do is set the criteria in the the query to only show results that match the project name but I am having no success. If I type a name into the critera then it works but if I enter a field name from the form then I run into trouble.

This is the initial SQL that works:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM First(tblWelderQualification.CertificateNo) AS FirstOfCertificateNo
  2. SELECT [First Name] & " " & [surname] AS Name
  3. FROM tblWelders INNER JOIN tblWelderQualification ON tblWelders.[Welder ID] = tblWelderQualification.[Welder ID]
  4. WHERE (((tblWelderQualification.Project)="filterCriteria"))
  5. GROUP BY [First Name] & " " & [surname], tblWelderQualification.Project
  6. PIVOT tblWelderQualification.[WPS No];
When I add a parameter and change the critera as below it fails.

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![frmQualReg]![Text5] Text ( 255 );
  2. TRANSFORM First(tblWelderQualification.CertificateNo) AS FirstOfCertificateNo
  3. SELECT [First Name] & " " & [surname] AS Name
  4. FROM tblWelders INNER JOIN tblWelderQualification ON tblWelders.[Welder ID] = tblWelderQualification.[Welder ID]
  5. WHERE (((tblWelderQualification.Project)=[Forms]![frmQualReg]![Text5]))
  6. GROUP BY [First Name] & " " & [surname], tblWelderQualification.Project
  7. PIVOT tblWelderQualification.[WPS No];
This fails also, I've only added a parameter here.

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![frmQualReg]![Text5] Text ( 255 );
  2. TRANSFORM First(tblWelderQualification.CertificateNo) AS FirstOfCertificateNo
  3. SELECT [First Name] & " " & [surname] AS Name
  4. FROM tblWelders INNER JOIN tblWelderQualification ON tblWelders.[Welder ID]=tblWelderQualification.[Welder ID]
  5. WHERE (((tblWelderQualification.Project)="filterCriteria"))
  6. GROUP BY [First Name] & " " & [surname], tblWelderQualification.Project
  7. PIVOT tblWelderQualification.[WPS No];
When it fails I get the following error from the form:
"You can't use a pass-through query or a non-fixed crosstab query as a record source for a subform or subreport.
Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property."

The reason for using the crosstab query is the number of colums is dynamic and I didn't want to use the pivotchart as my users are more used to the datasheet view.

Any help would be greatly appreciated. Thanks.
Jan 31 '07 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
Crosstable queries are nasty to use for forms and reports.
The basic problem is the fact that the content of a column will be transformed into fieldnames.
Thus when your selection fails to deliver a value or has a new value, then a field will be missing or a new field will be missing.
Best to make sure that all different values are present by using an outerjoin query with a table holding all values.

Personally I often create first a temp table with the selected data and use that for the crosstable query to have no trouble with the selection in the crosstable query.

When a report will work for you, then I can supply you with VBA code to fill a report depending on the result, but the max number of rows will need to be known.

Nic;o)
Feb 1 '07 #2

P: 2
outerjoin query? I'm not sure how this would be done but i think i undertand your concept
Feb 1 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Make a table with all values for the crosstable query.
JOIN that table with the original table and make the "value table" leading (click the JOIN line and chose option 2 or 3)
Now there will always be a value, even a Null will show.
I use the trick e.g. for having all 12 months and by joining to a field with the month all months will show.

Nic;o)
Feb 2 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.