Can I do this with a query?

[clars2000]

Need help to solve the following Access 2000 problem:

Create a procedure, activated by a button on a form, to group similar
records into a single record.

table "tblindividual" is populated with member records, like members will
have the same member number and different depid's.
table "tblfamily" has the same structure as "tblindividual" with the
addition of two sets of dependent columns. Append the records from
"tblindividual" to "tblfamily" grouping members by member number in
dependent id order. If there are more than three family members create
another record for that member number.

Table definition for tblindividual(MemSub, MemDepId, MemFName, MemLName, MemInit)
Table definition for tblFamily(MemSub, MemDepID, MemFName, MemLName, MemInit, Dep1FName,
Dep1LName, Dep1Init, Dep1ID, Dep2FName, Dep2LName, Dep2Init, Dep2ID)

Please help! clars2000

 

[ghubbell]

Hi Clars2000!

What we have here is a structural problem...

TblFamily
FamilyID (P,1-n,an) unique no dups
FamilyLName
PerhapsMoreInfoAboutTheFamily

TblIndividual
FamilyID (F) dups allowed connects up to above!
MemDepID (P,an) unique no dups
MemDepFName
MemDepInit
PerhapsMoreInfoAboutTheIndividual

P Primary key, 1-n one to many relationship, an AutoNumber, F foreign key

With this structure you are unlimited in the number if individuals you can have in your family.
With a query you would be able to "reassemble" the individuals name.
On a "Main form" based off of your Family table, install a subform based off of your Individuals table.
Set the Default Value of the FamilyID field on the subform to something like:
[NameofMainForm]![FamilyID]

Any entry will now automatically connect the two. (and you can even make this field invisible 'cause it's just a "connection" to the family)
No buttons to push! All automatic! Gord
ghubbell@total.net