How to remove duplicate recod 1

[nvtjellis]

I have 3 tables each primary key is SPONSORKEY.

SPONSORS table CHILDREN table CONTACT table
sponsorkey sponsorkey sponsorkey
last name childrenkey contact
first name last_name conttype
mi first_name comment
MI

When I run the query multiple sponsors show, I assum because the is one-to-many child condition and there
one-to-many contacts. I need:
One sponsor with one_to_many children and one_to_many contacts.
Currently with the run I get a repeat of the sponsor listed for each child and contact.

Here is what I would like:
sponsor children conttype comment
smith Larry smith windy doctor dolittle 123 west nowhere
smith james grandmother 456 east sunnyside
jones spike jone spike jr. uncle peg 909 brezy lane

I don't see how to get this results.....

 

[ghubbell]

Working with your table design (which is pretty good! but could be "tweaked" a bit):
Sponsors has a one to many relationship from its sponsorkey (primary) to sponsorkey (foreign) in Children? Sponsors has a one to many relationship from its sponsorkey (primary) to sponsorkey (foreign) in Contacts?
If so, in a query based on this arrangement you will see multiple listings of the sponsors. This is quite normal!

If you now use a new report wizard and base the report on this query, it will sort and group this in a fashion you choose. If you don't like the results, delete the report and try again.

If you need to create forms to enter this info, use a form wizard to build a "main" form based on the sponsor table. Make two more forms in the same fashion based on each of the other tables.

Now open your main form in design view (the main form "Default View" should be set to single form while both of the others to continuous or datasheet), and with the wizard button (wand & stars) depressed on the toolbar, click the subform/subreport toolbar button and drag out the rough shape of one of your other forms on to the main. Follow the steps in the wizard making sure to link the two forms by your "sponsorkey".

Do the same operation again for your last form, again, placing it on the main.

Now you should have both a form and a report that are legible. Write back if you need additional help! Good luck, Gord
ghubbell@total.net