473,692 Members | 1,872 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.FirstOfPRI ORITZTN_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
qryAllProjectsE ventsPrioritztn Deployment3_Cro sstab xTab
WHERE
(qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
.FirstOfPRIORIT ZTN_STATUS_NAME = "Enterprise Approved")
Or
(qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
.FirstOfPRIORIT ZTN_STATUS_NAME = "BU/Specialty Group Approved")
Or
(qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
.FirstOfPRIORIT ZTN_STATUS_NAME = "Internal Only")
Or
(qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
.FirstOfPRIORIT ZTN_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 2536
sc************@ gmail.com wrote in
news:11******** *************@f 14g2000cwb.goog legroups.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.FirstOfPRI ORITZTN_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
qryAllProjectsE ventsPrioritztn Deployment3_Cro sstab xTab
WHERE
(qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
.FirstOfPRIORIT ZTN_STATUS_NAME = "Enterprise Approved")
Or
(qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
.FirstOfPRIORIT ZTN_STATUS_NAME = "BU/Specialty Group Approved")
Or
(qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
.FirstOfPRIORIT ZTN_STATUS_NAME = "Internal Only")
Or
(qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
.FirstOfPRIORIT ZTN_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).ControlSourc e = 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
23498
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 3 10:15 10:30 Biscuits 5 10:30 10:45 PopCorn 2
5
6171
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 (www.johnmacintyre.ca). The problem is that I am trying to pivot biological taxonomy information, and may end up with a table containing over 200 columns. This takes the dynamic string well over the 8000char limit for variables. >From my understanding, the...
2
3352
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 created a unique query for every "sector", saved them, and select the right one according to the user's choice. What i'd like to do is to create the crosstab query directly in VBA, change the WHERE part for the sector i want to show and output it...
1
5964
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 along the top row and the data items below it. This would give a better appreciation of changes over time. I apologise if I'm missing something obvious. Phil
8
6068
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 enteries) into a table storing DealerID and DealerEmail (where the DealerEmail is the Primary Key as it's unique). The thing to keep in mind is that Dealers can have multiple Emails. I have (simplified for examples sake) another table containing...
4
6474
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 flat file. Ideally I would like to be able to just select the Last 2 Columns of the Crosstab query as inputs to the Select query WITHOUT having to go in month after month and manually change it... I may be asking a bit much here but is there a...
3
3488
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 program a dynamic multi-page crosstab report?
8
5912
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.. So if the user selects 3 months in the criteria form, there will be 3 (month) column headings, if he selects 6 months, the will be 6 headings.
4
23065
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 corresponding to the Years...for example: Year, Loss 1, 568 1, 621
0
8603
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8960
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8800
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7627
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6459
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5818
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4323
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4557
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2972
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.