MS Access Problem

[MonkeyBalls]

Hi
I am currently using Microsoft Access to use a read only sql database, which is constantly updated via another system.
I am creating a number of queries and reports in order to provide information to users.
However...

I have created a Query from tables in Access,with the fields:



Name, Surname, Group, Date, Event



I have created a crosstab query from this based on Event. This has created the following columns in the CrossTab query:



Name, Surname, Group, Date, GNC, GNX,GNH,GNO,MER



As you can see the Event field has been converted into the five fields, GNC, GNX, GNH, MER.

The problem I face is when i create a report on this data. I have created the report and was printing it on a year to date perspective, which worked fine. However when I attempt to print the report on a week to date, there are no field records for GNC, so the report gives an error saying that the Jet engine cannot create the report because it can't find any records for "GNC".

Is there any way around this, as the same problem will probably occur in the future, and I need the database to print the reports automatically upon the running of the program. It is to be used by people with little or no experience with Access, and has to be as easy as posisble, i can't have them going into report design view and deleting the GNC field so that it does not request it, as they do not have the knowledege to do re-establish the feild into the report.



Please Help! Its Kinda Urgent!

[flipper321]

In the crosstab query right click at the top of the "column heading" column. Where you have column headings enter all possible column headings e.g. GNC GNX GNH etc etc.

This should produce a column for GNC even when data doesn't occur.

[MonkeyBalls]

cheers for that but i can't find how to do it!?
I'm using Access 2000...is what you said to do in the design or print preview stage of it!?

[flipper321]

Let me check - that guide was Access 97....

OK it's the same in 2000 as 97 -

in query design mode your field that contains GNC etc etc will have "Column Heading" in the Crosstab row in the bottom section. Click into the column area and right click - select properties.

In the box that comes up there's a row called Column Headings - in the box here enter all the possible results for the field, so from your example above..."GNC", "GNX", "GNH", "GNO", "MER"

Type it in as I have there.. in fact copy and paste as I've done it for you!!

That should sort it.