[bobbi]

Hi

I have a question about removing duplicate records in query and report output.

Here is the overview. We have a database of Summer Campers. Several of the campers are children from the same house hold.

I have a query which adds all the kids to one postcard to be sent out. Then another query which runs the labels to be put on the postcard for mailing.

When I run the labels, each record (for each child) is a result. So I may have 2, 3 or even 4 lables for the same address. I only have one postcard.

I tried using the unique values and unique fields in the properties of the query but that does not work. I also tried saying "hide duplicates" in the report but that is per field so it will hide the address but leave the city state and zip. Of course I can't hide those because other people live in that same city, state etc.

I do not want to delete the duplicate records because they are each different kids in different sessions of camp. I just want to be able to print out one label per address.

Any ideas?

[OBP]

bobbi, how about running a query with just the Post (Zip) Code, in, set to unique values.
Then run a second query which has the first query as it's "table", add the original table of names and addresses, with the link between the (code) query and the address table set to Post Code and all the records from the Code query but only the matching records from the Addresses table. This should be set to Unique Records as well.
If you could post a zipped copy of the database I could probably do it for you.

[bobbi]

I'll try that.

The DB is at a remote site, nature center, and they are on dial up. I would need to go get the database. I'll give it a try, if I can't do it I'l take you up on your offer.

Thanks

[cristobal03]

The underlying problem is an issue with the database structure. It isn't normalized, I would assume, if you have as many as four address records that are exactly the same. I bet the reason you haven't seen the UNIQUE constraints work in your query is because there are no foreign keys in the tables on which to apply the constraints.

For the record I would strongly advise against posting a database on this site that contains private information about kids' addresses and so forth. If that's the way you want to go, send a private message to OBP and work things out through email.

chris.

[bobbi]

Yes, thank you for that information. I had already contacted OBP privately because of that very reason...I didn't want to put kids names and addresses out there. But I appreaciate the warning.

[OBP]

bobbi, any success?

[tssells]

Try using the distinct function in your query.