Remove Data From Fields
Feb 14, 2008
Hi, I am new to access. My father likes to gamble and has lists of horses he likes to check to see if they are running. He is currently using Microsoft Word and has to check each one by one. I thought if I could get the lists in to Access then it would be a lot easier. I have a field called 'Name' where I am going to put in the horses. The problem is many of the horses in his list have the country next to their name in brackets. I want to remove the brackets and their contents automatically. So if I enter 'horse (GBR)' then I want only 'horse' to be entered. How would I go about doing this. I have brackets in other fields so want this limited to the 'Name' field only. Is this possible?
Thanks :)
View Replies
ADVERTISEMENT
Aug 6, 2014
I have a large application that was built 8 years ago and the user now wants to remove some of the data fields. Is there an efficient way to figure out what objects would be affected if we delete these columns?
View 3 Replies
View Related
Jul 31, 2007
Hi everyone,
I am working on a database to manage newsletter subscriptions. Each subscriber record has the option of having up to four email addresses registered to his/her name.
Is there a way to check for duplicate email addresses in the entire database? It would have to compare all values in all four fields of all records.
Any ideas on how to implement such a thing? I'm clueless...
Thanks!
View 2 Replies
View Related
Oct 2, 2007
Greetings to all,I have duplicate records in my table (i.e. there are two or more records that are identical, and I only want to keep one of them). Is there another way to delete the duplicate records other than one at a time? Assigning primary keys and appending only unique records to a new table is not an option because in some instances, I want to maintain duplicate values within a field (i.e. the desire to delete is based on more than one field)Any help would be greatly appreciatedCheers, Lori (An Access amateur)
View 9 Replies
View Related
Aug 9, 2013
We use Access to pull data from data warehouse and this year they upgraded the new system on the back end and moved old data to the new one. Problem is that in some tables having big fields such as Asset Long Description, Work Long Description and when moved to the new database system, somehow there are certain some XML tags such as <br> </br, etc.. added into the description. So when we pull the data onto the table in Access, is there a way to remove those tags out of the descriptions fields? a macro or module?
View 1 Replies
View Related
Jan 11, 2005
I have a query which contains about 19 fields in it and some of the fields contain parameters. What I'm trying to do is to run the query using the parameters I've set, but at the same time remove fields that are empty (therefore only fields that are populated will be shown). I've tried using the 'is not null' parameter but it seems to interfere with the other parameters that I've set and as a result the query doesn't show any information. Also the field containing the 'is not null' parameter is still being shown. I'm not really sure what else to try!! Any suggestions would be most appreciated!!
View 14 Replies
View Related
Nov 19, 2013
I have a large table with many fields and many rows. There is no primary key. I'll call one field ParentPN, and another field ChildPN. There are many other fields as well. I want to identify all rows where BOTH the ParentPN and ChildPN occur more than once. I know how to create a query to identify duplicates of ONE field in the table, but not two. I can solve this with VBA: I will read the two fields of interest in the first row, then compare both values with every other row. If it finds another row with BOTH ParentPN and ChildPN identical with the first, that's a "hit". Then, repeat with all the other rows. I could find ways to make this run faster, but I was wondering if there are any build in functions to accomplish this. I looked at the Find Duplicates query builder, and all I see is I can select ONE field to search for dupes, not two.
View 14 Replies
View Related
Sep 28, 2015
I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.
i.e.
I have a
Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation.....
Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation.....
Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation.....
Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....
-A person can be within one of these tables more than once but with all the same information.
-A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.
Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers)
Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?
View 9 Replies
View Related
Jan 20, 2012
I want to use one database to remove data from another e.g. use a list of telephone numbers to remove remove any entry with one of those telephone numbers in another database.
View 8 Replies
View Related
May 22, 2007
I trying to figure out how to remove the last 3 Characters from my data
Such as
MA-D-97
MA-09
UJS-KK-OL-20
Like the data to look like
MA-D
MA
UJS-KK-OL
I Have This so far
Right([TYPEFY],3)
that selects the last three now is there a remove function I can use??
Remove(Right([TYPEFY],3) ???
Need help thanks
View 4 Replies
View Related
Jul 27, 2014
I copy some information from a website and then paste/values into Excel. I noticed that some non-printing characters are copied to. Suppose I were to import this Excel spreadsheet into an Access table. Now I need to clean up/remove those non-printing characters. What is the best way to do this in Access?
I suppose I could set up a calculated column with =SUBSTITUTE([InvHours],char(160),"") as the formula, where InvHours is the field name containing the non-printing characters. But if I did this, then I would still retain the original field with the non-printing characters which would increase the size of the database. I don't think I could delete it because then the calculated column wouldn't work. So I would rather not pursue this option if possible.
Would it be possible to somehow run a macro that could automatically be triggered as soon as data is imported, and that macro would clip out the non-printing characters without having to copy anything to blank columns?
Or does Access have some built-in feature that could do this?
View 3 Replies
View Related
Mar 10, 2015
bob@smith.com, 2010-01-10
bob@smith.com, 2010-02-10
bob@smith.com, 2010-03-10
bob@smith.com, 2010-04-10
ronda@q.com, 1984-01-02
ronda@q.com, 1990-04-02
ronda@q.com, 1996-08-02
want above data to appear as
bob@smith.com, 2010-01-10, 2010-02-10, 2010-03-10, 2010-03-10
ronda@q.com, 1989-01-02, 2010-04-10, 1984-01-02, 1990-04-02, 1996-08-02
View 1 Replies
View Related
Dec 18, 2013
I have some data in an array that I need to normalize, remove duplicates, and import.
Original Table
Every record in the array has a person, all but a few have an address, most have a phone, and some have an email.
Person----- Address ----- Phone ----- Email
Tom ----- 10 A Ln ----- 789... ----- e@a
Sue ----- 20 B Ln ----- 256... ----- _____
Sam ----- 30 C Ln ----- _____ ----- _____
Dan ----- 40 D Ln ----- 478... ----- _____
Jan ----- 40 D Ln ----- 567... ----- e@d
Stu ----- 50 E Ln ----- _____ ----- _____
Syd ----- ______ ------ 224... ----- _____
New Data Structure
I want to group the data by HouseHold; which Address will serve to define for this import.
tblHouseHold
hhID
tblAddress
adrID, hhID, Address
tblPerson
prsID, hhID, Person
tblPhone
phnID, hhID, Phone
tblEmail
emlID, hhID, Email
I've been working on a procedure to step through the recordset and add the data one record at a time so I can get rid of the duplicates.
I've tried a few approaches, but this is where I'm at now.
Code:
Dim rs As DAO.Recordset
Dim rsHH As DAO.Recordset
Dim rsPhone As DAO.Recordset
Dim rsEmail As DAO.Recordset
Dim rsAddress As DAO.Recordset
Dim rsPerson As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
[Code] .....
View 2 Replies
View Related
Sep 11, 2013
I have a report which contains 3 subreports. Now I require to do the following:
1. Hide the subreport if there is no data.
2. Remove the Blank space created when the data in subreport is null.
I tried to Set Can Grow and Can Shrink to Yes but still the blank space was not removed.
When I reduced the height of the subreports in the design view the blank space was reduced but it was still there.
What can be done so that the complete report auto adjusts itself if any sub-report is null? Something like the whole page auto fits itself in the available space?
The option of reducing the size of sub report in design view to minimum is there but it makes the report very un-handy for future reference.
View 3 Replies
View Related
Apr 26, 2013
I'm having difficulty with the syntax in this query to remove duplicate data for the field "StocktransID".
Code:
SELECT DISTINCT tblStockTrans.StockTransID, tblItem.Brand, tblItem.Category, tblItem.SubCategory,
tblItem.Model, tblItem.Description, IIf(TransTypeID=3,Quantity*-1,Quantity) AS Qty,
tblTransaction.TranstypeID, tblItem.ItemID, tblTransaction.TransactionID, tblItem.ItemType,
tblItem.Origin, tblOption.ParentID
[code]...
View 6 Replies
View Related
Sep 18, 2014
I have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.
View 1 Replies
View Related
Jan 19, 2015
When I copy data from Outlook or MS Word that contains bullets or other HTML formatted text, into MS Access text control, the HTML tags are displayed in the tables.
The memo field in the table is set to Rich Text and so is the text control on the form.
Below is an example of the data I'm copying from Outlook email:
Fire Alarm Activation
Actual/Smoke Present
False
Planned Drill/Testing
Evacuation
2. Utilities/Power/Communication Failure
Now, below is what it looks like in my table or output in a report:
<ol>
<ol>
<li><strong>Fire Alarm Activation </strong></li>
</ol>
</ol>
<ul>
[Code] ....
How can I copy and paste and remove the html tags?
View 1 Replies
View Related
Apr 13, 2008
I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
View 7 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
Nov 18, 2013
Basically in my order details table i have the following fields
Product
Unit
Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
View 4 Replies
View Related
Aug 27, 2013
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
View 2 Replies
View Related
Apr 1, 2014
Is there a way to remove data from one or multiple fields if the value of a separate field IsNotNull?
View 2 Replies
View Related
Dec 19, 2006
Hello Tech gurus,
I have a database with existing data, that is not normalized, :eek: and all the data is in one field... This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY on the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help... :confused:
Nana :D
View 10 Replies
View Related
Dec 19, 2006
Hello Tech gurus,
I have a database with existing data, that is not normalized, and all the data is in one field...:eek: This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY one the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help... :confused:
Nana :D
View 12 Replies
View Related
Apr 14, 2007
Hello there, I'm having a problem very much like the one described by this user (http://www.access-programmers.co.uk/forums/showthread.php?t=113742&highlight=SQL+blank+spaces) where an SQL statement I am using to read data from one table and copy it to another is filling the remaining field space with blanks.
I didn't notice until a VBA module I worked on later on which was to read data from the new table and copy the selected record into a form said it could not find the records. The code:
Do Until blnFound = True Or rs.EOF
'DEBUG: answer = MsgBox("ok", vbYesNo)
'DEBUG: MsgBox rs("Account Name")
If rs("Account Name") = strAName Then
MsgBox "DEBUG: FOUND " & rs("Account Name")
blnFound = True
End If
rs.MoveNext
Loop
The record selector just goes straight to EOF because rs("Account Name") never matches strAName (as strAname has all those damned spaces after the useful data).
I wondered if perhaps there was some way to use the code in the other thread to read the length of useful data and then chop off the garbage spaces afterwards, but I'm worried it would be incredibly slow when running through the thousands of records. Also, the field has legitimate spaces in between words (most of the account names are two or three words in length).
The SQL statement responsible is as follows:
strASQL(i) = "INSERT INTO " & strTableName & "([CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3])" & _
" SELECT [CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3] " & _
"FROM tblCustomer WHERE [" & strField(i) & "] LIKE '" & strValue(i) & "';"
strASQL, strField and strValue are arrays and this SQL statement runs in a loop, as this is part of a search form for multiple possible entries. It all works fine except it always adds those damned spaces :D
Any suggestions?? Cheers.
View 12 Replies
View Related
Apr 19, 2006
I have this table filled with patient information. I imported the
excel file into textPad, then to Access and I have a little problem.
There are 2 fields that contain Patients name because the names are too
long. How I know where each patient name is because in another field
their is the word Patient, then the next two fields are there name.
Example:
Fields: ServiceDate, PayerID, ServType
Data: Patient, (Lahrmer,), Frances L
03/12/06, WEEK, OT Yaha
The Service Date field is filled with dates, until you reach the next
patient in which it has "Patient" in the field. Same for the PayerID &
ServType!
*Some of the Patients name are in the PayerID field, but others that
are longer are in both!
Question!
How do I get the Patient Name from the PayerID & ServType fields and
put them into a field in the table called PatientName? There are 16,000
records, so typing is not an option!
Thanks in advance!! :D
Any Ideas!!!!!!!!!!!!!!!
View 1 Replies
View Related
