Max Wanadoo
max.wanadoo at gmail.com
Tue Mar 18 11:19:00 CDT 2008
Yes, that is exactly what I do Mark, When the table is parsed in mailsortcode order I append an incrementing number to a field (MailsortSequence) I then use that field (MailsortSequence) as the "ORDER BY" field for future queries, reports and forms. That way I KNOW that the records are in the same order as they were when the number (MailsortSequence) was inserted into the table. Thanks Mark, Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Tuesday, March 18, 2008 4:07 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Report Data Order Differs From Form or Query DataOrder 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 HotmailR-get your "fix". http://www.msnmobilefix.com/Default.aspx -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com