Modules & VBA :: Consolidate Addresses By Similarities?
Apr 9, 2015
I need to consolidate mailing addresses so that many records are grouped to one invoice based on the address they fall under. I am currently using an update query to change addresses that are similar to match exactly. Is there a way to make it so the addresses group together based on similarities rather than exact matches? For example I have two addresses:
1301 W Johnson Street Phoenix, Arizona 85725
1301 West Johnson St Phoenix, AZ 85725-7201
These are the same property however without cleaning them up manually to match, two invoices will be printed. Is there a way to consolidate these?
View Replies
ADVERTISEMENT
Jan 21, 2014
I have one Master database and multiple child databases in a folder. Each of these databases (both Parent and Child) have identical table called "source". I need a VBA code which will consolidate tables of child database into the Parent database table.
View 8 Replies
View Related
Oct 3, 2013
I have two tables in my Access database. Table 1 has three columns, First Name, Last Name and Phone Number. Table 2 has just 2 columns, First Name and Last Name. What I would like to do is write a VBA macro to consolidate Table 2 into Table 1 and insure any duplicate First Name-Last Name combination is removed.
Table1
Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333
Table2
Code:
F.Name L.Name
Albert Black
Larry Miller
John Bush
Table1 Post-Macro
Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333
Albert Black
John Bush
View 1 Replies
View Related
May 21, 2014
I am trying to populate multiple email addresses based on a table and a yes/no check box. If the checkbox is checked, they receive an email. I have a table with Employee/email combobox with the second column being the email address, and a checkbox.
View 14 Replies
View Related
Aug 4, 2013
I have a table, "TBL_Email", which simply contains a list of email addresses in a field called "email".
I would like to concatenate all of them together into one string, and add semi-colons to the end e.g.
"123@abc.com;456@def.com;789@hij.com"
I believe a may need a record set, but i'm not entirely sure how they work.
View 4 Replies
View Related
Nov 25, 2013
How I could initiate a search from MS Access on an Outlook folder for an email addresss.
I would like to to work just as if you were using the outlook 'Try searching in All Mail Items'
View 11 Replies
View Related
Jun 27, 2013
I am trying to loop through a query results to extract email addresses from a query result.
I have the following code
NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value
DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
[Code] .....
If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?
Someone on another post said that sql uses US date format so i tried converting to us date and still no results?
View 6 Replies
View Related
Jul 16, 2014
I have made a query with the name "Confirmation" and it is setup like this:
Name trainee Email Training
John John@mail.com Tr one
Mary Mary@mail.com Tr two
I also made a button in a report with the title "Send Mail" now is my goal that if i press that button automatically multiple e-mail message's will be generated with data from people in that query. So if click on that "Send Email" button i want two different mails messages generated that will be send to John@mail.com and Mary@mail.com with in the mail body their data.
View 13 Replies
View Related
Dec 26, 2006
In order to find the most recent piece of data for a particular customer I need to query my table in 3 iterations.
I have a TABLE with data in it pertaining to all customers.
QUERY 1 orders all the records in TABLE by date.
QUERY 2 plucks all records related to specified customer from QUERY 1.
QUERY 3 selects the TOP 1 from QUERY 2.
My main concern is that this all happens automatically. Can I shorten this process at all?
View 4 Replies
View Related
Jul 10, 2013
I have a database that, vaguely looks like the following
NAME Value Category A Value Category B Value Category C
Name A -50 0 0
Name A 0 50 0
Name B -100 0 0
Name B 0 75 10
Name B -25 0 0
Name C -100 0 0
Etc. etc.
What I'd like is to find a query that will first, generate a Category D which should be a total of all the values in the three different categories and consolidates the names to one line. So it should spit something like this out.
NAME Value Category A Value Category B Value Category C Total
Name A -50 50 0 0
Name B -125 75 10 -40
Name C -100 0 0 -100
After this, I'd like to have another query that will only display the values that are NOT zero.
NAME Val Cat A Val Cat B Val Cat C Total
Name B -125 75 10 -40
Name C -100 0 0 -100
View 3 Replies
View Related
Jun 11, 2013
Each month I get the previous months data. In that data, I have a list of names that have duplicates in them. Some of the names have last, first middle. Some have just last, first. I want to combine the like names and leave the unlike names not combined.I want to combine in this situation
ex. Smith, Peter
Smith, Peter W
I do not want to combine in this situation
ex. Smith, Peter M
Smith, Peter W
My goal is to get the original name in a table with the combined name next to it.
Original Name
New Name
Smith, Peter
Smith, Peter
Smith, Peter W
Smith, Peter
View 3 Replies
View Related
Nov 24, 2004
I am currently building a db that will handle information on many events around the world. What I would like to do is set a check box that when people scroll through the different records they could check it and it (the current record they are viewing) will go into a compiled list wherein the iindividual record details of that list which the user created by their choices could be exported to an excell sheet (to automate a reporting need in the job) I have seen functions like this in large websites particularly the classified job sections in the seattle Times . where as you scroll through you see something you like you mark it continue scrolling mark another and then when you are all done you go to a page that has all and only your selections.
Can anyone tell me where to start on this one what am I Looking for Etc.
This will be the next big step in this my first database.
All help greatly appreciated
View 1 Replies
View Related
Aug 3, 2015
I have an automated email system (Lotus Notes) in a Access database I have created and am looking for the code needed to add up to 3 c.c addresses into an email prior to sending automatically.
The code I have to send the email to the To: individual is all working but what code I need to add and where for the c.c addresses of which there could be 1, 2 or 3. The control for To address is EmailTo = CustCopyEmail The controls for the 3 c.c emails are cc_EmailAddress, cc_EmailAddress1 and cc_EmailAddress2.
Here's the code I have:
Private Sub SEND_CC_Click()
' Set up the objects required for Automation into Lotus Notes.
Dim Maildb As Object 'The mail database.
Dim Session As Object 'The Lotus Notes session.
Dim MailDoc As Object 'The mail document itself.
Dim UserName As String 'The current users Lotus Notes name.
[code]....
View 8 Replies
View Related
Aug 29, 2013
I have 3 tables of data, where I want to cross reference information to produce exception reports.
The tables all have Employee number held within them, and these form the basis of my queries.
We want to be able to cross reference locally held staffing information with centrally held HR and payroll records.
The type of information we want to be able to check, is if the hours of the individual are the same in all 3 records, the grade they are being paid for, the cost centre, and that the names match.
Currently we run separate queries for all of these, and I want to be able to consolidate the exceptions in one report, to save looking at the same persons record more than once.
For example, if John Smith has a different name, grade and cost centre are all different - we may be accessing his record 6 times, once for each variance against payroll and HR records.
Is there a way that I can pull all this information together to speed up the reconciliation process?
View 1 Replies
View Related
Apr 10, 2014
I have a few duplicate customer queries (different duplicate fields etc). I would like to construct a form to make it easy for an end user to remove the duplicates.
The main problems are...
1. the newer customer is to be deleted
2. any/all orders from the newer customer will be moved to the older customer before deletion.
3. preferably an easy way to switch between the duplicate queries in the same form, though if this is a problem I could just have one small dialog box/form, linking to 3 different duplicate query/forms.
I think other changes between the customers will be easy enough to just do in the subform, i.e update email address/tel/status/salesperson etc. These can just be edited directly?
But the transferring of orders can't be edited directly because there may be several orders and could be errors etc.
tblCustomers [CustomerNumber] primary key
tblCustomerOrders [OrderNumber] primary key
[CustomerNumber] linking to tblCustomers
So all that needs to be updated is [CustomerNumber] in tblCustomerOrders. All orders for the newer customer to have the CustomerNumber field updated to match the older customer. Then the newer customer is deleted.
I would just have a button with a message box to run the code, update CustomerNumber/s and delete record.
View 2 Replies
View Related
Oct 15, 2014
I have a table ("Student Info") that looks like this:
ID....Student.....Student #......Teacher Name......Teacher ID
1........A...............432...............Alice.. .................62
2........B...............674...............Alice.. .................62
3........C...............875...............Alice.. .................62
4........D...............934...............Katie.. ................18
5........E...............345...............Katie.. ................18
6........F...............134...............Dan.... .................4
7........G...............734...............Dan.... .................4
The above table suits me well for data entry.For analysis reasons, I want it to have another table or query or something (let's call it "Teacher Info") that looks like this:
Teacher ID....Teacher Name....St1.....St2......St3...St#1...St#2...St#3
62......................Alice...........A......... B........C.....432.....674.....875
18......................Katie...........D......... E........-.....934.....345......-
4........................Dan............F......... G........-.....134.....734......-
Is there any way that I could do this? I have been struggling with this for a while.Just so you know how this data is connected - At the moment, I have a form where I put in new Teacher information. I have a subform attached to that, where I put the student names and numbers. This way, the students are linked with their student #s, and each of them are linked to a teacher. Unfortunately, the table ("New Teacher Registration") that this creates looks like this:
id....Teacher Name
62...... Alice
(+ tab).........A.........432
...................B.........674
...................C.........875
18.......Katie
(+ tab).........D.........934
...................E.........345
4........Dan
(+ tab).........F.........134
...................G.........734
I created the "Student Info" table (above) from this to work with data entry (drop-downs and such). Now I'd like to create a "Teacher Info" table (above) for analysis.
View 11 Replies
View Related
Jun 11, 2013
Each month I get the previous months data. In that data, I have a list of names that have duplicates in them. Some of the names have last, first middle. Some have just last, first. I want to combine the like names and leave the unlike names not combined.
I want to combine in this situation
ex. Smith, Peter
Smith, Peter W
I do not want to combine in this situation
ex. Smith, Peter M
Smith, Peter W
My goal is to get the original name in a table with the combined name next to it.
Original NameNew Name
Smith, PeterSmith, Peter
Smith, Peter WSmith, Peter
View 2 Replies
View Related
Sep 6, 2005
we want to run a ping command via a radio button or command button. We have IP addresses set up for multiple pieces of equipment, each IP address consisting of 4 different text boxes. We want to be able to launch a ping and have the program read the values from each of the 4 text boxes that make up the IP address.
Any ideas/suggestions greatly appreciated.
View 14 Replies
View Related
Feb 28, 2007
How can I sort string addresses so as to get the correct number order? I know that strings sort on each character, which causes the problem. I have tried some things (with VAL and Len), but was not completely successful. I do not want to enter an address number as 0630, when I want 630.
Why does this not work:IIf(Len(Str(Val([address]))=3),"0" & [address],[address])? Address has 3 and 4 numbers only.
View 2 Replies
View Related
Apr 11, 2005
I am currently designing a contacts database for my business and need some advice on how to store addresses.
As I see it, I can either use a single field and carriage returns, or multiple fields which for some records may leave some blank fields.
The data will be used to perform mailmerges and will also be output to a contacts directory and I would be looking to display the address both horizontally and vertically. Is there a simple way of manipulating a multi line field so that it can be displayed on a form across the page on one line in a report?
View 2 Replies
View Related
Jun 10, 2005
Hi.
I have an Access 2000 database running on Windows 2000 Operating System. The database is on a shared drive on a network with approx 20 concurrent users. It runs a bit sluggishly so I am looking for ways to speed things up. The network guys say that the bit rate is OK so I need to tweak the database. I have run the performance and table analysers and get no suggestions for improvement. However, I have spotted a potential problem with the way addresses are stored.
Currently addresses are stored in the person table along with personal stuff like date of birth, gender, ethnicity etc. the address part has six fields:
Oh by the way - this is for the UK so the address structure has to be a bit flexible to accommodate quite a few variations of how addresses can be written but a reasonable guide is:
Number (or house name) and street name
(often a second line for part one of address)
Village / urban area
Town
County
Postcode
Part one of my question is - is this the best way to store this information or should I just gather it like this on a form and then store it as concatenated text in a single field.
I have tried this on a small test database and it works OK and prints address labels etc. - - But is it better or not?
Part two is - should the addresses be stored in the person table at all or should it be in a separate table. This is the bit I cannot get my head round. If it is in a separate table then each record in the table needs to be unique but because of the nature of the client base (i.e. students) many students share accommodation - OK a 1 to many relationship - but what about students in halls of residence they will have an individual room but the remainder of the address is the same. So for say 1000 students in the same hall 95% of the address is repeated although each address is unique overall. And with 4 halls there are 4000 entries that could potentially be reduced to 4 plus a room number and hall name, but I am not at all sure how to achieve this. And then what about addresses for students not in halls but shared houses? I cannot get my head round this. I can see why it was set up the way it was but there must be a better way.
Do I need one table, two tables or six tables (one for each field)?
And how do I then enter data? Do I have to have combo boxes on each line to see if that value has been entered before?
The data gathered would always be entered via forms so I can get the fields together but I don't know how many tables I need nor how best to relate them.
Can anyone suggest how I might get round this problem?
Thanks... Sprocket
View 4 Replies
View Related
Sep 13, 2005
Hi,
I have a field in one of my tables that contains IP Addresses. These addresses can look something like 142.67.214.73/24 or 192.168.196.0. I would like create a calculated field so that ALL IP addresses just show the first 3 octet's. So the first one would just show 142.67.214 and the second one just 192.168.196
Any help would be great .......
Thanks !
View 2 Replies
View Related
Jun 12, 2006
Simple query from an amateur. I have a list of people in a table, each with thier email address. How can I convert this into a list of addresses to paste into a group email? (Ideally with ";" as a separator between addresses)
View 4 Replies
View Related
Feb 6, 2008
Hello all I am using MS access for a little personal project and I have house number and streets along with a day they are serviced.
example:
unit road type day
1 main st friday
2 main st friday
3 main st friday
4 main st friday
5 main st friday
6 main st friday
7 main st friday
13 turtle st monday
15 turtle st monday
16 turtle st monday
I want to make a report of this data to look like this
unit road type day
1-7 main st friday
13-16 tutle st monday
I figure I have to do some sort of query before I can make the report im jsut not sure how to seperate it or what symbols to use. I know this is faily simple I just can not get the computer to give me what I want....
ok I think i figured it out what I did was take unit and I put it in 2 times one fo min and one for max and then I grouped the road by ascending and it worked out
View 1 Replies
View Related
Sep 28, 2007
Hello everyone. I hope you are well. I am new to this forum and need some help with access.
I have a database which records an organisations addresses. There is the main address, where the activity takes place, and then there is the postal address, which is often different. What I want to be able to do is to select all the organisations in one town (e.g. Town1), but send information in the post to their postal address (Town2).
Is this something that can easily be done? I hope somebody can help me (especially with some user friendly terms).
Many thanks,
Chadder
View 10 Replies
View Related
Dec 14, 2006
Please please please!
It's driving me crazy!
Is it possible to send e-mails to all of your e-mail addresses in one single e-mail?
Right now I am able to open up an individual e-mails for each of my stored e-mailo adresses but it would be so much better (and more user friendly) if I could just have one e-mail with all addresses in the To: field.
Any help would be really appreciated.
Rob
View 3 Replies
View Related
