473,322 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Variable # of columns being output by a Crosstab query

....and the SELECT thats trying to pull from said Query doesn't like it
one bit! ;)

I'm working on this project (in Access 2002) and there is a report
who's RecordSource is the following SELECT;

SELECT
xTab.FirstOfPRIORITZTN_STATUS_NAME,
xTab.[Total Of CountofITEM_ID],
iif(IsNull( xTab.[FY06 Q1] ), 0, xTab.[FY06 Q1]) AS [FY06 Q1],
iif(IsNull( xTab.[FY06 Q2] ), 0, xTab.[FY06 Q1]) AS [FY06 Q2],
iif(IsNull( xTab.[FY06 Q3] ), 0, xTab.[FY06 Q1]) AS [FY06 Q3],
iif(IsNull( xTab.[FY06 Q4] ), 0, xTab.[FY06 Q1]) AS [FY06 Q4],
iif(IsNull( xTab.[FY07 Q1] ), 0, xTab.[FY06 Q1]) AS [FY07 Q1],
iif(IsNull( xTab.[FY07 Q2] ), 0, xTab.[FY06 Q1]) AS [FY07 Q2],
IsNull( xTab.[<>], 0 ) AS [<>]
FROM
qryAllProjectsEventsPrioritztnDeployment3_Crosstab xTab
WHERE
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "Enterprise Approved")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "BU/Specialty Group Approved")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "Internal Only")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME="Event Only");

Now, as soon as I try to run the report (Print Preview), I get the "The
Microsoft Jet database engine does not recognize 'xTab.[FY06 Q4]' as a
valid field name or expression."

I've seen a lot of talk on the 'Net about how others who have gotten
this error need to specify the data type(s) of their parameters. Well,
this query doesn't take parameters, it pulls its data from another
query.

After looking at the output of the crosstab query, I can see that
'xTab.[FY06 Q4]' is simply not being output by the query as there is no
data for that column (and the other 2 that follow).

My question is, is there any way to dynamically find out that those
columns are not being output, and deal with it properly so that I can
run this report and not have it crashing?

OR, how should I deal with this? I was thinking of trying to force the
missing columns in there somehow with 0's or something but I just can't
seem to wrap my head around this one.

Thanks for listening,
-S

Jan 21 '06 #1
2 2519
sc************@gmail.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.com:
OR, how should I deal with this? I was thinking of trying to
force the missing columns in there somehow with 0's or
something but I just can't seem to wrap my head around this
one.
from the Access help file on the Crosstab Query .ColumnHeadings
property
:
"If you include a column heading in the ColumnHeadings property
setting, the column is always displayed in query Datasheet view,
even if the column contains no data. This is useful for a report
based on a crosstab query, for example, when you always want to
display the same column headings in the report."

Thanks for listening,
-S


--
Bob Quintal

PA is y I've altered my email address.
Jan 21 '06 #2
Bri

sc************@gmail.com wrote:
...and the SELECT thats trying to pull from said Query doesn't like it
one bit! ;)

I'm working on this project (in Access 2002) and there is a report
who's RecordSource is the following SELECT;

SELECT
xTab.FirstOfPRIORITZTN_STATUS_NAME,
xTab.[Total Of CountofITEM_ID],
iif(IsNull( xTab.[FY06 Q1] ), 0, xTab.[FY06 Q1]) AS [FY06 Q1],
iif(IsNull( xTab.[FY06 Q2] ), 0, xTab.[FY06 Q1]) AS [FY06 Q2],
iif(IsNull( xTab.[FY06 Q3] ), 0, xTab.[FY06 Q1]) AS [FY06 Q3],
iif(IsNull( xTab.[FY06 Q4] ), 0, xTab.[FY06 Q1]) AS [FY06 Q4],
iif(IsNull( xTab.[FY07 Q1] ), 0, xTab.[FY06 Q1]) AS [FY07 Q1],
iif(IsNull( xTab.[FY07 Q2] ), 0, xTab.[FY06 Q1]) AS [FY07 Q2],
IsNull( xTab.[<>], 0 ) AS [<>]
FROM
qryAllProjectsEventsPrioritztnDeployment3_Crosstab xTab
WHERE
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "Enterprise Approved")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "BU/Specialty Group Approved")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "Internal Only")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME="Event Only");

