Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-26-2003, 07:21 AM   #1
AshikHusein
Newly Registered User
 
Join Date: Feb 2003
Posts: 147
Thanks: 0
Thanked 0 Times in 0 Posts
AshikHusein
Problem with a report based on a crosstab query

I have created a reprt based on a crosstab query where the query totals the values within a certain field. So effectively that field becomes the column heading. The problem is that if there is one field missing ina table that I am analyzing, the report gives me an error saying that the field is not found. I can try to explain below:

Say that you have a field called "Callergroup" as the row heading and "Calltype" as the column heading.(it sums over a field called "TOTAL") Let us say assume that "Calltype" has three types of entries X,Y and Z.

Now when create the report from the crosstab query, X,Y, and Z will become the column headings. So assume that these will be the column headings for which I have designed and formatted the report for to be viewed by the management.

Now there are times that there may not be an entry, say Z, in the field "Calltype". Under these circumstances when I try to get the report it gives me an error saying that the field "Z" does not exist (since Z is one of the fields in the report previously created).
Is there a way to get around this so that even though the field does not exist the report assume that all TOTALS for that field will be zero but it does open the report without giving me an error. Thanks.

AshikHusein is offline   Reply With Quote
Old 06-27-2003, 07:39 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,334
Thanks: 13
Thanked 1,403 Times in 1,337 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Change the column headings property to include a list of the valid values. This list will control what data is selected (so if your list of values changes, you will need to change this property) and the order in which the columns appear.
Pat Hartman is offline   Reply With Quote
Old 06-30-2003, 11:29 AM   #3
AshikHusein
Newly Registered User
 
Join Date: Feb 2003
Posts: 147
Thanks: 0
Thanked 0 Times in 0 Posts
AshikHusein
Unsure about the properties aspect of the reply

I am not certain that I understand your reply so I request a clarification. I am unsure as to what property you are referring to.

A report when made on a cross tab query has columns which are created based on certain categories within that field. In my case the field is "Calltype" and the categories within it are "X", "Y: and "Z". Now a report is based on these three categories within the field and for any week there may or may not be one or more of these categories on which to crosstab query. The report is based on all these categories. Now during a certain week if there is no data to be toatlled within a category (say "Z") for example, the field list for that saved report format will not contain "Z" and so the when you try to print the report it gives an error saying that "Z" is not found. So again the question is that is there anyway that you can not have this error and make the report assume that all values of for "Z" are zero.

Would appreciate a response again. Thanks.

AshikHusein is offline   Reply With Quote
Old 06-30-2003, 12:48 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,334
Thanks: 13
Thanked 1,403 Times in 1,337 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
1. Open the query in design view.
2. RightClick on the grey background in the top part of the form.
3. Choose Properties
4. Put your code values in the ColumnHeadings property -

X", "Y, "Z"
Pat Hartman is offline   Reply With Quote
Old 07-02-2003, 04:55 AM   #5
AshikHusein
Newly Registered User
 
Join Date: Feb 2003
Posts: 147
Thanks: 0
Thanked 0 Times in 0 Posts
AshikHusein
That works! Tahnks!!

That worked like a charm!!. Thanks!!!

AshikHusein is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 05:15 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World