Tables :: Extract Data From Memo Field And Put Into Separate Fields Of New Table
Jun 15, 2014
I have a notes field in the customer table that is a memo field. An example of one customer's notes field data :
<div>20.3.14 Ordered 2 cartons</div>
<div>4.3.14 Ordered 2 cartons</div>
<div>18.2.14 ordered 1 carton</div>
<div>30.1.14 ordered 3 cartons SCENTED wipes</div>
[Code] ....
I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?
View Replies
ADVERTISEMENT
May 13, 2014
Is there a way I can take each entry in a memo field and put it into a text field in a separate table. The database is getting really big and the customer notes field for each record has lots of entries. The memo field looks like this:
8.4.14 Ordered 2 cartons
20.3.14 Ordered 2 cartons
4.3.14 Ordered 2 cartons
18.2.14 ordered 1 carton
30.1.14 ordered 3 cartons SCENTED wipes
[Code]...
I want to take each line and put put the date in a date field and the text in a text field in a separate table linked by CustID. Is there a way to do that?
View 7 Replies
View Related
Mar 18, 2005
I am new to Access and was wondering if there was a way to parse the data from a field into two fields.
I have a field named tName which contains both the first name and last name of a person.
How can I get the corrosponding last name into a new adjacent field called tLastName (this field doesn't exist yet)?
Thanks so much in advance,
Paul
P.S. What if the the person doesn't have a last name? or if they included a middle initial?
I know, it's messed up. This DB has a bad setup.
View 2 Replies
View Related
Jan 23, 2006
Does anyone have an idea how to extract the first paragraph of a memo field of a table to be used in a query? Thanks...
View 5 Replies
View Related
Jun 17, 2014
for example how can I extract the following number from the field ITEM 117145 5410076462162, 714774 117072 5410076462223 ,714768 117153 5410076462285,733289 998214 5410076462193 from this record. The field is memo. The common: the string "+" always is in front I tried this Expr2: InStr([ITEM],"+"), but it only give me first one.
The following is record
BARCODE Retail Line Code NSL Code Prod Ean Name Size Facings Capacity Pos Comments Offer Description Offer Type Offer No 5ELKAHQ*agiaji+ 714766 117145 5410076462162 NS PRNGLS SUR CRM AND ON 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiagh+ 714774 117072 5410076462223 PRNGLS ORIG 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiddd+ 714768 117153 5410076462285 PRNGLS SLT/VNGR 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agibia+ 733289 998214 5410076462193 PRNGLS BBQ 190G 3F 15 BUY ONE GET ONE FREE MV 105574
View 6 Replies
View Related
Aug 19, 2013
The interface being used is a main form with various tabs and a subform on each of these tabs.
There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?
View 7 Replies
View Related
Aug 18, 2015
I have imported an Excel table with a column which has several values in one cell separated by "". In my case names of institutions afiliated with a patent. Example of the format of a single cell: MASSACHUSETTS INSTITUTE OF TECHNOLOGY (US) RIVE TECHNOLOGY INC (US)
In order to normalize the Access database I would like to extract the institutions to a separate table (institutions) and that the patent is related to both institutions.
I am using Access 2007.
View 2 Replies
View Related
Aug 7, 2014
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
View 2 Replies
View Related
Nov 7, 2012
How to export ms access table into excel workbook with separate sheet based on a value of field?
For Example:
I have One Table with three fields
Name Address Company
Steve a Apple
John b Apple
Josh c Dell
Pete d Dell
Pat e HP
Jacob f HP
Output in Excel(list for Employee by company):
Sheet 1 Sheet 2 Sheet 3
Apple Dell HP
Name Address Name Address Name Address
Steve a Josh c Pat e
John b Pete d Jacob f
View 3 Replies
View Related
Aug 1, 2007
I have a database with a field LNAME with last names preceded by maiden names in parentheses. I would like a query that would update fields (LAST and MAIDEN) with the corresponding data:
Example –
LNAME = (Jones) SMITH
LAST = SMITH
MAIDEN = Jones
Any ideas would be greatly appreciated.
Thanks,
SKK
View 2 Replies
View Related
Nov 2, 2004
I have imported several Excel files into Access to create tables in the database.
I teach online and basically I need to know how to extract certain bits of data from each table and put them together.
For example:
Table #1 is my student roster list and contains the fields: Firstname, LastName, SchoolName, and several other fields.
Table #2 is a list of schools throughout the state with fields such as: SchoolName, Registrar, ContactPerson, and so on.
There are several other tables involved but I’m trying to make this question as simple as possible and if I can get this question answered, I think I may be able to figure out the rest.
I would like to print a report out for each individual student that will include the school name from Table #1 and match it with the same school name in Table #2 and then extract the pertinent school information from Table #2 for that school.
I have more than one table with a list of schools. Should I name each field that pertains to the school name with a unique name?
To clarify….
Table#1 can have the same school name listed any number of times because some of the students attend the same school.
Table #2… Each unique school name will be listed only once.
These Excel files come to me regularly as they are updated and I am trying to find an easy way to extract the data that I need.
Can someone please tell me how to write this query?
Thanks so much for your help!
View 5 Replies
View Related
Jan 9, 2012
I am new to Microsoft Access. I have built two entry forms for product release information. I have a drop box under the label customer for each specific customer. I would like for this form to send the data entered in the form to the customer specific table from the customer names in the drop box. I have tried to research and execute this myself thus have been very unsuccessful.
View 14 Replies
View Related
Sep 4, 2014
I'm trying to export my table using the following code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "incidents", "c:Incidents.xls", True
But unfortunatly I get truncated errors upon export via the vba above. is it not possible to export memo fields to excel via vba?
View 3 Replies
View Related
May 11, 2005
Hi.. Junior access user here. Need help fast!
I created 2 tables. [General Info] & [Details]
[General Info] has 2 fields. Name(is a primary Key as well) & Organisation
[Details] has 4 fields. Primary Key, Name, No of products & Type
[Details] is a sub form for [General Info]
It is a one to many relationship. With Name from [General Info] linking to Name from [Details].
I can update via the table directly and [Details] links the name fields automatically. I created a form but the form just cannot register the field automatically and requires the user to type in the Name from [General Info].
I have attached the DB file. Pls help... your help is greatly appreciated!
View 3 Replies
View Related
Oct 22, 2005
I am trying to extract data from one field and then put it into another field. I have 2 fields, Code_A and Code_B. I am trying to take the Code * data from Code_A and put it in Code_B. Any help is appreciated.
Code_ACode_B
AlphaCode 3
Code 3
AlphaCode 3
Code 2
DeltaCode 3
Code 3Unknown
CharlieCode 3
Code 3
View 4 Replies
View Related
Dec 6, 2007
Hello people.
Im just after a bit of advice please??
I have created an audit trail for data held on a form so that when a field is changed it adds a line of text to a memo field with the date of the change and the old and new field values. The problem I have is that each time something is changed the line of text is added beneath the existing text in the memo field. This means that for users to view the most recent change they have to scroll all the way down to the bottom of the memo field. Is there a "Quick" way of me having the most recent entry first.
I am using the vbnewline command when adding the text. A sample is shown below:
Dim Response As Integer
Dim AnotherItem As Integer
Dim sqlstr As String
Set Myform = Forms![DataDetail]
Producer = Forms![ListOfProducers(Existing Data Item)]![LstProducers].Column(1)
Response = MsgBox("Are you sure you wish to add a Producer?", vbYesNo, "Data Dictionary v1.0")
sqlstr = "INSERT INTO DataProducers ( Data_Id, Producer_Id ) SELECT Data.Data_Id, Producers.Producer_Id FROM Data, Producers WHERE (((Data.Data_Id)=[Forms]![DataDetail]![Data_ID]) AND ((Producers.Producer_Id)=[Forms]![ListOfProducers(Existing Data Item)]![LstProducers]));"
If Response = 6 Then
DoCmd.RunSQL sqlstr
Myform!LstHistory = Myform!LstHistory & vbNewLine & "Changes made on " & Now & ""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "The Producer '" & Producer & "' was added to this data item"""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "-----------------------------------------------------"
MsgBox "Producer has been successfully added.", vbInformation, "Data Dictionary v1.0"
Thanks folks...
View 2 Replies
View Related
Dec 27, 2007
All,
I have a quick question that could lead into a longer one.
First off, I am trying to associate tests with a course a student is enrolled in. Would it be better to add a column in the courses table and have a comma separated list of all tests associated with each course or would it be better to create a separate table that has a column for the course and another column for the associated test with that course?
If comma separated fields would be better, how would I handle that in forms? How does one parse that field?
Thanks!
View 6 Replies
View Related
Mar 4, 2008
I currently have 8 tables in my Database.
How can i extract all the information in those tables and put all the data into one large table? I want to extract everything apart from one table?
and can I format the large table once the data have been put in i.e. insert new Columns at the start, and populate fields based on the value of other fields values?
Any ideas or help? thanks
Kind Regards
Richard
View 6 Replies
View Related
Jun 17, 2013
Full Name, Forename, Surname, Salutation. This data has been extracted from another database where the Surname field was not required but it is now. So i need to find a way to pull data from the salutation field into the surname field where salutation has data but surname is null. The next one when surname and salutation are null but full name has data i need to pull that across.
View 11 Replies
View Related
Apr 6, 2006
Hi,
I have an old database for generating service reports which has:
Report No
Date
Customer
Site
In separate fields which is fine
but also
wrk1
wrk2
wrk3
etc.
with a separate field for each printed line on the page
I would like to merge all these into one memo field while retaining the previous data which goes back 15 years (it came from a MSWorks 2.0 DOS db which is the reason for the weird structure I think)
Is there any way to write a macro or code to selectively merge some but not all fields in a table?
I've already copied the db and modified it after clearing all the old data, it works fine but I'd like to have access to the old data in the new format
TIA
Kim
View 3 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
Jan 7, 2013
We are running many MS Access databases in a mixed estate - mostly Windows XP terminals. The databases are split with the front end on the desk top and the back ends on a server running Windows Server 2003. We have a chronic problem of crashes when users go back into memo fields to add data - all text. The problem is intermittent, not possible to reproduce and varies in frequency.
View 2 Replies
View Related
Nov 28, 2012
I am trying to construct a query to extract mailing addresses from a table. I have individuals entered into a table (a separate record for each person) but if they are married I want an address such as Mr and Mrs J. Doe so that only one address label is printed off so that only one letter is sent out. If one of them dies then the address should only go to the surviving party eg Mrs J.
View 4 Replies
View Related
Jan 21, 2013
I use a Access database to import data from an email generated from google docs..When the data imports to the table it creates a "Memo Field" with several lines of carriage seperated data containing the Form Field name & answer. (ie: Firstname=John, Surname=smith, etc)Is there any way to seperate this data in the cell into seperated cells in a new record?
View 1 Replies
View Related
Mar 25, 2006
Hello,
Is it possible please to extract a tables structure and data into SQL in Access. I know how to do this in phpmyadmin (an internet based DBMS) but not so sure in Access.
What I want to do is to create an identical version of the table in SQL Server, so therefore require the structure and data in SQL format if possible.
Any help is much appreciated, thank you.
View 3 Replies
View Related
Nov 29, 2006
I have a really simple question :-
I want to write a simple routine to enter a value into a text box, retrieve a record from a table using the value entered as a key (or display a message if it doesn't exist) and then populate a form with the other fields from that record.
So if I entered code 123 it would find the record where code = 123 and display the associated description and price for code 123.
I've created the table and the form but am struggling to do the bit that retrieves the data ! Help!
View 3 Replies
View Related
