Sort report same as underlying table/query 1

[Dawnit]

Hello! I have an Access 2000 table or query that is NOT sorted ascending or descending, which is what I want. The table or query is the data source for a report that must reflect the record order as defined in the table or query. My report settings are as follows:

sorting and grouping: field 1, ascending
Order by on: yes
Order by:

.field 2

Ultimately, I want the report to list records as they are listed in the underlying table or query.

Thanks very much!

 

[Philly44]

I can't tell what your asking

 

[Dawnit]

My table records in field 2 are in a specific order that is NOT ascending or descending, alphabetically speaking, which is what I want. And, the table is the data source for a report that must show the records in the same order as they are listed in the table. My report properties are as follows:
Order by on: yes
Order by:

.field 2

Sorting and grouping is set for field 1 of the underlying table, ascending.

Ultimately, I want the report to list records as they are listed in the underlying table or query.

Is this more clear?

 

[JimHorton]

Dawnit,
If you know the sort of the underlying, just reproduce it in the report. If it's a table, then it will sort by primary key, so just list that/those columns in the sorting box in the report.

If the underlying is a query, it doesn't make sense to sort the query, since the report does it's own sorting anyway--you are better off doing any sorts in the report if any sorts are to be done. But either way, access doesn't sort randomly (unless you have no primary key), so there is always a distinct sort in the table that can be reproduced--and as I said if you're looking at a query you'll still have some primary key'd table in the query which will determine that sort when there's no explicit sort set (which there shouldn't be).

So, bottom line is just reproduce the sort of the underlying in the sort/group box. Now, what may be confusing, is that if you just open a table in datasheet mode, maybe you or someone had right-clicked some random column and sorted-acending or decending, then clicked 'Yes' when asked to save the layout. Now who knows what the sorting is--it's anyone's guess unless you remember which columns you clicked on to sort. Let me know if this helps,
--Jim

 

[Dawnit]

Not helpful Jim. The info in field 2 of underlying is NOT SORTED and that is how I want the report results to stay for the info in field 2.

However, I do want the report to "group" all of the underlying field 1 data by field 2. Here is an example:

Underlying table:
Field 1 Field 2
------- -------
Adm. City Manager
Adm. Asst. City Manager
Adm. Office Asst.
Maint. Sanitaiton Supervisor
Maint. Field Worker

My report is doing this:
Adm.
Asst. City Manager
City Manager
Ofice Asst.
Maint.
Sanitaiton Supervisor
Field Worker

I also must be able to set up a subtotal field on the report for each group (underlying has number fields too, related to field 2).

Without doing a sort/group, I can resolve the first field 1 and 2 problem, but I can't do group subtotals.

With the sort/group box, I cannot figure out my field 2 and 2 problem, but I can do report group subtotals.

I am seriously in need of a solution!

Thanks for your help thus far, I look forward to more.

 

[JimHorton]

Here's what I believe is happening. First, I know that when Access does a report with any grouping, it builds an aggregate (Group By) query behind the scenes. When an aggregate query runs, the query itself sorts in the order of the groupby clause. So if you did (and I did this to verify) a simple query on your table, clicked the group by and the sql was:
SELECT Field1, Field2
FROM Table1
GROUP BY Field1, Field2

You get a default sort of field1 (asc), field2 (asc), but reverse the field1 and 2 (in the group by, leave the select order alone) and the sort is Field2(asc) field1(asc)

So the good news, now you know why. The bad news: I don't know if there's a way to specify 'not sorted' in a groupby or report--as you've seen, just leaving the field2 out of the group by doesn't help, only leaving *both* out does the trick, but that of course won't help you.

There are of course workarounds, such as hidden sort fields, but this gets messy, and I wouldn't recommend that unless this sort thing is critical.
--Jim

 

[MichaelRed]

Try setting the order by OFF.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JimHorton]

Michael,
I did the orderby = no and it did nothing...I just made a table with the identical data in dawnit's example and my report (with grouping on field1) orders field 2 O,A,C not A,C,O as the example does---regardless of orderby, etc.

The the 'raw' table in my test has field2 ordered C,A,O (for Adm.), same as Dawnit's. At this point I'm just curious.
--Jim

 

[Dawnit]

Thank you Jim! It is a great relief to know the "why." This is a critical problem since this is for the City of Claremont, CA budget book that I rather not have to retype when I publish it to Word. I will try a hidden sort field with my limited knoweldge, but if you can figure any way to resolve this for me, it would be greatly appreciated.

You're caliber of assistance is why I keep coming back to Tek-tips!