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

Mark A Matte markamatte at hotmail.com
Tue Mar 18 11:06:58 CDT 2008


Max,

This isn't so much about solving your issue...just stating some highlights that may or may not help.  Sorting of data can be in many places.  Table, query, form and report can all have different sorts for the same data.  Is it possible 1 of the 4 is out of sync?  You can sort in a query...but display it sorted differently in the form...and have a report on the same exact query...but the report sorted differently.

For my postcard printing the print order is essential because I am printing duplex with customer info on both sides of the card...so I have to maintain the order at all cost.  I use an autonumber generator to insure the order is preserved.  I use an APPEND query to a temp table to preserve the order because a SELECT query display will cause the code to fire again as you scroll.  I just have to reset lngTableCounter  before I run it each time.  Then my form and report are sorted off this number

Good Luck,

Mark A. Matte

*******************
Option Compare Database
Option Explicit
Global lngTableCounter As Long

Function MyAutoCtr(prmAny)
'Trick is to pass a field from the input table(s) so that function called for each record
'otherwise Access thinks that the function will always return the same value and
'only calls it once, and every output record gets the same value
MyAutoCtr = lngTableCounter
lngTableCounter = lngTableCounter + 1
End Function
********************


> From: max.wanadoo at gmail.com
> To: accessd at databaseadvisors.com
> Date: Tue, 18 Mar 2008 09:50:21 +0000
> 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

_________________________________________________________________
Need to know the score, the latest news, or you need your Hotmail®-get your "fix".
http://www.msnmobilefix.com/Default.aspx



More information about the AccessD mailing list