grouping

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Are you able to group records based on a field without
sorting them as well?

I want to group a set of records by a certain field but
sort by a different field.
 
Could you provide an example. A question like this has been asked before and
I have never seen an example of how or why it should be done.
 
I want to group on an Item field but sort on an object
code field.

For example, my item field contains salaries, fringes,
travel, communications, etc.

The object code field contains numbers such as 1000,
1012, 1100, 1212, 3400, 3500, 6000, 6200, etc.

So, I want to group all the salaries (item field)
together but I don't want to sort on the item field. The
salaries codes have a range from 1012 to 1212 for
instance, while the communications codes have a range of
3400 to 3600.

If it is grouped on Items, it seems to automatically sort
the report by items. Thus, communications will be the
first records in the report instead of salaries if it was
sorted on object code.

If I try to sort on object code, it seems to group the
like object codes together (all of the 1012's) instead of
grouping the items together (all of the salaries) like I
want.

Any suggestions on how to group by Item but sort by code?
 
I still haven't seen sample data typed or pasted into an email that displays
what you want. If you are grouping all the Item fields, then they are all
grouped/sorted by similar Item values. If the Item value isn't how you want
the report sorted then you will have to add a field some place that is
linked to the Item field that contains your sort order.
 
Sounds as if perhaps you want the items grouped by Item, but the details
within an Item sorted on object code. That's just two consecutive entries in
the Sorting and Grouping properties.

If that's not what you want, I am puzzled and bewildered as to what you do
want, just like Duane is. Please clarify here in the newsgroup, not by
e-mail. Thanks.

Larry Linson
Microsoft Access MVP
 
Let's see if this is a better example of my data:

Object: MajorItem: SubItem: T: Amount:
3200 Communication Budget B 9,000
3211 Communication Postage E 1,835
3222 Communication Phone/Fax E 147

1920 Consult Budget B 150,000
1921 Consult Fees E 65,000
1922 Consult Expenses N 1,000

1001 Salaries Budget B 145,000
1112 Salaries EPASals E 34,000
1271 Salaries Longevity E 300
1411 Salaries Temp E 20,000

3100 Travel Budget B 6,000
3111 Travel IAirfare E 3,000
3119 Travel IMeals E 250


Ok, the above data is grouped and sorted by the MajorItem
field. What I would like is for the data to remained
group by the MajorItem field by sorted by the Object Code
field. It would reorder the above data so the Salaries
group under MajorItem is first (because the object code
for that group is the lowest number).

Currently, under "sorting and grouping" I have MajorItem
first and Object second. I have the group footer for
MajorItem set to Yes. In the footer, I have a
calculation set up to give me a total for each MajorItem
group.

How would I change my setup inorder to get the appearance
of my report sorted on Object by remained grouped by
MajorItem?

Thanks for your time.
 
It looks like none of your Object Code ranges overlap. I would create a
query that Groups by Major Item and contains the MIN of Object Code. Then
add this query to the record source of your report and join the Major Item
fields. Add the MINofObjectCode to the query grid so that your report can
sort by it.
 
I expected the totals query to have two columns. One for the Min of Object
and the other Groups By MajorItem. No more columns then this. Save this
query and then open the recordsource of your report. Add the totals query to
the record source and drag the MajorItem field from one onto the other to
create a JOIN. You can then add the MinOfObject field to the grid and use it
in your report sorting.
 
Back
Top