Now, as soon as I try to run the report (Print Preview), I get the "The
Microsoft Jet database engine does not recognize 'xTab.[FY06 Q4]' as a
valid field name or expression."

I've seen a lot of talk on the 'Net about how others who have gotten
this error need to specify the data type(s) of their parameters. Well,
this query doesn't take parameters, it pulls its data from another
query.

After looking at the output of the crosstab query, I can see that
'xTab.[FY06 Q4]' is simply not being output by the query as there is no
data for that column (and the other 2 that follow).

My question is, is there any way to dynamically find out that those
columns are not being output, and deal with it properly so that I can
run this report and not have it crashing?

OR, how should I deal with this? I was thinking of trying to force the
missing columns in there somehow with 0's or something but I just can't
seem to wrap my head around this one.

Thanks for listening,
-S


Bob's answer is good if you know ahead of time what columns you are
going to want in the crosstab. However, it is more usual for the
crosstab to be dynamicly creating different columns over time. This
means that the report's query and the report itself need to be capable
of figuring this out and dealing with it.

I have done this before and it is a bit complicated. Here's an overview
of what I do:
- In the report design I create empty label controls (caption=blank) for
each potential column named in a consistant way (label01, label02, etc)
- In the detail section I create unbound textbox controls also named in
a consistant way (Data01, Data02, etc) and set visible=no.
- In the Open event of the report I open a recordset based on a query of
the same base as the crosstab to get a record per crosstab column
- I then do a test to make sure there isn't more records than the no of
columns I presetup in the report design
- Then loop through the recordset and change the Caption of the Labels
and the controlsourse of the Textboxes and make them visible

air code:
i=1
Do Until rs.EOF
Me("Data0" & i).Visible = True
Me("Data0" & i).ControlSource = rs!AATCAT
Me("Label0" & i).Caption = rs!AATCAT
i = i + 1
Loop

Like I said, its a bit complicated and there may be a better way to do
it, but this has worked very well for me so far.

--
Bri


Jan 25 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Pooj | last post by:
have a urgent requirement. Please somebody help me. I have a table departinfo with following records begin_time end_time Name Pieces 10:00 10:15 PopCorn ...
5
by: tim.pascoe | last post by:
I'm attempting to modify some Crosstab generating code, and I need some advice/examples. Currently, the code uses a single string variable to store the dynamically generated query...
2
by: Mike | last post by:
Hi everyone, I found the Access reports too limited to do what i wanted so i created a module to export a crosstab query to an excel file, and then i modify it as i want. My problem is that i...
1
by: Phil | last post by:
Is it possible to swap rows and columns in select query output so that each record's data is displayed in a column? I want to collect data each day and display it in a query with each day's date...
8
by: m.wanstall | last post by:
Hi All, This is similar to a question I asked earlier however this is following a more "correct" way of doing things. I have normalised and summarised an Exchange addressbook (a few thousand...
4
by: m.wanstall | last post by:
I have a crosstab query that compiles data for Months of the year. I have a stacked select query on top of that crosstab query that uses the latest 2 months data and exports it to a fixed length...
3
by: Niranjan | last post by:
I want create a report based on the crosstab query which normally returns about 50 - 60 columns. The columns have names of the counties and they keep changing for every session. Is there a way to...
8
by: Paul H | last post by:
I want to base a form on a crosstab query. The query shows statistics for a user defined period. The column headings will look something like this: ClientID Month01 Month02 Month03 etc.. ...
4
by: Haas C | last post by:
Hi all, I have a table with two columns, labeled Year and Loss. In the Year field, I have the numbers 1 to 10,000, each which can or cannot repeat. In the Loss column, i have numbers...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.