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: 40
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
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,448
Thanks: 71
Thanked 1,089 Times in 1,017 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
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?
__________________
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.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

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.
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: 40
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
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,448
Thanks: 71
Thanked 1,089 Times in 1,017 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
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.
__________________
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.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

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.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
dafne (Yesterday)
Old 04-17-2018, 04:13 AM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,047
Thanks: 66
Thanked 1,355 Times in 1,278 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 (Yesterday)
Old Yesterday, 04:01 AM   #6
dafne
Newly Registered User
 
Join Date: Mar 2017
Posts: 40
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 Yesterday, 10:54 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,801
Thanks: 13
Thanked 1,293 Times in 1,232 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
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 Yesterday, 03:25 PM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,047
Thanks: 66
Thanked 1,355 Times in 1,278 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 Today, 01:30 AM   #9
dafne
Newly Registered User
 
Join Date: Mar 2017
Posts: 40
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 Today, 02:37 AM   #10
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,047
Thanks: 66
Thanked 1,355 Times in 1,278 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
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 05:51 AM.


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