Design question on Sorting a Form's Recordset

[rkl122]

Hello all. I've been building a small app for some friends. At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process.

Ok, so I've adopted the datasheet form as the principal entry vehicle. Based on the initial info they gave me, there were two convenient fields to sort on, so I used them in the OrderBy clause of the query which serves as the form's recordsource. I also developed the code to maintain the sort order after they inserted or deleted a record.

Now it turns out they don't require an entry into those fields, and in fact there's no logical field at all on which to order by.

So the question is: how to approach this? From my reading, I can't use the primary key or an autonumbered field, since the former won't necessarily reflect the desired incrementation and the latter only triggers for a new record, not an insertion (is this correct?).

My thought is to add a simple numerical field (which will be hidden from the user) with sequential values. I can do the OrderBy on it, and add code to adjust those values whenever a record is deleted or inserted. (That will require looping through the recordset from the point where the deletion or insertion occurred.)

I'd be interested to hear if there's a better or more common approach. For instance, is there some way to add a calculated field to the recordsource for this purpose?

Thanks for any help,

-Ron

 

[rzw0wr]

Can you show us you query?
I don't understand "No Logical" field.
If there is no logical field then possibly you don't need to sort your query.
Order=sort.
How do you want your fields sorted?

 

[rkl122]

The query for the form recordsource is

SELECT tblShotList.ShotNumber, tblShotList.ShotNumSuffix, tblShotList.Inline, tblShotList.ShotDescr, tblShotList.ShootsFK, tblShotList.CamerasFK, tblShotList.ShotListID
FROM tblShotList
ORDER BY tblShotList.ShotNumber, tblShotList.ShotNumSuffix;

(This is actually a subform, filtered by a one to many relationship, where tblShoots is the one and tblShotList is the many.) By "no logical field," I mean that (as I only now learn) tblShotList.ShotNumber and tblShotList.ShotNumSuffix can be null. Yet the user wants all records to appear in the same sequence as they would have typed them into a Word document. (Yeah, I should be more careful about using a phrase like "logical field" in a database forum. I meant it intellectually, not boolean. )

Keep in mind, I'm trying to transliterate a "word processing" mentality into a database reality. This is what I told them they should do. It's gonna be sort of embarrassing if it turns out I'm wrong about that

So the answer to

How do you want your fields sorted?

is: in the order entered.

Thx for looking,

-Ron

 

[Pat Hartman]

Use an autonumber as the PK. It will increment sequentially for each new record. The actual value of the field is not important and there will be gaps for a variety of reasons but if you sort on the autonumber, the sequence should represent the data entry order.

 

[rkl122]

Use an autonumber as the PK.......

Thanks. I already have an autonumbered primary key, and yes, it has many gaps, but I thought I read that it also can sometimes show up as a negative number. (Some access quirk?)

Anyway, my "order entered" answer is too quick and simple. The user can insert records in places where those original sorting fields, ShotNumber and ShotNumSuffix, have values, so it's those fields which determine the sort order at that point. It's really the records for which there are nulls in those those fields that need somehow to be kept in the original order of entry.

Hence the idea in my OP about a simple incrementing numeric field that will adjust after each insertion, deletion, or new record has been entered. Absent a better idea, guess I'll go with that.

Thanks for reading, -Ron

 

[Pat Hartman]

Then use the autonumber as a secondary sort. There is no need to attempt to create your own. Sort by ShotNumber, ShotNumSuffix, YourAutonumber.

 

[rkl122]

Thanks. Access only permits one auto-numbered field per table, and as mentioned, I already have the PK field autonumbered. So you're saying use *it* for the third degree of sort?

Will try on a test table, but not sure that's sufficient. There can be discontinuous clumps of records with no data in ShotNumber or ShotNumSuffix. Further, user may want to insert a record among the ones with no data in those fields. Seems like that sorting scheme wouldn't preserve the desired sequence. (I've already developed an insertion routine, based on trapping for a particular key combo when an "insertion" is desired below the current record. As long as I have an OrderBy clause that's sufficiently robust, I can make any new record appear to have been inserted into the datasheet at the desired point. (Guess that's stating the obvious.))

Gotta think carefully how the db will be used, but at moment still thinking safest to create my own auto-incrementation for the additional field.

Thx, Ron

 

[Pat Hartman]

Further, user may want to insert a record among the ones with no data in those fields.

That isn't the way relational tables work. Physical position is a flat file concept. You can't move to a spot and insert "there". Position in a recordset is dictated totally by how you ordered the recordset. If you didn't specify an order by, the order is indeterminate. That means the query engine will choose based on how it elects to retrieve the rows.

There can be discontinuous clumps of records with no data in ShotNumber or ShotNumSuffix.

Not if you order by the autonumber within the other two fields.

still thinking safest to create my own auto-incrementation for the additional field.

Do some reading on relational theory. I don't see why you think you can do this better than the database engine. If you are thinking of generating numbers to fill gaps, that is absolutely wrong thinking. It is never appropriate to reuse missing IDs.

 

[PNGBill]

This appears to me similar to the age old problem of producing a Catalogue and or Price List from a table of SKU's where the "word" price list document needs to be presented in a way not related to, SKU, Description or other field.
Sometimes, more then one ordered document is required. Even multiply entries of some records eg common replacement parts.

Maybe this is why some people still use Word and Excel

 

[rkl122]

That isn't the way relational tables work...........

Thanks for the comments. Sorry if I gave impression I don't understand difference between the relational model and a flat file. I have a lot to learn about syntax, etc. but I do respect, and greatly admire, the power of the relational paradigm.

As indicated in my OP, I'm trying to *simulate* the appending and insertion of records in a certain order. Yes, by appropriate ordering. I'm doing a small project for friends, who only now inform that not all records will have data in the fields on which I was depending for the OrderBy clause. I don't want to "reuse missing IDs." I'm just contemplating whether to add an *additional* field for that "third degree" of sorting - ie. in order to achieve the simulation of a record insertion for records which lack the first two "degrees." (I'd have thought that illustrates that I do understand the relational model, not the opposite.)

But in the end, I may accept the implication of your remarks: it aint worth it. My friends may need either to better accommodate the relational paradigm - by accepting that some fields are required - or learn VBA for Word

I do appreciate your thinking about the problem.

-Ron

EDIT - On reflection, I realize that if I were to add that additional column that re-sequences itself each time there is an "insertion" or "deletion" in the datasheet, it's the only orderby field I'd need. Apologies for not acknowledging this before and perhaps avoiding some confusion. (Still think I understand the relational paradigm - just not all its operational implications. )

 

[Pat Hartman]

But you don't yet understand that the autonumber (in its standard definition) does provide you with insertion order so there is no need for a new field for this purpose. If you want to completely control the order manually (you don't have any other columns that will sort the data you want to see it.) then let the user type in a "sequence" number. You'll need to give him a way to renumber the sequence field when the gaps get too small. In your original post you wanted this to be hidden but that doesn't make any sense. The only way you could hide it was if you had some other field that told you where it needed to go and since you don't, the user has to specify where the record should go and so the user needs to assign the sequence number manually. You can assign a number (rounded to hundreds to give room for insertions) to the existing records and let them pick up from there.

 

[rkl122]

Either I'm truly missing something, or I've failed to portray the scenario I'm addressing. I'll try again.

But you don't yet understand that the autonumber (in its standard definition) does provide you with insertion order so there is no need for a new field for this purpose.

It's precisely because I do understand that that I can't use it. (Not sure what part of "At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process. " and " I also developed the code to maintain the sort order after they inserted or deleted a record." (Post #1) or " As long as I have an OrderBy clause that's sufficiently robust, I can make any new record appear to have been inserted into the datasheet at the desired point." (Post #7) or " I'm trying to *simulate* the appending and insertion of records in a certain order." (Post #9) are unclear, but I can see how my references to keeping the records in "original order" can be confused with "order of entry.") So to be clear, I'm not concerned about order of entry. I'm concerned about the sequence of records on the data entry screen. I'll illustrate below.

If you want to completely control the order manually...

Yes, I do.

... (you don't have any other columns that will sort the data you want to see it.) then let the user type in a "sequence" number. You'll need to give him a way to renumber the sequence....

Here's where I disagree. I think the system can keep track. Hence the "hidden field" mentioned in the thread. I'll give a hypothetical illustration.

Say the user is designing a sequence of instructions for assembling a chair - ie. chair in the main form, assemble steps in the subform. Each step of the process will be a separate record in the subform datasheet. So the user starts entering records in the datasheet. The recordsource for the form has ...orderby fldMySort. Each new record is created by tabbing into it. The PK autonumber field always increments by 1, as does a hidden control called "txtMySort" - whose value, which is bound to fldMySort in the recordsource, will be set in code (OnCurrent; not by the user) by DMax + 1. The user continues putting the chair together Suddenly, after a dozen steps, he realizes he left out a step. After step 5, he forgot to attach the arms. So he places the cursor on step 5 and hits a predefined key combination, say CTRL-I. Now the keydown event, which has been trapping for this key combo, springs into action. It executes code that reads txtMySort for record 5, it adds 1, creates a new record, then assigns 6 to txtMySort. At this point the autonumber field (the PK) is the next highest value in the entire recordset, so the PK is no longer equal to txtMySort. Then, using the recordset object, the records "below" this new sixth one are looped, and for each, txtMySort is incremented by the code. To the user, the new record has been inserted automatically on the screen just where he wants it. He's never aware that txtMySort is even there and that it has been incremented for every record that had followed the one beneath which the insertion was made. (For all of those, the autonumbered field no longer equals txtMySort.) A similar reordering is done if he deletes a record.

My apologies for didactifying this. Wanted to clear any confusion I caused above. I'd still be interested to know if folks think this is a viable scenario and/or if there is a more efficient one that accomplishes the same thing.

-Ron