How do I sort fields by the value in a report

[mikewilday]

I have a table of fields with set values. I want to create a report that
would take those values and sort them (per individual - not across all
records) and create a report for each record sorting the fields based on the
highest and lowest value.

So in my record:
Name: John Aker
Field Name: Dogs - Value: 5
Field Name: Cats - Value: 8
Field Name: Horses - Value 12

I want to create a report for Each name - with the fields sorted... ie.

John Aker
Field Name: Horses - Value 12
Field Name: Cats - Value 8
Field Name: Dogs - Value 5

See the field names are sorted based on the value of the field and presented
accordingly in a report

 

[Allen Browne]

John if you have fields named Dogs, Cats, Horses, etc, you will need to
programmatically rearrange the fields on the section of your report, in the
Format event of the (detail?) section. You could do that by writing VBA code
to load the fields into an array, then sort the array, then change the Top
property of the text boxels and labels based on their order in the array.
The Top property is measured in twips, where 1440 twips = 1 inch.

A better solution would be to use a related table to hold the various types
of animals and their count. This table would have fields:
PersonID relates to the primary key of your Person table.
AnimalID which animal the person has.
AnimalCount Number (how many of this animal the person has)

You can then create a report with a Group Header for the person, and sort
the records based on the AnimalCount field. No code is needed.

 

[mikewilday]

Can this be done in Access 2002?

John if you have fields named Dogs, Cats, Horses, etc, you will need to
programmatically rearrange the fields on the section of your report, in the
Format event of the (detail?) section. You could do that by writing VBA code
to load the fields into an array, then sort the array, then change the Top
property of the text boxels and labels based on their order in the array.
The Top property is measured in twips, where 1440 twips = 1 inch.

A better solution would be to use a related table to hold the various types
of animals and their count. This table would have fields:
PersonID relates to the primary key of your Person table.
AnimalID which animal the person has.
AnimalCount Number (how many of this animal the person has)

You can then create a report with a Group Header for the person, and sort
the records based on the AnimalCount field. No code is needed.

 

[Allen Browne]

Yes: both solutions will work in that version of Access.

 

[mikewilday]

Ok. So I made the extra table, with a field marked animals, and a field
marked value and linked the person data from the previous table. It gave me a
plus mark in the new table and contains the ID from the people's table and
all the results...

Now how do I make a group header, and cause the report to show me the
results for each individual person... better yet, how do add this other table
to the form to increasingly add the results for each person???

Mike

 

[Allen Browne]

To make the report:
===============
1. Create a query that combines all 3 tables: Person, Animal, and the
PersonAnimal table that has the PersonID and AnimalID fields.

2. Create a report based on the query. You can use the wizard, or follow the
remaining steps to do it yourself.

3. In report design view, open the Sorting And Grouping dialog (View menu.)

4. On the first row of the dialog, choose the PersonID field.
In the lower pane, set Group Header to Yes.
Access adds a grey bar for the group header.
In this group header section, put the text boxes to show the person's name.

5. Drag the animal name and value (count) into the detail section.

When you test the report, it shows the person's name as the header, with
each animal and the count on lines under the name.

To make a form:
============
1. Make a main form bound to the Person table.

2. Add a subform bound to the PersonAnimal form.

3. In the subform, place a combo box for selecting the animal. The RowSource
for this combo will be the Animal table.

In the subform, you can now select any animal in the combo, and specify the
count. Then add the next animal on the next row of the subform, and so on.