[adamng7]

Hi,

I am creating a database where I monitor my daily cost. In my table for planned costs, I have the following fields:

Planned Cost
- Day i.e. day 1, day 2, day 3 (I have a total of 132 days)
- Expense_Description
- Amount

Basically, I am trying to create a report that shows

Day Day Day Day Day...

Expense Description xx xx xx xx xx xx xx...
Expense Description xx xx xx xx xx xx xx...
.
.
.

I tried creating a crosstab query, which turned out fine. However, when I try to create a report based on the query, I get an error message "The number of fields from your currently selected table or query exceeds the macimum width in forms and reports".

Am I doing something wrong?

Thanks in advance for the help.

Regards,
Adam

[OBP]

Adam, it would appear that you are trying to display 132 fields across the Report, which makes the Report too large to fit on a Page, can you you reduce it to 31 days (i.e. a month) and see if you can get that to fit?
Unlike Excel you can't make an Access Report "Fit to page".
Can you reverse the Crosstab and have the days going down the page with the other data going across the page?

[adamng7]

Hi OBP,
Unfortunately, I cannot reduce the days as this represents the length of a certain project.

[adamng7]

Hi OBP,
I am thinking if I can't do this, can I create a selection criteria, say select a particular day and generate the report. How do I actually do this?

THanks.

[OBP]

You can report on a day, a week or Month or between 2 dates.
If you want to select the dates on a form you can do that as well.
To select between 2 dates using the simplest method add this to the date field's Criteria Row
between [Start date] and [End date]

[adamng7]

OBP,
Thanks, but how do I exactly do that?

[OBP]

Which part?

[adamng7]

Hi OBP,
I did make some progress. Just to be clearer, let me first define what is in my db. Kindly bear with me as I am a real novice in Access.

First I have the following table with the following fields

Planned Cost: Table
- Transaction_ID (primary key)
- Progress_Day
- Progress_Date
- Expense Description
- Unit_of_measure
- Unit_required
- Price_per_unit
- Total_amount

Progress: Table
- Progress_Day (primary key)
- Progress_Date

My objective is to create several reports. First a daily cost report, and second a range of cost report, i.e. from a certain date to a certain date (on both a single display, as well as a multi-column display)

To achieve the daily cost report, I created a crosstab, with the Expense Description as the row heading, and Day as the column heading. Value is the sum of Total Amount.

To link it to a selection criteria, I created a blank form and named it "Print Report", with a combo box to select Day as the criteria.

Then what I did was to go to the design of the crosstab query and input "[Forms]![Print Report]![Combo0]". However, I got an error saying that "Microsoft Office Access database engine does not recognize [Forms]![Print Report]![Combo0] as a valid field name or expression. After some research, I managed to solve this by inputing the same set of criteria in the parameters of the query. I have no idea why this had solved my problem, but it did anyway. Basically, I could now generate a query based on the selection criteria of the "Print Report" form.

Next, I managed to create a report based on the crosstab query and the report turned out fine. However, my stumbling block come as I have no idea (as in even how to start) how to go about making a report that is based on a range of date where the planned cost is displayed as a total as well as a day-by-day display.

Appreciate your help.

Thanks.

[adamng7]

Hi again,
I managed to create a range. Basically I used the expression >([Forms]![Print Report]![Combo9]-1) And <([Forms]![Print Report]![Combo11]+1), where of course combo9 and combo11 are my start and end range respectively.

However, a report based on this crosstab query only generates the total, instead of the individual days. How do I go about displaying the days?

Thanks again in advance.

[OBP]

Well done on the Crosstab, especially the Parameter part, it is a quirk of Crosstabs that any external criteria have to be declared as a parameter.
When you run the query do you only get the Total?
If you get the days in the query, redo the report and ensure that you don't ask for a Summary only, it should end up looking just like the query, but formattable.

[adamng7]

How do I make the report the way that it is formattable? I tried using the wizard, but there is no field for me to select in the crosstab query.

[OBP]

Can you show a screenshot of the Crosstab Query?

[adamng7]

Hi OBP,
Thanks for the reply. I have attached a screenshot of my query and the report for your reference.

Thanks.

[OBP]

adam, you should be able to add the other Query Fields by using the Add Existing Fields Icon in the right hand side "Tools" tab on the main menu.