Ordering on a foreign table fields

[Guest]

Okay, a bit of a fix here. I have two tables, Memberships and People and
forms to go with them.

People is like this:

People_ID People.Last_Name People_First_Name ...

Memberships is like this:

Membership_ID Person_ID ... (where Person_ID is an FK into People as
People_ID)


At the moment, when I use the Membership form, the order of the records
appearing is by People_ID. What I want is for the form to display the
records in order by First_Name, Last_Name.

The order by property in the form doesn't seem to accept things like
People.Last_Name or fancier SQL queries. I've also tried creating a new
field in the form which lists everyone in People in alpha order and ordering
by that, but that doesn't work either.

Any help would be greatly appreciated.

 

[Allen Browne]

Create a query to act as the RecordSource of your form.

In the query, include both the Memberships table, and the People table.

Drag the * from the Memberships table into the grid, so the query outputs
all its fields.

Drag the First_Name and Last_Name fields from the People table into the
grid, and sort on these fields. (You can uncheck the "Show" box, so they are
not displayed in the form's Field List.)

Because you are only entering fields into the table on the "many" of this
relation, the query should be fully updatable, and you should be able to add
new records without a problem. However, if any of the fields in the People
table have a Default Value assigned, you may that this confuses Access and
it gives you weird messages about not being about to assign a value to the
field named "|". Removing the default values from the fields in the People
table should solve this.

 

[Guest]

Thanks, that works.

Best,

Glenn

Create a query to act as the RecordSource of your form.

In the query, include both the Memberships table, and the People table.

Drag the * from the Memberships table into the grid, so the query outputs
all its fields.

Drag the First_Name and Last_Name fields from the People table into the
grid, and sort on these fields. (You can uncheck the "Show" box, so they are
not displayed in the form's Field List.)

Because you are only entering fields into the table on the "many" of this
relation, the query should be fully updatable, and you should be able to add
new records without a problem. However, if any of the fields in the People
table have a Default Value assigned, you may that this confuses Access and
it gives you weird messages about not being about to assign a value to the
field named "|". Removing the default values from the fields in the People
table should solve this.