1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Databse Help Required

Discussion in 'Business Applications' started by rishiratta, Oct 6, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. rishiratta

    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.
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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. :)
     
  3. khaki

    khaki

    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 :)
     
  4. GillIT

    GillIT

    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.
     
  5. THoey

    THoey

    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...

    ;)
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    ROFL, Terry! I got a C-note! Read my mind right now!!
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/169855

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice