Tables :: Split Fields And Look For Updated Match?
Apr 20, 2013
I have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:
Street Number
Street Name
Street Type
Street Direction
And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.
I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.
But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.
There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.
This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.
I need to match the data in all the fields between 2 tables.
eg. Field 1 (Table 1) = Field 2 (Table 2) Field 2 (Table 1) = Field 2 (Table 2) so on......
Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?
I have a database that is split - one central PC holds the back end, and then there are multiple end users with a copy each of the front end.We have updated a drop down menu on a field in the back end to add some new entries, but this is not showing up on the front end copies.Is there an easy way of updating the front end to show the changes?
I have a database that has 5 linked excel files. From there I have a form that runs many different queries. I have a macro that updates the queries by running them and closing them. Occasionally I update the excel files and was wondering if there is a way to show which records were added to the linked excel files. In other words, I need to display the new records that were updated. Does anyone have any solutions? Thank you for your help in advance.
I have created a database with other to log my clubs sports score by player etc. what I want to do is to create and email form that I can email out so other club captains can send me their scores and info to add to my database.
I do not want to use excel, I just want to know if this is possible using out look to send the email.
There are multiple tables that will need updating. Via the email form. Basicaly the main database entry form i need to create a query or vba code to allow all the fields in that form to be updated via email.
I am exporting an Oracle report to excel, and using an Access macro to import into a table. The records contain some duplicate info that I do want to capture, but I don't want to import the same records. (I import the file on one day, and someone comes in and imports the same file later). This would work if I could get the table to not duplicate if three of the fields are the same as an existing record. I tried using the index, but it reconizes any/all of the fields that duplicate. Any help would be appreciated.
I am trying to setup a table that tracks the progress of an item from a cradle to grave process with 26 steps in between. I would like a calculated status column that is calculated by finding the max value (Date) in a row and returning the "header" that is associated with the max value.
I now want to update one field (what I type into it is irrelevant) but if this field is updated, it means quite a few others become Not Applicable and I would like that to be populated once I updated this one field. After Update is what I am looking for.I know it is simple (but will mean entering each field name) but it would save me truckloads of time.
how can I get dates to show in last updated and last viewed fields..I did get last updated working but for some reason when I tried to do last viewed, last updated vanished :/
I did try writing code (but im a novice lol, I think that's why last updated vanished).I have got 2 unbound fields called HiddenLastViewed and HiddenLastUpdated thought I might need them.
I'm mid build on a commitments tracking (pseudo Purchase Order system) project, but seem to have run into an issue with a loop I'm building to allow users to edit commitments. The idea is that an edit form is launched, values amended and the submit button is clicked. A VB subroutine then validates the entries to ensure that the mandatory entries are included. It then writes a copy of the original values to the Archive table, before attempting to update the existing commitment with the new values..
This is working perfectly, apart from the fact that 3 fields that are "updated" are being updated to a blank value..
Code: Private Sub CommitSubmit_Click() Dim SQLStr, LastID, DOwner, DHeading As String Dim ErrState, Dtype, DProperty, DTCA, DITD, DSD, DED, DSP, DRetention, DRA, DRPD, DSupplier, DDOW, DStatus, DUser, DShD As String Dim Authcheck, Complete, ErrMsg As Boolean Dim QDF1, QDF2 As QueryDef
I currently have a query that uses a function to get the current users email address. This is matched with a "Teacher Email" address in a table of students to only show students which match the logged in teacher. I'd like to modify this so the teacher can see any student in their department. Is there a way to do this without creating a separate "Teachers" table that would contain a department and email?
I have 5 tables which comes from different departments in our company. All of them have the same key "project no" but hold different information in other colums. As a starting point I have secured that they all have same no. of records meaning all "project no" are in all tables but some table might not contain other information in that record than the project no. I have put them into a quirie and on a form I am able to look at all data from all 5 tables. I have linked them one to one and it works fine untill I add a record to one of the tables. Afterwards I am not able to write into fields from other tables because there is no automatic creation of the record in the other tables. Please How would this be best/easiest to make for me. ? The reason why I have to keep the 5 tables is that regular updates are avaiable form any of them.
Appricate any comments which can make me any progress.
The database I am working on, I split a while ago to give it some security. Now i'm updating a related form, and i'm finding that if I delete and add fields in the BE, the FE fields (being the fields that I need to insert into the form so the data entered propogates to the DB) are not updated.
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2 apple a apple b banana a carrot a carrot b dog b elephant b
I would like my query to now display a third field and group field :
field1 field3 apple both banana a carrot both dog b elephant b
I would likw for my first and last name fields width to match the length of the name. So if the last name is Smith then the field width will resize itself to fit perfect fit the name. Then if I have a last name of Connor then it will grow to fit it perfect. I dont need this but it will be nice so everything looks a bit nicer an cleaner. I thought the following would have worked:
I've been making small changes here and there to my database in Access 2013. I have left the original database in place and it has acquired many new records from the last month. I'm ready to start using the newly updated design version of the database, but I do not want to have to type all those new records into the new database to get it up-to-date. There has to be a way to import the data from the original database to the newly updated one.
Most, If not all of the fields have the same names, but some of the fields have changed, like to Combo boxes. I removed two or three unneeded fields, and added two or three. I understand the new fields will need to have data enter to them, but the remaining would be redundant and inefficient if I have to reenter all the recent records again.
How do I import the data from the original two tables to the new updated database? I only have two tables and they use a One-to-One relationship in both databases. The table's names are different, but as I mentioned earlier, the fields are mostly the same. Do I need to import into Excel, and then modify the data slightly, and then import into my new database?
I recently got a new job and am trying to learn access for it. I have two tables. Both of them have client id numbers. They are both supposed to have the same client id numbers. However, Table 1 has more client id numbers than Table 2. I want to do a match query that selects the client id numbers in Table 1 that do not have a corresponding match in Table 2. How would I go about doing this?
Also, I want to do a simple select query where I select the client id numbers in Table 1 whose first two numbers are "88." How would I do this in the query or SQL form.
Do you have any recommendations about the best way to learn Access for practical applications like this? I'll also need to get good at making Forms which seem fairly complicated.
Table 1 has National Insurance Number, first name, last name, phone no, address. Table 2: has National Insurance Number and email address.
Table 1 is the master table where it will link to table 2 containing the email addresses of the individuals.
However, there will be a lot of email addresses in table 2 which do not relate to any record in table 1 because I do not have the individual in my database yet.
BUT I want to keep their email address because in the future this person may enlist in my imaginary business and therefore - if they do I would have their email address!
1. Is this possible in Access?
In Excel, it would be the case of a simple vlookup from the email address field into another sheet containing the National Insurance Number and email address.
2. Would it be possible to establish a one-one relationship while enforcing referential integrity?
3. Is there a way I can establish a lookup which can be built into table 1 which can lookup the email address of a person in table 2, matching on National Insurance Numbers?
So in the future if new data is input into table 1 or 2 which results in a match of National Insurance ID numbers, the outcome would mean an email address is now paired to the corresponding individual it belongs to.
I have two access tables named as "DestructionTBL" and "AnnutiesTBL". Now I have to check each PolicyNumber of AnnutiesTBL with PolicyNumbers in "DestructionTBL". If match is found then add that PolicyNumber into "NODestructionTBL" and delete that PolicyNumber record from "DestructionTBL".
I Have made a change to a field in my tables. it was was based on ethnic background and originally i had just created the field but had not added in the options ( via adding it into the row sources).
So now the tables field have been updated but unfortunately on the form it has not updated into the dropdown i had created containing the options..
What I would like to do is for the BoatReg field in the Quotes Table to only display the boats that have been registered to the Client that has been selected, currently it displays all the BoatRegs.
I am trying to trap the database error "View cannot be updated because modification affects multiple base tables" in the Form OnError event to display a user-friendly message instead of the above.The above can happen in the scenario of inserting/updating several fields of different tables at one time, likewise what I am facing now is the scenario of the user copying an entire record and pasting it.
I tried Case 4405 MsgBox "Can not update the record. it is related to base tables" Response = acDataErrContinue
But it doesn't work. How can I know the case number and solve all the scenarios of multiple insert/update attempts?Also, are there any better solution than this? I first tried to detach the SQL statements out of the views and make it one select statement so that I can normally multiple update/insert(as this problem happens with views only) but since CTE is not supported in Access , I failed to do so.
Very new to access, I need an easy way to compare 2 tables with a common field (the name field) and list just the records that appear in the second table but not the first (primary) table.
