Compare Addresses

Please Help.  :-)

I have two tables that have two fields of addresses. I want to create a query
that can campare the first 5 charecters of these fields only.  For example,
1223 Back Street   and 1223 Back St. shouldn't be executed because they are
the same. I don't want to go through my table and replace Street with St.

Table 1 
-----------
Name
Address1

Table2
---------
Name
Address2

Thank you in advance for your help!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200703/1

0
qkimchi
3/30/2007 12:06:42 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1435 Views

Similar Articles

[PageSpeed] 53

Create a query with the 2 tables, and no join line between them.

In the Field row, enter:
    Left([Table1].[Address1],5)
In the Criteria row under this, enter:
    Left([Table2].[Address2],5)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"qkimchi via AccessMonster.com" <u15272@uwe> wrote in message
news:6fed980ffb7ba@uwe...
> Please Help.  :-)
>
> I have two tables that have two fields of addresses. I want to create a 
> query
> that can campare the first 5 charecters of these fields only.  For 
> example,
> 1223 Back Street   and 1223 Back St. shouldn't be executed because they 
> are
> the same. I don't want to go through my table and replace Street with St.
>
> Table 1
> -----------
> Name
> Address1
>
> Table2
> ---------
> Name
> Address2

0
Allen
3/30/2007 1:58:01 AM
Reply:

Similar Artilces:

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

Redirecting mail based on sender address
Is it possible to redirect incoming mail based on the senders email address ? Eg if an email arrives in exchange for 123@abc.com from 456@xyz.com can exchange see that the sender is 456@xyz.com and redirect it to say 789@abc.com ? I know i could do this through a rule in Outlook but i want to be able to do it through exchange as the recipient address may not be a valid user. Thanks in advance, Steve ...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Change incoming email "From" address for contacts
Hello, Is it possible to change the "From" address field for incoming emails based on the local AD? In other words change the display name from remote Exchange 2003 AD based address to reflect the display name in local AD? Example: From: ABCSupport@xyz.com "ABC Support" To: recipient@ABC.com Could we change it from "ABC Support" to something more locally coherent like "Support at XYZ"? In this case we have field engineers who use the company name as their email address. When recipient@ABCSupport receives the email it is confusing, as the email add...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

How to stop e-mail address from becoming a link?
Whenever i add an e-mail address to a doc, it is underlined, turns blue and becomes a link. How can I avoid this? If you never want the automatic conversion, clear the "Internet and network paths with hyperlinks" option on the AutoFormat As You Type tab of the AutoCorrect dialog box. On the other hand, if you occasionally want to suppress the conversion, Undo immediately after Word has created the link. -- Stefan Blom Microsoft Word MVP (Message posted via NNTP) "Joseph S. Bonin" <Joseph S. Bonin@discussions.microsoft.com> wrote in message ...

How do I choose which email address I send from???
I reinstalled Outlook 2000 after a virus and in the old one, (same version/install disc) when I created an email message, the send button was a drop down menu with a selection of email addresses to "send from" Now I can only send emails from the one address and everyone replies to my dial up address, when I need them to be replying to the correct address. How do I get this feature back??? You don't say why you cannot use this feature? Are you saying the dropdown is no longer there or it doesn't work? If the button is missing you can add it again - use the Customise ...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

Outlook Client Address Book not renamed
I've renamed the entity 'Account' to 'Company' in CRM 3.0, but the address book in Outlook is still called 'CRM Accounts' - is there anyway of changing this? thanks in advance. The custom entity name will not display automatically in the ABP. Thanks for your suggestion. We will consider correcting this issue in a future service pack or release. An Issue has been entered into our tracking data base. -- Jason -- This posting is provided "AS IS" with no warranties, and confers no rights. "hb" <hb@discussions.microsoft.com> wrote i...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

Changing Outlook address
Hi, I get my email forwarded from my website to Outlook. When I reply from Outlook the address shows my computer address. Is there anyway I can configure Outlook so that the address shows my website address? ...

comparing cell
i have a spreadsheet that contain a column of name (some the same and some not) and would like to place each name in a row acroos the top of the same spreadsheet. Smith Jones Brown Smith Smith Brown Smith Jones Jones Smith Brown Hi Stanley First insert a heading above your column of names. Then Data>Filter>Advanced Filter mark the source as your column of names. Choose copy to another location and choose a detination cell on the sheet. Select Unique values only. Having got a unique list of names, copy this list and Paste Special>Transpo...

Compare and delete row
I need some help - I need to compare a cell, let's say A1, that wil contain a date. I need Excel to compare it to today's date, if it i over 60 days, I need it to go ahead and delete that row. If not, I nee it to leave alone. Thanks in advanc -- Message posted from http://www.ExcelForum.com Try this Sub test() If IsDate(Range("a1")) = True Then If Range("a1").Value > Date + 60 Then Range("a1").EntireRow.Delete End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "intheway >" <<intheway.176s7x@excelforum-nospam.c...

