Sort field items by the order in which they were added (1 Viewer)

dafne

Registered User.
Local time
Today, 11:02
Joined
Apr 1, 2017
Messages
41
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

MVP / VIP
Local time
Today, 11:02
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 11:02
Joined
Apr 1, 2017
Messages
41
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

MVP / VIP
Local time
Today, 11:02
Joined
Jan 14, 2017
Messages
18,186
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

Super Moderator
Staff member
Local time
Today, 22:02
Joined
Jan 20, 2009
Messages
12,849
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:

Code:
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

Registered User.
Local time
Today, 11:02
Joined
Apr 1, 2017
Messages
41
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:02
Joined
Feb 19, 2002
Messages
42,970
There is no reason to add an additional field unless you actually need the date/time added. Sorting by the autonumber will work fine.

Reports march to the beat of their own drummer. Access is smarter than us and because it is, it recreates the report's RecordSource based on what columns are bound to controls on the report (Forms do NOT work the same way. With a form, your RecordSource is left as is). Because of that, you will need to bind the autonumber ID field to a control on the report. Make it hidden since it isn't necessary to see it. Then you can use the report's sorting and grouping options to sort by the ID.

Remember, because the query for the RecordSource is rebuilt, you cannot rely on the query for sorting. Always do your sorting using the Report's sorting and grouping options.

I had used Access for probably 10 years before I figured this out. Every once in a while, I would have code in a report that referenced a column that wasn't displayed. It would work when I tested but once I saved and reran the report, it would break. By trial and error, I discovered that if I bound the column to a hidden control my code would work. The point is that the situation came up maybe once or twice a year that I had to use a column I didn't need to see so it took quite a while for the pattern to emerge. But, there it is. Now you know too :)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:02
Joined
Jan 20, 2009
Messages
12,849
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

Registered User.
Local time
Today, 11:02
Joined
Apr 1, 2017
Messages
41
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. :eek: 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

Super Moderator
Staff member
Local time
Today, 22:02
Joined
Jan 20, 2009
Messages
12,849
Code in the subform module

Code:
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:
Code:
"AbstractID=" & Me.Parent.AbstractID
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:02
Joined
Feb 19, 2002
Messages
42,970
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. The autonumber does in fact contain that piece of information with relation to other records in the same table.

If you want to be able to alter the sequence AFTER THE FACT (not part of the problem statement and based on my understanding of what you are doing, unfair), you should probably use a date/time field. The date/time field can be autopopulated but you can allow updates if that is what you need to do. Using a generated sequence number gives you the same effect as the autonumber. An unchangeable sequence. It doesn't add value. It is simply a different way to log data entry order. And it is much harder to alter if you actually need to modify a sequence later.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:02
Joined
Feb 19, 2002
Messages
42,970
The DMax finds the highest AuthorID already present in the table for that AbstractID.
Unless your names are funny, the DMax() isn't working with the correct fields.
 

dafne

Registered User.
Local time
Today, 11:02
Joined
Apr 1, 2017
Messages
41
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:02
Joined
May 7, 2009
Messages
19,169
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.
 

Users who are viewing this thread

Top Bottom