 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2006duplicate mailing labels
i am creating a database (in access 2000) for mailouts among other things. i found a tutorial in the knowledge base to allow 2 addressees at the same address using 3 queries. one uses 'first' function on names, one uses 'last' and the other links the two queries to give only 1 address with 2 names (see http://support.microsoft.com/kb/207753/en-us). my problem is that if there are two people at the same address with different last names, the last step doesn't work and 4 entries are created (1 for each combination of the names). how can i stop this? i thought of running a filter to get only 1 of each address at the last step but i'm not that cleaver :(. another thing, is it easy to make fields take the values of the previous record by defult? this would make data entry for same addresses a bit easier. thanks! well this isn't the answer you seek; but we had the same situation and in our case we decided was that duplicates needed to be identified upon data entry and not via a query at the time of printing labels.... so we added a new column where we entered 1 if it was a unique address; then 2 if a duplicate address, or a 3 if the third entry, and so on..... this obviously takes more time upfront at the time of data entry....not alot - one does a search on the street address...but a few more seconds.... it also helped sort out missing POBoxes from apartment buildings.... but the benefit of seeing multiple entries for the same address was very helpful...and for the query for labels it was of course very simple to require only "1" be returned....  SignatureNTC > i am creating a database (in access 2000) for mailouts among other things. i > found a tutorial in the knowledge base to allow 2 addressees at the same [quoted text clipped - 11 lines] > > thanks! surely there's a way to delete all but 1 duplicate of each address from the last query. the person entering the data has never used a database b4. i don't want to confuse her with extra fields. > well this isn't the answer you seek; but we had the same situation and in > our case we decided was that duplicates needed to be identified upon data [quoted text clipped - 11 lines] > helpful...and for the query for labels it was of course very simple to > require only "1" be returned.... yes there is a 'Find Duplicates Query'. It doesn't automaticly eliminate the duplicate - which I think you are looking for....but it might be something for you to consider to at least identify them upfront. Click Queries In the Objects bar displa the Queries List, and then click 'New' Click the 'Find Duplicates Query Wizard'....and go from there.... SignatureNTC > surely there's a way to delete all but 1 duplicate of each address from the > last query. [quoted text clipped - 16 lines] > > helpful...and for the query for labels it was of course very simple to > > require only "1" be returned.... yeah sure, and i could probably use that to remove all duplicates but i want to remove all but one. Perhaps i'm approaching this from the wrong side. what if i had a record per address with multiple names under each. is there a way to then go backwards from there to get individual names with addresses in seperate entries in a query? could i somehow use a subform maybe. i'm a bit over my head here and don't know if im digging my way out, or further in :S > yes there is a 'Find Duplicates Query'. > [quoted text clipped - 26 lines] > > > helpful...and for the query for labels it was of course very simple to > > > require only "1" be returned.... your thinking is correct in terms of normalized db theory. if there were lots of duplicates ; then in normal db organization, one never re-enters the same data twice. so you would enter full address once - but also assign a unique ID# to it. then you would have a second table that would be made up of nothing but ID#s (that points to the other table's full address) and names. however; if duplicates is more the exception than the rule....then you really might consider my first advice - rather than going down this road. Adding simply another column now and populating it with a 1 or 2 (occasionally maybe a 3) is pretty simple and makes for a very simple query method too.... as a db purist - it is not the perfect solution but in terms of pragmatics - it is feasible unless you existing db already has a great amount of records.... SignatureNTC > yeah sure, and i could probably use that to remove all duplicates but i want > to remove all but one. [quoted text clipped - 37 lines] > > > > helpful...and for the query for labels it was of course very simple to > > > > require only "1" be returned.... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.