Making groups within a query

[louielol]

We have a database we've been using for over 10 years, and up until this year the data was manually entered. We are now importing the data with an .xml file.

During manual entry, the user would assign the record to a group by selecting from a pull down list on the entry form, so that we could group the data properly on the reports. Now that the data is imported into the database, is there a way I can set up the query to assign each record to a group automatically based on the data in the record? We would need to use data from two fields to assign a record to the proper group.

These are cattle production records, and we need to sort by sex and weight to get 5 groups; steers <700, steers 700-800, steers >800, heifers <650, heifers >650. Each record has the sex and the weight data.



The reports we need to print are sorted by the service rep and then on the farm. For each farm we need to summarize the production data for each of the 5 sex/weight groups.

I've already figured out how to group using 5 different queries, but now that I am working on the report I'm thinking it would be better to get the data from one query - hence the need to automatically group them in the single query.

Thanks!

[June7]

Use report Grouping & Sorting with aggregate calcs. Grouping doesn't take place in the query. The report setup handles that. This allow display of record details as well as summary calculations.

[louielol]

Thank you! I'm half way there; I found the grouping in the reports, and I can group by sex because that is a fixed input (steers, heifers, mixed). However, how do I group on the weight ranges that I want? The weights are entered as actual and each record has it's own unique weight. I want to have 3 categories or groups of weights; <700, 700-800, and >800. I know how to do that in a query, but I can't find an expression builder in the group function on the report design view.

[June7]

You can either:

1. create a field in query with expression that calculates a value that can be used for grouping

2. create a group section in report with expression - in the Grouping & Sorting setup, select Group On expression from droplist

[louielol]

Got it!
I did an update query for each criteria to add an identifier in a "group" field in the table. Now I can use that to make and sort by group in the the reports.
Thanks!

[June7]

A new field and an update query should not be necessary. This 'group' value can be calculated from the weight data. What if there was an error in the weight entry and you correct it after the update, the data will be out of sync. This is the main reason for not saving calculated data (data dependent on other data).