Databse Help Required

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rishiratta

Thread Starter
Joined
Oct 6, 2003
Messages
6
I HAVE TWO FIELDS CONTAING LIST OF EMAIL IDS IN A TABLE UNDER A DATABASE IN MS ACCESS. THE FIELD 1 CONTAINS ABOUT 20000 DIFFERENT EMAIL IDS AND FIELD 2 CONTAINS ABOUT 5000 EMAIL IDS.

THE EMAIL IDS THAT ARE THERE IN FIELD 2 ARE ALSO PRESENT IN FIELD 1. WHAT I WANT IS THAT ALL THE EMAIL IDS THAT ARE IN FIELD 2 GET DELETED FROM FIELD 1 SO THAT FIELD 1 DOES NOT CONTAIN ANY EMAIL ID THAT IS THERE IN FIELD 2.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
ALL CAPS IS CONSIDERED "YELLING" ON THE INTERNET. Please don't use all caps. Wish I could help with your question, but have not yet had my coffee. :)
 
Joined
Jul 26, 2003
Messages
2,431
hi rishiratta....

A quick and dirty way to do this is as follows:

Make a copy of the table.
Delete field 1 from the copied table.
Alter the field 2 name of the copied table so that it is called field 1 (or whatever field 1 is actually called in the original table).
Create a Delete Query and add both tables to the query.
Drag the * from the original table into the Field section.
Drag field 2 (or whatever it is called) into the Field section to the right of it.
Right-click in the gray section where the tables are, and select Properties.
Change "Unique Records" so that it says YES.
Run the query.
It should delete all dupes.

I'd suggest making a copy of the database before trying this (in case you do it wrong, or in case I didn't explain it well enough).

There may be a "proper" (or easier?) way to do this....
but I'm self-taught and this method has always worked for me.
Let me know how it goes for you :)
 
Joined
Jul 17, 2003
Messages
53
Sorry, but could you please be more specific with your subject line? I find caps a little distracting to read as well.

I'm not sure how easy it would be with a large db, but you could...

Create a new query
Select the table
Select the first column and second column
Run
Go back to Design view
select all the data in your second column
copy the selection
go to the end of the first column
Go to edit in the menu
Select Paste Append (Now all the data is in the first column, also not sure if you have to go to the end)
Turn on the Group By... (The symbol that looks like a summation sign)
Make sure Group By is selected for the first column
Turn off or delete Column 2 selection
Turn on make-table query...
Run...


Um... I just realized this, but I am assuming both fields are in the same table. And that you wanted them to be in the same column.
 
Joined
Feb 12, 2001
Messages
3,420
Sorry to jump on the band wagon, but it sounds like bad design in the first place. You don't go into enough detail, so it appears that you maybe have a field for work and home email address and then maybe a field that says which is prefered. I would suggest that you make the email addresses a new table so that your structure looked something like:

tblClients
ClientID <--- Unique Key
Lname
Fname
Address1
Address2
City
State
Zip
Phone

tblClientEmails
ClientID <--- Foreign Key to tblClient
EAddress
Location (Home, Work, ETC)
Prefered

This way, a client could have multiple email addresses, and you could easily print a list of all the addresses. If you do it your way, you are going to have to query off both columns to get a list of addresses.

I may be wrong, but then again, you didn't give much information. I usually charge $100 per hour to be a mind reader, but I didn't think you wanted to pay that...

;)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top