how can we disable of primaryindex

[Frank Situmorang]

Hello,

In my church membership database that I am working on, I have the form
similar to the MS Access database sample Address data base.

It has tab control, for tab 1 is address and for tab 2 there we can see all
the members of the household.

I have a problem that on the tab 2, since the member table has the memberID
primary key ( of course it is indexed) so in the tab 2 the presentation of
the members record is sequenced by member, while what I want is sequence by
Role ( from Role Table starting form Father, Mother, .....)

My question is how can i make it in the 2nd tab sequenced by role. But I can
not do it because the data is already indexed by member id, so even though I
have made it the query sorted ascending by Role, it does not work.

Hope that you can understand my problem, coz I could have the language
problem.

 

[Allen Browne]

If you don't specify any other order, Access does present the records in
primary key order. But it's easy enough to create a query that sorts the
records as you want, and use that as the source for your form.

I assume that you have a related table where each family member's ID is
stored, along with their Role in the family. You will therefore have a
little lookup table containing a record for each Role. You need to add a
Number field to the Role table to indicate how you want them sorted.
Example:
Role SortOrder
Parent 1
Child 2
Guest 3
(I'm suggesting gender-less roles here rather than Father or Mother, because
that can be derived from the person's gender, and the data would be invalid
if you have a female Father.)

You then create a query that contains both the Role table and the table of
family members. In query design, you use the Sorting row to sort by Role
first (on the left), and then by Date Of Birth (so older children are listed
before younger ones), or whatever other order you want. Then you make this
table the Record Source of your subform.

Notes
====
a) Make sure you get the fields from the correct table into your query.
Otherwise you could be altering the Role table instead of the table you
intend.

b) If Role is not a required field in your household table, use an outer
join. Otherwise people without a role won't show up. More info:
http://allenbrowne.com/casu-02.html

c) If any of the fields in the Role table have a Default Value, you can get
an error message when you try to add a new record. In some circumstances,
Access makes the mistake of trying to assign the default value to the fields
of the lookup table, even though no new record is being added there.