Access report header and sorting

[lonely]

Hi there,

I am working on a access report for financial analysis, I am wondering if anyone would help me with following questions.

I created a form with a drop down list to select cost center from tables, so the report will only show the data from the selected cost center. My questions are:

1. I want the chosen cost center automatically shown at the Header of the report, as selected from the form;

2. The data was grouped in a particular sequence, such as: Revenue, Compensation, MM&S and Other Expense, not simply ascending or descending

3. There are some category data I do not want to show, for example, other expense, how can I hide it?



Would anyone please share your thought? I would be greatly appreciated!

Thanks,

Luna

[rpeare]

1. several ways you can get it there.
a. include the cost center description in the query driving your report and put that field in your report header
b. put a text box on your report and put the formula =[forms]![formname]![comboboxname].column(x)

where formname is the name of your form
comboboxname is the name of your combo box
and x is the column (remember combo boxes are 0 indexed so if the cost center is the 2nd physical column it's index is 1) where the cost center name appears


2. What is the question here, whether you can re-order or change grouping schemes?

3. If you are talking about the combo box on your form, in other words you want to deny your users the ability to look at certain cost centers you can do this a couple of ways

a. Simply put a critera on your cost center PK of <> CC_ID where CC_ID is the PK of your cost center
b. Modify the table that has your cost centers and add a field "DoNotShow" or something like that as a yes/no field and put a check in the cost centers you don't want to show, then in the query driving your combo box put a criteria in the "DoNotShow" field of <> -1

[lonely]

Thanks, rpeare,

I fixed the first question by simply add a text box in the header.

The 2nd question is I want to re-order the rows in the reports, but the simple ascending and descending is not satisfying. Do you know how to sort the data according to a specific order?

Thanks,

Luna

[June7]

If Revenue, Compensation, MM&S, Other Expense are values in a single field and you want to sort in that order, options:

1. have table of these values with a field for a sort value (1, 2, 3, etc) and include this table in the report RecordSource

2. create another field in query or report Sorting & Grouping design that calculates a value to sort by:

Switch([fieldname]="Revenue", 1, [fieldname]="Compensation", 2, [fieldname]="MM&S", 3)

If you don't want to include Other Expense records then apply filter criteria: <> "Other Expense"

[lonely]

Thanks June7.

Your explain is very clear. So the Access can only recognize Ascending or Descending, we have to create another line for the sorting.

[June7]

Basically, yes. What other type of sorting could you envision?

[lonely]

I was expecting something like we could do with Excel, customize the sorting.

I have another question.

Is there a way you would let the user chose which field to show?

I have a table with data for different cost centers, we have data for 12 months, both Actual cost and budgeted cost. What I would like to have is,

If the user chose May, I would have a report to show May Actual Data, May Budget Data, Variance, May Year to Data Data, and maybe May last year's data from a different table.



Thank you for your thought!

Luna

[June7]

Excel is still sorting ascending or descending - whether alphabetic or numeric, can be only ascending or descending.

If you want to show only May records then apply filter criteria.

[lonely]

Hi June7,

We can customize sorting on Excel, please see attached pic to sorting data in accordance with weekdays, Months or anything you want.



Maybe I do not make myself clear. May data is a column, every month's data is a field. To my understanding, filter criteria can only apply on rows.

Correct me if I was wrong.

[June7]

Okay, I've never played with Excel OrderList. Interesting. I suspect what you don't see Excel doing is assigning a numeric value to each item in the list and using that 'behind the scenes' to sort by or some other complex algorithm I can't begin to envision.

No, Access does not make so simple. What is not visible to you in Excel you must consciously construct in Access. Define a field for sort order and apply ascending or descending parameter.

A field for each month means your data structure is not normalized - you are 'committing spreadsheet'. This will likely give you lots of frustration in Access.

A query cannot be dynamic for selection of fields, only records.

[lonely]

I am new to Access, that's why I post the questions, am I.

So when you say normalize, do you mean to structure data like:

[June7]

Yes. That allows filtering records on month values. Then if you want to view output of months horizontally, that would be a CROSSTAB query.

[lonely]

That really helps June7!

Many thanks!