Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2018, 01:54 AM   #1
dafne
Newly Registered User
 
Join Date: Mar 2017
Posts: 41
Thanks: 16
Thanked 1 Time in 1 Post
dafne is on a distinguished road
Sort field items by the order in which they were added

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.

dafne is offline   Reply With Quote
Old 04-17-2018, 02:07 AM   #2
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,805
Thanks: 79
Thanked 1,428 Times in 1,333 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Sort field items by the order in which they were added

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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
dafne (04-17-2018)
Old 04-17-2018, 02:14 AM   #3
dafne
Newly Registered User
 
Join Date: Mar 2017
Posts: 41
Thanks: 16
Thanked 1 Time in 1 Post
dafne is on a distinguished road
Re: Sort field items by the order in which they were added

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?

dafne is offline   Reply With Quote
Old 04-17-2018, 03:42 AM   #4
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,805
Thanks: 79
Thanked 1,428 Times in 1,333 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Sort field items by the order in which they were added

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
dafne (04-18-2018)
Old 04-17-2018, 04:13 AM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,183
Thanks: 69
Thanked 1,394 Times in 1,315 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Sort field items by the order in which they were added

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.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
dafne (04-18-2018)
Old 04-18-2018, 04:01 AM   #6
dafne
Newly Registered User
 
Join Date: Mar 2017
Posts: 41
Thanks: 16
Thanked 1 Time in 1 Post
dafne is on a distinguished road
Re: Sort field items by the order in which they were added

Quote:
Originally Posted by Galaxiom View Post
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?
dafne is offline   Reply With Quote
Old 04-18-2018, 10:54 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,246
Thanks: 13
Thanked 1,382 Times in 1,317 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Sort field items by the order in which they were added

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

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-18-2018, 03:25 PM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,183
Thanks: 69
Thanked 1,394 Times in 1,315 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Sort field items by the order in which they were added

Quote:
Originally Posted by Pat Hartman View Post
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.
Galaxiom is offline   Reply With Quote
Old 04-19-2018, 01:30 AM   #9
dafne
Newly Registered User
 
Join Date: Mar 2017
Posts: 41
Thanks: 16
Thanked 1 Time in 1 Post
dafne is on a distinguished road
Re: Sort field items by the order in which they were added

Quote:
Originally Posted by Pat Hartman View Post
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

Quote:
Originally Posted by Galaxiom View Post
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
dafne is offline   Reply With Quote
Old 04-19-2018, 02:37 AM   #10
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,183
Thanks: 69
Thanked 1,394 Times in 1,315 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Sort field items by the order in which they were added

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
Galaxiom is offline   Reply With Quote
Old 04-19-2018, 02:00 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,246
Thanks: 13
Thanked 1,382 Times in 1,317 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Sort field items by the order in which they were added

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-19-2018, 02:04 PM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,246
Thanks: 13
Thanked 1,382 Times in 1,317 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Sort field items by the order in which they were added

Quote:
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-24-2018, 04:20 AM   #13
dafne
Newly Registered User
 
Join Date: Mar 2017
Posts: 41
Thanks: 16
Thanked 1 Time in 1 Post
dafne is on a distinguished road
Re: Sort field items by the order in which they were added

Quote:
Originally Posted by Pat Hartman View Post
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!
dafne is offline   Reply With Quote
Old 04-24-2018, 06:08 PM   #14
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,348
Thanks: 54
Thanked 2,029 Times in 1,942 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Sort field items by the order in which they were added

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.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
many-to-many relationship , report , sort

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Keeping track of added/removed items in a field canonquiche Queries 6 01-06-2016 11:21 AM
Cannot chaneg sort order on float field Salbrox Queries 1 04-22-2013 05:25 AM
VBA Rx to switch index of local temp table to update Multiple Items form sort order mdlueck Modules & VBA 14 09-02-2011 10:50 AM
Concatenate Field, order in which items appear cdoyle Reports 4 07-18-2008 09:52 AM
Can I create a sort order for a field/table? vangogh228 Tables 4 09-03-2002 09:04 PM




All times are GMT -8. The time now is 04:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World