[AccessD] Report Data Order Differs From Form or Query DataOrder

Max Wanadoo max.wanadoo at gmail.com
Tue Mar 18 05:56:42 CDT 2008


Yes, spot on.
This is the difference I was trying to explain.  Passing a query to a report
requires you to change the Group/Sorting within the report and not rely on
the sortation within the query (which, as you and Gustav said, is ignored).
I have sorted the problem but I was having a moan and also pointing out to
others that this problem exists.
Having said all that, it is the first time in all my years programming (many
with Access) that this problem has surfaced because mainly indexes sort in
an order and actually records within the sort order are just not important
or are controlled correctly.  Here they are a problem because they govern
the bag breaks.

Ta
Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, March 18, 2008 10:48 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Report Data Order Differs From Form or Query
DataOrder

Max,

I would say it is not so much that it uses a different sort engine as that
it simply ignores the query behind the report ENTIRELY.  It pulls the
records that the query says to pull, but it does NOT sort them per the
query.  It sorts them per the report itself.  Basically your task is to
discover what the report does and do that in your query for the form and
other processes.  Or make the report do whatever the query does, but simply
know that whatever you do in the query (in terms of ordering the records)
will be thrown away by the report. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Tuesday, March 18, 2008 5:50 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Report Data Order Differs From Form or Query
DataOrder

Hi Gustav,
Hmmm.  I know you are trying to help and I do really appreciate that.
I think however you are still missing the point of the problem I was facing
with the REPORT Engine displaying records in a different sequence to that
displayed in a FORM or directly from a QUERY where the SAME query is used as
the RecordSource.

I have multiple forms and multiple records which are identical apart from
the source table which changes depending on which spreadsheet the user opted
to import and process.
Instead of programmatically changing the RECORDSOURCE for each FORM/REPORT I
have set all their RECORDSOURCES to a QUERY (qryData) The query is recreated
during the processing.

Image x records which need to have a mailsort code entered into a field.  
These records are processed and a new mailsort code derived and the record
updated.  This process is quite complicated but at the end of it we end up
with all records having a mailsort code.  There are many records with
multiple matching codes.

I then sort by this mailsort code and process again.  The sequence of which
the records are now presented must be maintained.

A. Every time we have a new mailsort code we insert a bagbreak symbol into a
field and set a counter to 1.
B. For subsequent records we increment the counter until either:
1. A new mailsortcode is found, or
2. The counter is equal to the maximum bags allowed per bag.  At this point
we go back to A.

So, having done all that we must now preserve that same order and start to
print the letters.
I am doing this via a REPORT so it is essential that the REPORT retains the
same sequence of records.

As you very correctly pointed out, for the sequence of records presentation
to be the same as in the table, the SORT BY needs to be done within the
Report's Sorting/Grouping whereas with the FORM is done straight from the
query.

The Point is this:  Why does the RPORT use a different engine that a FORM
when identical query is used.

Assume:
100 records all with Mailsortcode = "ABC" and another 100 with code = "BCD"
and bag breaks every 20 records.
The table was indexed on MailsortCode
When they were processed the bag breaks would have been inserted every 20
records and for each code change.
So, the first 20 records when view in a Form (using the same query that was
used when they were processed) may have been presented on the form/query as:
Record 2076
Record 17
Record 155
Record et

When the same query is put into the RecordSource of a Report, the sequence
changes, to (for example) Record 155 Record 2076 Record 17 Record et

In other words, they are correct within the mailsort code but the actual
sequence of presentation is different.

Phew!  I think I will drop this now.

Thanks for all your help.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, March 17, 2008 3:54 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Report Data Order Differs From Form or Query
DataOrder

Hi Max

I didn't say that. If you (now) have established a unique sorting you will,
of course, preserve that. The only place to do this for the report is,
however, in the report. That's the point.

/gustav

>>> max.wanadoo at gmail.com 17-03-2008 16:43 >>>
Hi Gustav,
No, I MUST NOT change the sequence in any way.  Once it has been processed
through a loop, each record will have a mailsort bagbreak inserted based on
its real life position in the table.  This must not change in any way from
there on.  No filters, no indexes etc, it must be the exact order it was in
when it was processed.
Thanks anyway.
Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, March 17, 2008 2:31 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Report Data Order Differs From Form or Query
DataOrder

Hi Max

Oh, now I see. You have to sort on something unique to obtain the exact same
sort order. 
If the fields you wish to sort on in the report do not form a compound
unique key, then include the PK in the query and pull it (invisibly) into
the report and sort on that as the last field.

/gustav

>>> max.wanadoo at gmail.com 17-03-2008 15:18 >>>
Hi Gustav,
I tried various things, but the bottom line is that where there are
repeating codes I am not confident that the report will pull the records in
the exact same order as it did for the Form even though it "appears" to do
so with the changes you suggest.

So, I have added an extra field which is incremented at the same time as the
codes are processed/entered and both the forms and the report now sequence
on that fields - fool proof!

Thanks for the feedback - much appreciated.

Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, March 17, 2008 10:10 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Report Data Order Differs From Form or Query
DataOrder

Hi Max

Did you use the sorting/grouping option of the _report_ and left the source
unsorted:

SELECT *
FROM LotterySpring2008;

/gustav

>>> max.wanadoo at gmail.com 17-03-2008 10:52 >>>
Hi Gustav,
Generally I accept what you are saying.
But, no matter what I do, I cannot get the report to have the same ORDER as
the Form.

The query is

SELECT *
FROM LotterySpring2008
ORDER BY LotterySpring2008.Mailsort;

The Query is called qryReportDataTemp.  The contents is dynamically created
from code where the tablename changes based on the user requirements.

The SOURCE for the Form is qryReportDataTemp The SOURCE for the Report is
also qryReportDataTemp

No filters, etc applied.
The Form shows records in correct order.
The Report does not.

Even if, as you suggest, I use the sorting/grouping option and explicitly
state "Mailsort" Ascending.  The ORDER of the records display in the report
is NOT the same ORDER as those display in the FORM or that which is shown if
you open the QUERY directly. 
 
It appears to me that there is a different ENGINE driving the Report.

Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, March 17, 2008 8:27 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Report Data Order Differs From Form or Query
DataOrder

Hi Max

Yes, _always_ use the sorting/grouping option of the report (and leave the
source unsorted) if you have a specific sorting/order need - which is true
for most reports.

/gustav

>>> max.wanadoo at gmail.com 17-03-2008 09:20 >>>

Is there a different "engine" used by the  RPORT as opposed to that used by



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list