Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / February 2008

Tip: Looking for answers? Try searching our database.

how can we disable of primaryindex

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Frank Situmorang - 14 Feb 2008 12:49 GMT
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.
Signature

H. Frank Situmorang

Allen Browne - 14 Feb 2008 13:06 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> In my church membership database that I am working on, I have the form
> similar to the MS Access database sample Address data base.
[quoted text clipped - 18 lines]
> Hope that you can understand my problem, coz I could have the language
> problem.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.