How to specify a default sort order (non-primary key) for my table?

[sueb]

I have a table that appears in two forms: in one form, it is the "control", but in the other form, it is subordinate to another table (one-to-many relationship). In that second form, I want the "many" records of the subordinate table to be ordered by a date field (which is not the primary key).

I'm probably thinking about it all wrong, but I assumed that somewhere in the definition of that date field (or even somewhere on the subform) I'd be able to specify that it's the "sort key". I know I could do a query thing, but that seems like enormous overkill. Everything fits together so smoothly right now just opening the tables in the forms, except for this.

I feel REALLY dumb asking this question because it seems like it must be staring me in the face, but how do I do this?

(Edit: My table's Properties currently has "Order By" set to "IURs.[Receive Date] DESC", which I would have thought was the trick, but no.)

[Stewart Ross]

Hi Sue. Generally, forms should be based on queries rather than directly on the underlying tables. Using a query you can specify whatever sort order you want, completely independent of the table's primary key and index settings.

I would suggest that you define a query on the table concerned, include all of the fields for that table, then set the relevant date field into either Ascending order (oldest-first) or Descending order (newest first), then base your form on the query rather than on the table.

You can also use the query to sort on multiple fields if you want - introducing grouping into your ordering.

-Stewart

[NeoPa]

Also, forms have an Order By property that you can use if the ordering is specific to that form and you don't want it to be the default for other uses of the bound table or query. It also supports sorting by multiple fields.

[sueb]

Okay, that sounds like what I was sort of coming to. I guess I was just being a bit lazy, but I can see that using a query instead of the bare table is probably worth the extra step.

Thanks, both of you--I'll set about doing that.

(Oh, and I actually do have the "Order By" property set to use the field I want, but I still wasn't getting the result I expected. I'm sure all these troubles will be fixed using a query.)

[sueb]

I'm still not getting it right. Here's what my form originally looked like:

[imgnothumb]http://bytes.com/attachments/attachment/4268d1291069048/slide1-original-form.jpg[/imgnothumb]

Here's the query I made:

[imgnothumb]http://bytes.com/attachments/attachment/4269d1291069048/slide2-query.jpg[/imgnothumb]

I changed the form's record source (I also had to change the data source for the field "Receive Date", which puzzled me):

[imgnothumb]http://bytes.com/attachments/attachment/4270d1291069048/slide3-record-source.jpg[/imgnothumb]

So now when I run the form, I get this:

[imgnothumb]http://bytes.com/attachments/attachment/4271d1291069048/slide4-new-form-start.jpg[/imgnothumb]

and the form looks like this, with the patient information simply missing:

[imgnothumb]http://bytes.com/attachments/attachment/4272d1291069048/slide5-new-form.jpg[/imgnothumb]

What else do I need to do to make this happen?

[Stewart Ross]

Your screenshots show that a field - patient index - is missing. That is why you see a parameter entry box appear. Without this field I reckon you will not be able to match patient information.

Something is different between your query and the original table in some way. A query cannot of itself cause a parameter entry, or change the source of some other item on your form. It suggests that you have not included all fields from the original table in the new query, or that there are other things going on which you have not mentioned yet (for example, renaming a field using a conflicting name).

To know this for sure we'd need to see the list of fields from the underlying table itself, and the SQL for the query as it now stands.

-Stewart

[sueb]

All I did was create the query as you see it, then modify the Record Source of the form. When I opened the form, I got that request for a Patient index, and when I then looked at the form in design mode, I saw that the field for Receive Date now had an error. Once I changed the field to look for "IUR.Receiv e Date", it no longer had an error, but the form still behaved in the new way (asking for an index and having the patient area blank).

I changed the form's Record Source back to the table itself, and the Receive Date back to just plain "Receive Date", and it now behaves properly, although I don't have the sort I want.

That's it. I didn't change field names, and I used the ".*" convention in the query to make sure it pulled in all fields that the form would want.

[sueb]

How do I export the SQL so I can upload it? And what's the best way to show you the tables' fields? Would screenshots be enough? Or is there an export function for that?

[Stewart Ross]

Hi Sue. From the Access query editor you can select View, SQL View (dependent on the version of Access you are using) then copy the SQL for the query from there.

There is no equivalent for tables, I'm sorry to say.

I'm particularly interested in the names of the fields in the original table, and in how you have set up the query. If you are using tablename.* to select all fields, you must also have included additional copies of particular fields so that you can sort on them. This is one potential source of name clashes.

By the way, if it is an issue with the query itself you will get the same request for a particular parameter if you run the query itself.

If you are using a main-form sub-form structure it is also possible that the problem relates to the naming of the linked fields. Check any such links in your sub-form's properties to see if there are potential problems arising from the way the fields are named.

-Stewart

[sueb]

I don't get the parameter request when I run the query by itself. While the query does use .* for both tables that are involved, the single field on which I sort was dragged into the query from the table, so it's unlikely that it's the wrong name. I checked just to make sure, and it's correct. (You could look at the screen shot I uploaded, unless maybe those are not visible to you?)

Here's the SQL:

SELECT IURs.*, Patients.*, IURs.[Receive Date]
FROM Patients INNER JOIN IURs ON Patients.[Patient Index] = IURs.[Patient Index]
ORDER BY IURs.[Receive Date] DESC;

Also, the form/subform work correctly together until I introduce the query as the Record Source, so that's a puzzle. The join of the two tables is the field that's being requested, so I know it has something to do with that; I just can't figure out what.

[Stewart Ross]

Hi Sue. Your query is NOT a one-to-one replacement for the table you must have based your form on before; it involves TWO tables joined together (IURs and Patients). Moreover, it includes one duplicated field, IURs.[Receive Date], which is not otherwise aliased (given an alternative name). This is the field you have mentioned as being a problem since you made the change to the recordsource. Further, both tables in your query include a field called [Patient Index]. This is possibly the cause of the parameter request, as the [Patient Index] control that was placed on your form without the need to qualify its source tablename cannot any more be linked to the correct table as both tables in your query contain the same field with the same name (again, without aliasing). It's an example of the name conflict problem I referred to in my previous answers.

A multi-table join query was NOT what I meant when I advised that you based your form on a query; all I was suggesting was that you take your single-table recordsource for your form itself and replace it with an equivalent single-table query on the same table, with custom ordering introduced by setting whatever fields you needed to set into Ascending or Descending order as appropriate.

If you have a main-form sub-form structure you will have different recordsource queries for each subform and for your main form. The ordering of the forms concerned depends on how their separate recordsources are ordered, not on the ordering of the main form itself.

I can't emphasise enough that each form in a main-form sub-form structure has independent ordering; the ordering of the main form does not affect the ordering of a subform linked to it at all - the ordering of each subform is set by whatever is the recordsource of the subform concerned.

A multi-table query introduces unnecessary complication, and depending on whether or not grouping is used, and on other factors such as the relationships between the tables involved, can in some cases make it impossible to update records.

I'd suggest that you change your recordsource for the form concerned to be a single-table query, not a multi-table joined version. I don't know which of the two - IURs or Patients - this will be, although IURs seems the most likely given the patients sub-form you show in the screenshot. I would also suggest that you look at the recordsource of your patients subform and set appropriate ordering using an appropriate query for its recordsource too (unless you can do so using the subform's sort order property to achieve the same effect).

-Stewart

[sueb]

If I can't display fields from linked tables in a single form, then it doesn't seem to me that Access is providing support for real normalization. My form must show fields from both Patients and IURs. Currently, it does this just fine until I set the record source to the query.

Are you trying to tell me that my query is formulated incorrectly? Are you saying that I need to alias the sort field? (I haven't done that before, so I don't really know what that entails.)

I do, in fact, understand quite clearly that the ordering of the subform is separate from the ordering of the main form (how could it be otherwise?), and that's what I originally tried to do by setting the Order By property of the subform, but it didn't do the trick.

Would it be possible for you to be a little specific about how I can achieve my goal?

[Stewart Ross]

Sue, you are confusing normalisation with recordsources for forms. Normalisation is an absolute pre-requisite for database design and for getting the correct fields into the correct tables.

In the case of the application you show, which is a main-form sub-form structure, there is no need at all for the main form to be based on a multiple-table query. You have said yourself that the form was working when it was based on a single-table recordsource. I am solely advocating replacing the use of the direct table as a recordsource with an equivalent single-table query which can be used to custom-order your form.

I am not at all clear what sort problems you have been experiencing. I'd guess that they are actually with the ordering of the patient records in the subform, but as you haven't listed any sample data I just don't know if this is so or not. It is why I have emphasised that the main form and the subform are independently ordered depending on their recordsources. What this also means is that it is not possible to affect the ordering of patient records in your subform by changing the order of the recordsource of your main form. The main form filters the subform using the patient index field as the common field, but it does not affect the subform's separate ordering.

I'd suggest that you look at the subform's own ordering property, and if this does not do the trick I'd look at using a custom single-table query to order the subform.

In my own work day in day out I use queries for all of my forms. The underlying tables are named with the prefix tbl - for example tblPatients - and the equivalent queries are named with the prefix qry - for example qryPatients. I always base my forms on the query version as I can then choose the sort order etc as I want it to be. Many tables use autonumbers or other sequence numbers as their primary keys. These bear no relation to characteistics such as attendance dates that users may well be much more interested in, and that is why I always use a query to set the order of the recordsource of my forms, rather than basing the form directly on the table concerned.

-Stewart

[sueb]

So I think what you've been trying to tell me (in any case, it's what I just did that worked) is that my subform itself should be based on its own query.

I did that, and it works great.

Thank you for the time you spent trying to help me.

[Stewart Ross]

You're welcome Sue. It was indeed the subform's recordsource I was pointing to, and I'm glad you have found that basing the subform on its own query has resolved the ordering problems for you.

-Stewart