Report Challenging

[sjpistolesi]

ACCESS 2000:

I'm moving a DOS programmed [old Fox-Pro and previously CLIPPER] legacy system to ACCESS 2000.

I'm trying to group by a specific of ACCTID, but not in ascending order, which appears to be the only selectable criteria.

[1] Must first group by ACCTID and ACCTCODE(ascending the right 3 characters) using a query. The query I've written assembles the records, grouped correctly (but the groups are in ascending order).

[2] But when actually printing, I must output the ACCTID groups in a distinct [user required] order that is neither ASC or DESC ....

(In the original programming, this is done by sequential "do while's" for each ACCTID group, in the desired, specific order, as the records are sent to the printer.)

Any suggestions on how I can control the group order as an ACCESS report?

Sandy

 

[LarryDeLaruelle]

Sandy:

I would suggest creating a sort field in the underlying query to hold the data necessary to get the report into the 'user required' order.

Simply group on that field but do not display it. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[sjpistolesi]

Larry De Laruelle,

I've been fiddling with that (and using non-displayed) sorts for several days, but sorting is just not the way to "get there."

I have a "report" table with everything (the ACCTIDs) in the correct order; remember that sorting is not an option --- sorting (and sub-sets of sorting) skews the order.

The first 5 records have blank ACCTCODEs. I don't want those first 5 records to print, but then want to print the remaining records sequentially without rearranging them.

Now I cannot figure out how to deliberately print sequentially. Everything seems to be defaulting to a sort tendency and I've not turned that on anywhere I can find and/or deliberately set as "false" when opening the report.

Grrrr, Sandy

 

[LarryDeLaruelle]

Sandy:

When you say 'sequentially', what does that mean?

To the best of my knowledge (limited at best), if you do not set a sort order, the records will be arranged in either primary key order (I think that is considered natural order), if one exists, or in entry order if no primary key has been set. I don't know how indexes on non-keyed tables would affect that.

However, the kicker is that you cannot use grouping without using sorting. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[WildHare]

Add a new "group" field to your record to mimic the DO..While Looping that took place. I don't know what your values would be, but it sounds like simply an arbitrary value that is not numeric or alphabetic, just something assigned arbitrarily - like account groupings. This should then allow you to "group" via this field and get your report to print acceptably.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[sjpistolesi]

Jim ... Yours was closer to the method I ended up using.

I'm not well enough versed in groups as your described. But, I found a way to make it happen.

[1] I created a table with the correct order of the ACCTIDs I needed to assemble in a specific [user requested] manner.
[2] I inserted an integer field which numbered them in the correct order [1, 2, 3, ...]. I then "sorted" on that integer in the report.
[3] I was then able to perform the subreport query (with an "artificial" group) together with each sequential [err ... ACCTID] record, drawing in the records and sorting them according to the right-most 3 characters of the ACCTCODE. (See KSB Article Q208541 and also Rich Gorvin's "Sophisticated Reports: Multiple Columns and Subreports," in the MSDN Library.)

Thanks for brainstorming, Sandy