Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Unanswered: How to remove spaces from query

    Hi

    Have this access DB that consists of fax number blacklist & uncleaned table.

    Problem is that some of the fax numbers have spaces, in both tables.

    When I run a query to compare blacklist fax table and uncleaned fax table, I am unsure whether all the fax numbers that have been chosen to opt out have been removed.

    e.g.

    Blacklist Table
    Name----------fax
    tim freight------02089888080

    uncleaned table
    Name----------fax
    tim freight Ltd--0208 988 8080

    -------- RUN QUERY--------

    Compare Table Query
    Name----------fax
    tim freight Ltd--0208 988 8080

    -----------------------------

    The query table should be blank
    Name----------fax

    Pls help

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Easy to remove spaces - however I would remove them from both tables in case there are examples the other way round.

    Code:
     SELECT REPLACE(MyPhoneNo, ' ', '') FROM MyTable
    I think this is an example where use of a surrogate key would be well advised (wouldn't you say Teddy? ). Create a tel numbers tables with an autonumber as the Primary Key and telephone number as an alternate key. Then put the primary key number in your other two tables.
    At the minimun, if you retain your current deisign, you should be enforcing referential integrity between your two tables to prevent this sort of mees. Easier to type than to impliment I admit however a bit of effort now will save you from this sort of problem again.

    How about a free link?

    HTH
    Last edited by pootle flump; 12-16-05 at 09:14. Reason: silly spelling error
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    At the minimun, if you retain your current deisign, you should be enforcing referential integrity between your two tables to prevent this sort of mees.
    Or even simpler - what about a NumberIsBlacklisted YesNo column in your first table (assuming there isn't lots of additional data associated with the blacklist and it is a 1:1 relationship)
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •