Sort field items by the order in which they were added

[dafne]

Dear all,

I've been working with (what I hope is) a normalized database I've created to help me organize a conference, and in which I've had to establish many-to-many relationships through junction tables. For example, one abstract may have multiple authors, and an author may have submitted multiple abstracts.

Everything is going great - there's just one little thing that's been bugging me. When I create the report showing the abstracts and their respective authors, it's really important that the names of the authors appear in exact order they were added in my continuous sub form - not by ID, as it's currently the case.

For example, right now, my form shows:
Main form: Abstract 131
Sub form: Marie C., Dylan G.

Which is the order I'd like to keep in my report (not Dylan G. (ID 146), Marie C. (ID 160)).

Can you help me? Thank you in advance.

 

[isladogs]

You need to define a field to be used for the sort otherwise it will default to the first field ID. You can't use name either with your examples. Do you have a date entered field?

 

[dafne]

Dear ridders,

Thank you for your reply. No, I don't have a 'date entered' field. How and where should I add it, if it's not asking too much?

 

[isladogs]

Add the field to the table with the names used in the subform.
Set the default value to =Now() or if time isn't needed use =Date()
Add the field to your report sql and then sort by that field. It doesn't have to be visible.

Note that you will need to edit the new field for all existing records in your table.

 

[Galaxiom]

A datetime field as ridders suggested would work but it is slightly odd to store entry time when that isn't really something that matters. What you really want is presumably the order of author priority on the abstract.

I would use an integer field. It is simpler to determine and increment integers above a particular number so you can insert another in case of omissions than doing the same to datetimes.

The Priority field would be in the AbstractAuthor junction table. It can be incremented using a DMax + 1 on the BeforeInsert Event. The exact construct depends on how you build the form but it would be something like this:

Nz(DMax("[Priority]","tblAbstractAuthor", "AbstractID=" & Me.AbstractID), 0) + 1

The Nz() deals with the first entry.

The junction table should have a Unique Composite Index on AbstactID and AuthorID to prevent any combination being entered twice. I would probably use this index as the Primary Key rather than having a separate ID field. Junction tables rarely have child tables so a composite key is not an added complexity.

The advantage of ridders' datetime idea is that no code is required but it is hard to fiddle with later if you accidentally skip an author.

 

[dafne]

The junction table should have a Unique Composite Index on AbstactID and AuthorID to prevent any combination being entered twice. I would probably use this index as the Primary Key rather than having a separate ID field. Junction tables rarely have child tables so a composite key is not an added complexity.

Dear Galaxiom,
Thank you. I do have a Unique Composite Index (AbstractID + AbstractAuthorID) on my AbstractAuthor junction table. Does that mean I have to change your code?

 

[Galaxiom]

There is no reason to add an additional field unless you actually need the date/time added. Sorting by the autonumber will work fine.

Author precedence is real data. It isn't a good idea to rely on an autonumber to encode real data. Autonumber should not be used for any purpose other than providing a unique key.

Moreover, if a author was inadvertently omitted it would be an horrific task to insert them when the error was discovered.

 

[dafne]

There is no reason to add an additional field unless you actually need the date/time added. Sorting by the autonumber will work fine.

Thank you, Pat! I tend to agree with Galaxiom, though, when s/he says

Author precedence is real data. It isn't a good idea to rely on an autonumber to encode real data.

But I'm embarrassed to say I haven't been able to implement Galaxiom's solution. I've created a Priority (integer) field on my AbstractAuthor junction table and linked it to a control on my AbstractAuthor subform. I'm not sure about the code, though... what am I missing?

Thank you all for your invaluable help.
Dafne

 

[Galaxiom]

Code in the subform module

Private Sub Form_BeforeInsert()
    Me.Priority = Nz(DMax("[Priority]","tblAbstractAuthor", "AbstractID=" & Me.AbstractID), 0) + 1
End Sub

Assumes:
tblAbstractAuthor is the RecordSource for the Authors subform.

Subformcontrol Properties:
MasterLinkFields: AbstractID
ChildLinkFields: AbstractID

The BeforeInsert fires as the record is created by entering an AuthorID. AbstractID is automatically added to the new record via the subform LinkFields.

The DMax finds the highest AuthorID already present in the table for that AbstractID.

I think it should be fine but if it doesn't work it would be because the AbstactID is not yet populated.
This could be fixed by reading the AbstractID from the parent form:

"AbstractID=" & Me.Parent.AbstractID

 

[dafne]

The request was to sort rows by the order in which they were added. It was not to sort rows by the order in which they should have been added and the autonumber does exactly that.

Hello, Pat! Mea culpa, then. I should have explained myself better.

In my continuous subform AbstractAuthors, I want to make sure that the first author entered gets the label "1" automatically, the second author "2" and so on. For example, for abstract ID 46:
(first combo) José Teixeira (ID 25) ---- 1
(second combo) Marie Lavoisier (ID 13) ---- 2

I'm really willing to learn, and I thank you for your precious input, but I haven't been able to implement any of the solutions presented here

Thank you all for your help!

 

[arnelgp]

You should add now the date/time field in your table. Or a number field which states the position of the author in the list. Have a function that generate the sequence for you.