Query Based Removal of Duplicates

[WillYum]

Hold your horses, this is not a duplicate question.

In the simplest form of the question, I need to have a query that draws from two tables remove duplicate listings of a certain field and continue to show the rest of the data.

More detail:
I have two tables, ParentFiles and StudentFiles. Since we have many students who are in the same family I based the Address in ParentFiles and link appropriately to the StudentFiles table.

What I need to be able to do is print labels with the Students being addressed but only sending one letter to each household (realizing that it would probably way too complicated to include all the children on a single mailing label).

So I have my FirstName, LastName, Address, City, State, and ZIP fields. FirstName and LastName being drawn from the StudentFiles Table and the rest of the fields from the ParentFiles.

What would be the most efficient way of removing duplicate mailings to the same household.

Bonus: If you have a thought on how to add all the children linked to that Parent to a single label so that the label reads appropriately, "To the parents of John, Mary and PoPo Jones" or whatever, but hardly something I'd request.

Thank you for any help you can provide.

P.S. I did look into a query looking like this:
SELECT DISTINCTROW FIRST([field]) AS myvar, COUNT([field]) AS anotherVar
FROM table GROUP BY [field]
HAVING COUNT([field])>=1;

but I was unable to make it function correctly given my constraints. Thank you! - William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
Did you smile at your friends today?

 

[MichaelRed]

You do not state that there is any primary key, so I will not go in that direction. Looking at the field list, I would assume that the lastname and address fields might make a reasonable substitute, so selecting a record set based on their combination would appear to be an approach.

It is also completly unclear as to what (remainding) fields are available - or how you are joining the student/family tables, so the remaining details are up to you.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WillYum]

Michael, thanks for your response,

Both the StudentFiles and ParentFiles have Autonumber ID's as the Primary Key. I'm just joining them together in a query (Though they are linked via relationships)

Let me know your thoughts, am keenly interested in any guidance. Thanks - William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?

 

[THoey]

But, even if both tables have a primary key, if you don't relate them together some how, it doesn't matter. You need to have some sort of ParentID in the Children table. Then you could easily link and pull the data you are looking for... Terry M. Hoey

 

[WillYum]

Terry!

Sorry, I didn't make that clear. I do have the relationship. StudentFiles have ParentRefID in the table, every file is related to a Parentfile..

The relationship is in place, I can pull up the information I need, I just want to be rid of the duplicate mailings because we have 3 students with the same parents (therefore the same address) but I want the mailings to be addressed to the Students (or even just one of the students from the family) - William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?