compare side by side
Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. When I choose the "compare side by side" option, my two workbooks appear one above the other rather than side by side. Am I missing something?? >>Am I missing something?? I commented on that to MS during the beta test. They ignored me! -- Jim "BK" <nospam@nospam.com> wrote in message news:%233jhCdasHHA.1216@TK2MSFTNGP04.phx.gbl... | Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. | | When I choose the "compare side by side" option, my two workbooks appear ...

Macro to send grab email address and open Outlook
Hi there I am trying to write a simple macro to be able to send an email based on an emai address in a form. tblAgency Details has a field EmailAddress (data type is text) I have a Command Button on a form frmAdd Agency Details to run a macro. Macro name is SendEmail - Action is only SendObject There are only two items in the Action Arguments To: =[EmailAddress] Edit Message: Yes (I only want Outlook to load with the email address) So far it does not work. When I have a field for a Web Address and the data type is a hyperlink that works well for me....

Global Address List #3
We have two Windows 2000 AD domains, each with their own Exchange 2000 server. We would like to be able to share the Global Address Lists between the two domains. What is the easiest way to do this Thanks! ...

compare time
I would like to compare a calculated time in my work sheet vs. the actual system time. For example, if the caculated time is >than or = to the system time I want excel to tell me to "CALL", if not then "REST"...any help would be appreciated. Thanks, DE Put your calculated time in a cell (say A1) In B1 put =NOW() in C1 enter =IF(A1>=B1,"CALL","REST) -- Gary''s Student "Eddie Munster" wrote: > I would like to compare a calculated time in my work sheet vs. the actual > system time. For example, if the caculated time is ...

Global Address List Issues.
I have a question pertaining to the global address list. Some computers I am using have Office 2000, and some have Office 2003. I would like the global address list to be the default contacts list that comes up when Outlook is opened. I would like the different address lists to be listed on the left side of the screen so that I can toggle between the different lists. Anyone know how to do this? Any and all help would be greatly appreciated. Thanks. Daryl akagod Not possible. The Global Address List is not an Outlook contacts list. It is an address list. herefore, it can only appe...

how to create the postmaster@[IP Address] email address in exchange server 2003 ?
Hi, I try to create the postmaster@[ip address] email, but exchange server 2003 doesn't allow me to put the bracket in the address. so I can create this email address : postmaster@111.222.333.444, without any bracket. how to resolve the problem ? thanks. Jerome. ok I've found this article: http://www.msexchange.org/articles/Exchange_2000_PostmasterIP_And_AbuseIP_Mailboxes.html and this works also for exchange server 2003 "J�j�" <willgart@_A_hAotmail_A_.com> a �crit dans le message de news:OKR9YqN5DHA.2432@TK2MSFTNGP10.phx.gbl... > Hi, > > I try to crea...

Global Address List Sort
I have taken over a network and have an Exchange 2003 server. The admin before I has somehow sorted the Global Address book with he Groups at the top, but any new group I add just go into alphabetical order with the other users. I have been searching around and can find nothing about this. All the users here are use to this and would like to continue with it. Any help would be great. On 8 Dec 2006 07:24:24 -0800, "Coleen" <coleen33@hotmail.com> wrote: >I have taken over a network and have an Exchange 2003 server. > >The admin before I has somehow sorted the Globa...

How do I re=send an e-mail to the same address?
I have an e-mail ,currently in 'sent items', which had an error message during the original 'send'. I want now to re-send this e-mail without having to re-type it all. I cannot see how to get it into a state for re-sending open it in the sent items folder, click options, resend it. -- Regards Judy Gleeson MVP Outlook in Canberra, Australia .. "Bodicote" <Bodicote@discussions.microsoft.com> wrote in message news:34FB0534-D25D-4D71-8994-A8FEB33A88D2@microsoft.com... >I have an e-mail ,currently in 'sent items', which had an...

Changing ip address of exchange server #2
Hi, I have a back-end server and a smtp server in DMZ. I want to change ip address of back-end server. are there any issues? all incoming and outgoing emails are going via smart host. Hi, No issues at all as long as you remember to change all the references to this server in your firewall, SMTP scanner etc. Leif "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:FE5927A1-D20D-4C6B-991F-2E1EFD19434D@microsoft.com... > Hi, > > I have a back-end server and a smtp server in DMZ. > > I want to change ip address of back-end server. are ...

Comparing Excel Spreadsheets
Ok, got one I can't handle.. I have one of two scenarios. Either I have 2 Excel workbooks, or 1 workbook with 2 worksheets.. Either way, I have 2 one Column data sets. I need a way to look at the data sets and find what is missing Example Data 1 = 1 2 3 4 5 Data 2 = 1 3 5 6 Results = 2 4 6 Does anyone know how to do this? Thanks Joe ...

Vba Cell Address Question
I am attempting to direct a macro to copy data. It does not like my attempt to define where the cell is. That is not a hard address. How do you use a calculation from another cell as an address. VBA compiler does not like ADDRESS(8,D5) or CELL(8,D5) or $D5. What is the correct way to set an indexed cell address? Thanks, Michael -- MJSlattery ------------------------------------------------------------------------ MJSlattery's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16141 View this thread: http://www.excelforum.com/showthread.php?threadid=275969 ...