need help with sorting/ updating my table (1 Viewer)

[Katie2322]

I am making church registry which includes first, last, middle names, DOB, date of marriage, and a whole lot else. The way I was asked to put this together is having a new record (line) for each family member. So if a family has 5 people it has 5 lines. each family also has a family number that is used really to differentiate between families with the same last name, like Smith. As it stands now, I am entering the family number in the first column, named 'FAM ID', but only once for the whole family. So basically I have something like this:

FAM ID Last Name First Name
1 Smith Nancy
Smith John
and so on, and so on. My question lies with sorting. While not all familyies have the same last name within the same family, I want them all the same number. The name lists cannot be sorted aphabetically due to some families having different last names. I have entered all the information of some 250 families thus far, but as new families arrive, I want them to be in alphabetical order. For example, John and Nancy Smith may have child with a different last name, i.e. Davis, but that child is still with her parents. When I need to add new names how am I going to get them to be in the right place in the list, as alphabetically as possible. I know that I can't just insert a new record in the middle of a list. I really am new to access, and do not know much about forms, subdatasheets, I know a little about queries but thats about it. THe family ID number is less important than is the order of all the families alphabetically. If I sort the fam ID column ascending then all the family names that do not have the number nexto them are moved to the top and the list is screwed up again. I hope someone here can make some sense of this. I do not know what else to say to explain it any better. I am really getting frustrated with all of this and how to do all of this. Any help is much appreciated.

I just thought of another question, is there any way to tell access to insert a new name between say record 174 and 175 thus bumping 175 and the rest down one row? If that is a possibility then I would think that is how I could keep updating my list with keeping it alphabetical. The new family ID number may be 258 but like I said above that is less important than an accurate list of names.
Thanks agin for any help

 

[ColinEssex]

Hi Katie

You gave a good detailed description of your table. Why is it so important to have the table alphabetical? It doesn't really matter in a table, you can sort things later using queries etc.

Each family has an ID number, so can't you sort alphabetically within each ID number? or have I missed the point

Col

 

[cogent1]

I agree with the last respondent. You don't need to worry about getting things in the right order at table level. You can bring all members of a family together by having a shared family ID field and sorting by FamilyID and then by Last Name, First Name in a query. You then use a form based on that query to show your records just as you want them to appear.

Putting multiple names on one row is not a good idea, because you will soon run up against the maximum of 255 characters for a text field. If Jim Smith, Jill Smith and Mary Bloggs all belong to one family , put them on separate lines (rows) and use the same familyID on each row to show that they are all one family.

Another way to approach the problem is to have 1) a family table and 2) a family members table and link them in a one-to-many relationship. I don't know if you feel ready to look into this aspect of design yet, but ultimately it's probably the best way to do it.

 

[Katie2322]

thanks, but need more help

If I just put all the names into the fields, last and first, including all other fam. members with different last names, with the same family, but give the whole family one ID number then I will be able to sort the table, or really find one family and all its members by using a query? Is that correct? Now I have also encountered another problem. THe way Ihave been entering the info thus far is to only assign the ID to the first name in the family. The rows below that are of the same family but do not have the numbers. My problem has been that I will do hours worth of work and shut out the program after saving, and the next time i go to open it again, all the info is so out of order it isnt even funny. How do I fix that? thanks for all the help

 

[Hayley Baxter]

Katie

To sort the family members in a query drag the items you want to sort to the design grid so sa for example you are sorting by family id then by family member alphabetically. You would first drag the family id to the grid in the query and sort ascending then secondly membername ascending. A query sorts in the order you place them on the grid.

If you're struggling you can email me your database, import all the objects etc into a blank db delete all your confidential data and I'll add some dummy data to work with.

Let me know if you are interested
Regards
Hay

 

[cogent1]

Yes Katie, you will be able to manipulate the data just as you wish if you construct your tables properly. The basic structure has to be correct though. I would take up Hayley's generous offer, because you are obviously feeling your way at the moment and lack the expertise to proceed with confidence.

 

[Pat Hartman]

Let me propose a somewhat different solution. You need two tables. One to hold information relating to people and a second for addresses. You really don't want to duplicate the address information for 5 family members. The other big change is how you group families. I would designate one member of the family as the head of household and use that member's RegistryID as the FamilyID for ALL family members. Single people would have their own RegistryID stored as their FamilyID. Then to solve your sorting problem you can join the Registry table to itself on FamilyID to RegistryID and select the Lastname and FirstName of the family head for sort purposes.

tblRegistry
RegistryID (autonumber, primary key)
LastName
FirstName
...
FamilyID (foreign key pointing to RegistryID in this table, required)
AddressID (foreign key pointing to the AddressID in tblAddress)

tblAddress
AddressID
Addr1
Addr2
City
State
Zip

Then a sample query to produce a Registry in family name sequence:

Select f.RegistryID, f.LastName As FamilyLastName, f.FirstName as FamilyFirstName, r.LastName, r.FirstName
From tblRegistry as r Inner Join tblRegistry as f ON r.FamilyID = f.RegistryID
Order by f.LastName, f.FirstName, f.RegistryID, r.LastName, r.FirstName;