Queries :: Extracting Data From List In Separate Field
Sep 19, 2014695515*97
531924*11
495853*131
377035*181
Is there a way to extract the 97, 11, 131, 181 numbers from the above list in a separate field?
ADVERTISEMENT
Queries :: Separate 6 Digit Data In Same Field
Jun 10, 2015In same field Im saving every time 6 digits data and i need to separate them. (It was not my fault. They created DB like this... )
For example my data is
123412(main-field) and i want to see them 1234(field1) 12(field2)
Extracting Data Into A New Field
Dec 13, 2005You guys have been so helpful in the past. Perhaps you can help me with this as well. I believe it’s a bit out of my scope. I have a list that reads similar to this:
Field 1
DMA: ALBANY
CAR 1
CAR 2
CAR 3
DMA: BOSTON
CAR 1
CAR 3
CAR 4
CAR 5
CAR 6
DMA: HOUSTON
CAR 1
CAR 3
CAR 4
CAR 5
I need it to read like this:
Field 1 Field 2
DMA: ALBANY CAR 1
DMA: ALBANY CAR 2
DMA: ALBANY CAR 3
DMA: BOSTON CAR 1
DMA: BOSTON CAR 3
DMA: BOSTON CAR 4
DMA: BOSTON CAR 5
DMA: BOSTON CAR 6
DMA: HOUSTON CAR 1
DMA: HOUSTON CAR 3
DMA: HOUSTON CAR 4
DMA: HOUSTON CAR 5
If my list was this short, I would do it all manually, but my record list is in the 10,000s. Basically I want field 1 to read: DMA:ALBANY and Field 2 to read:CAR 1, and so on. I’ve tried linking the table to itself in a query, however, that hasn’t proven to be helpful. I've also searched the FAQ's and newsgroups. Any insight would be appreciated.
Thanks,
Amanda
Queries :: Extracting Data From A Query
Jun 19, 2015SELECT tblLeaveBalance_Cashables1.Facility, tblLeaveBalance_Cashables1.Division, tblLeaveBalance_Cashables1.Position, tblLeaveBalance_Cashables1.Name, tblLeaveBalance_Cashables1.[Class Code], tblLeaveBalance_Cashables1.Class_Title AS Classification, tblLeaveBalance_Cashables1.CBID, tblLeaveBalance_Cashables1.[SS Months], tblLeaveBalance_Cashables1.Age,
[code]....
I need to extract data that where vacation time is >640 or annual leave is >640 and where personal Hol hours are >24 and CBID is M06,S06 and R06.
Extracting Data From One Field In A Table To Another--Help!!!
Jun 27, 2006Hi Guys,
I have a table called "tblEmployees" with the following fields:
EmployeeNo (Primary key)
Name
Address
DOB etc
Letter1Dated
Letter2Dated
I have another couple of tables:
"tblLetter1" with the following fields:
EmployeeNo (Primary key)
Name
Letter1Dated
Letter1RemainingFields...
"tblLetter2" with the following fields:
EmployeeNo (Primary key)
Name
Letter2Dated
Letter2RemainingFields...
I want to be able to put the data from 'Letter1Dated' field (from the "tblLetter1" table) into the 'Letter1Dated' Field in the "tblEmployees" table.
Likewise, I want to be able to put the data from 'Letter2Dated' field (from the "tblLetter2" table) into the 'Letter2Dated' Field in the "tblEmployees" table.
All three tables have the same primary key (EmployeeNo), which makes it even more difficult to do this!
I understand this may sound abit confusing so i apologise for this.
I would really appreciate it if anyone could point me in the right direction please.
Thanks alot.
Extracting Data From A Field In A Table.
Jan 29, 2007when I have created my table I have created a field called name that stored both last and first name. Right now I need to extract the the last name from the the field name and store it in another field "lastName". Any ways to do that?
View 7 Replies View RelatedQueries :: Extracting Data From Unusual String
Mar 5, 2015I have a flat file sent to me today that contains a number of fields with the same issue. As an example, I have a field named Coordinators from a table named Audit and the data within it is formatted as follows:
Mark Hollings;#14664;#Judy Thompson;#10169;#Tammy Wilson;#3608;#Tim Levy;#2785;#David Simpson;#1251
Is there any way to extract only the names while leaving in either the ";" or replacing it with a ","? My desired output would look like this:
Mark Hollings;Judy Thompson;Tammy Wilson;Tim Levy;David Simpson
This field can be populated with one or more names so that's a variable in all of this as well.
Extracting Data From Multiple Queries To A Table/excel
Apr 24, 2008I am an access (2003) amateur, willing to generate an excel file or table from the data from many queries. Moreover, some fields in those queries having different names but same data type, should be filled in the same columns in the new excel/table. Should I use Macro/SQL?:(
View 2 Replies View RelatedModules & VBA :: Remove Data From One Field If Separate Field IsNotNull
Apr 1, 2014Is there a way to remove data from one or multiple fields if the value of a separate field IsNotNull?
View 2 Replies View RelatedHow To: Separate Data In One Field Into 2 Fields
Mar 18, 2005I 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.
Queries :: How To Split Data Into Separate Columns
Nov 11, 2013I have been given the task of organising a mail-merge with a sharepoint list, but the names and emails attached to each object are seperated by a delimiter. Furthermore each person is associated with many objects, and they want the merge programmed to only send 1 email to each person.
So what I need to do is split the data in one column into three separate columns, and then perform a concatenate. The concatenate isn't an issue, but how to split the data into 3 new columns within Access?
If worst comes to worst I'll tell them they will have to use the text-to-columns function in excel first, but would like to try and avoid that where possible.
Queries :: Create Separate Columns From Same Field And Table
Apr 9, 2013I have 1 table that I duplicated to make 3 tables total. I did this b/c I am trying to create separate columns from the same field and table. The field is Workorder. Each workorder list the workorder number followed by a dash and then code. I am wanting to put all of the workorders with the same code in it's own column.
I have 5 codes that i am searching for. The first column list the workorder and a code (123456789-AD). The second column (123456789-BC). I'm good to this point but my problem occurs next.
The third column i am trying to put 3 types of workorder and it's code in the same column. As follows, (123456789-CD, 123456789-TC, and 123456789-PTC. However, when I do this it takes the results from the 3rd column and applies it to the 3rd column but also the 1st and 2nd column. I tried a UNION query and unless I am doing it incorrectly it does not work.
Queries :: Parameter In Query By Two Separate Form Field
Oct 31, 2013I want to set parameter in my select query with or function as in detail.i have two form with same field
1-with field A
2-With Field A
now i want to select data for a table .with a parameter select with Field A in form 1 or Field A in Form 2.How can i do it
Tables :: Extract Data From Memo Field And Put Into Separate Fields Of New Table
Jun 15, 2014I 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?
Queries :: Using Variable In A List Field Query - Getting Complete List On Initial View
Mar 28, 2014In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
General :: Separate A List Box In Two Categories
Nov 28, 2012I would like to separate my list box in two categories.Indeed it is composed by 2 types of criteria but arranged in the same List box (I can't create another List box, it's forbidden).how can I proceed?I've thought to insert a dash but it has to appear all the time and I don't know if it's possible to do that.
I've also thought to enter a title IN the list box above each category but it has to appear all the time too..when I click on one item in the List box it becomes highlight.May be code the 2 titles I could add for being always selected (highlight).I don't want to have to select the 2 titles every time!!!
Extracting A Portion Of Data
Oct 3, 2006I probably have an easy question for one of you out there, but I just cannot get it myself.
I need to extract the data between two characters in a string.
The data will be similar to this:
T4454: Text Text-Text: $296.07: Text Text
I need to get the dollar amount between the dollar sign and the colon on the right of it.
So far, I have this for my query:
Amount1: Mid$([subject],InStr([subject],"$"))
Which gets the amount to the left side, but also has the $ in it.
In the example above, I would like to just end up with 296.07 as the result from the query. I should also add that the dollar amount may vary from 1.00 to #,###.00
Thanks in advance for any help you can provide
Extracting Data From Combo Box
Aug 1, 2006Hi,
I have a combo box, which the first and last name of an employee is combined using trim. Does anybody know of a way to seperate both fields as these would be used an a query so I would need them seen as 2 fields otherwise there won't be any matches.
Cheers,
Ben
Extracting Data From A Table As String
Nov 15, 2005I need to be able to extract individual fields from a table as strings in order to then send this data to my EPOS printer (which will accept only strings to print) I have created the table with a query so the question could equally be how can i output strings as query results. Im using Access97 HELP!!
I have tried to set up a ADODB.Connection but either Access 97 wont accept it or i've got the coding wrong
Extracting Data From A Single Cell
Feb 20, 2005Hi,
Is there any way that i could extract data from a single cell inside the table to the forms, and vice-versa?
Extracting Concatenated Column Data
Oct 23, 2007Hi, I need a little help. I will give you the background on what i am doing to hopefully make this easier to resolve. P.S I am open to other approaches.
1) Items are requested for order indervidually
Table/Query looks like this
Code:ID#PartNumberStatusQty ReqOrder StatusNeed DateWork Order110-980-21Stock Required2Waiting26/10/2007WO07-12CR212-5-3Stock Required30Waiting26/10/2007WO07-1310-980-21Stock Required1Waiting28/10/2007WO07-24CR212-5-3Stock Required20Waiting28/10/2007WO07-25CR212-5-3Stock Required15Waiting28/10/2007WO07-2
2) The following Module concatenates the columns (my idea being the user can order all items in bulk.
Code:Public Function Conc(Fieldx, Identity, Value, Source) As Variant Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim SQL As String Dim vFld As Variant Set cnn = CurrentProject.Connection Set rs = New ADODB.Recordset vFld = Null SQL = "SELECT [" & Fieldx & "] as Fld" & _ " FROM [" & Source & "]" & _ " WHERE [" & Identity & "]='" & Value & "'" ' open recordset. rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly ' concatenate the field. Do While Not rs.EOF If Not IsNull(rs!Fld) Then vFld = vFld & ", " & rs!Fld End If rs.MoveNext Loop ' remove leading comma and space. vFld = Mid(vFld, 3) Set cnn = Nothing Set rs = Nothing ' return concatenated string. Conc = vFldEnd Function
3) A query calls on this
Code:SELECT Conc("ID#","PartNumber",[PartNumber],"Qry_70_BOM_Awaiting_Order") AS [BOM ID#], Qry_70_BOM_Awaiting_Order.PartNumber, Sum(Qry_70_BOM_Awaiting_Order.[Qty Req]) AS [Total Qty Required], Conc("Work Order","PartNumber",[PartNumber],"Qry_70_BOM_Awaiting_Order") AS WOrdersFROM Qry_70_BOM_Awaiting_OrderGROUP BY Qry_70_BOM_Awaiting_Order.PartNumber;
Producing this result.
Code:BOM ID# PartNumberTotal Qty RequiredWOrders1, 3 10-900-213 WO07-1, WO07-22, 4, 5 CR2162-5-365 WO07-1, WO07-2, WO07-2
OK, Now after that, (ALL ABOVE WORKS FINE).
I would like my users to purchase againts the new BOM ID# records as seen above. Once a order is placed against these i will, put something against each records to show there order status. (i'll take care of this part.)
Code:BOM ID# PartNumberTotal Qty RequiredWOrders Order Status1, 3 10-900-213 WO07-1, WO07-2 Waiting2, 4, 5 CR2162-5-365 WO07-1, WO07-2, WO07-2 Ordered
Finally, sorry about the lenght of this, I need to be able to use the above BOM ID# to reference back to the the ID# table at the start of the thread. In this example Id's records 2 - 4 and 5 will show ordered.
In other words how do i extract 2, 4, 5 back out of the above and into there original state, but including the order status?
Hope this makes sence
General :: Extracting Data In Forms?
Jun 12, 2014I have managed to get my access DB set up, get the data entry, searching and editing working perfectly (in forms)
The next stage is extracting data from these.
ie I four of the columns I have are
Date Received
Portal (drop down list of 3 choices)
User Initial (list of 4 choices)
Days of the Week (ie Monday, Tuesday etc)
I need to be able to search through the database, ie between X Date and Y Date and mark the totals from each of the sections. so the following
Date: 01/06/2014
to
Date: 13/06/2014
Portals 1 = XXXX
Portal 2 = XXXX
Portal 3 = XXXX
Total = XXXX
Then I'll need to do one for all of the above. and more.
So how can I do this? will a report do it? or a form where it searches down and displays totals?
Modules & VBA :: Extracting Data From PDF Form
Aug 7, 2014I am trying to extract information from a fillable PDF form that my company has created. Rightfully so, they have completely locked down all options, so I am not able to convert the file in Adobe Reader to a text file. I've seen options to extract information using VBA code with Adobe Acrobat; however, I do not have a license for the program. Is there any VBA code that uses Adobe Reader to extract information and put it in an Access database?
View 9 Replies View RelatedExtracting Part Of A Field
Jul 27, 2005I found a thread from last year that is close to solving this question but not 100% so I'll post this new thread.
I have a field called ITEM_NUM in a database with the following structure:
XX-YYYYYYYYY
XX-YYYYY
XX-YYYYYYYYYYYY
I need to extract the Y portion of the data and a Query format would be the best.
FYI, the X portion consists of 2 Letters/numbers then the Hyphen and the Y portion is variable in length.
thanks for your help!
Tables :: Importing / Extracting Data From PDF Files
Nov 25, 2014I am building a database for a coworker. For one table, he'd like to extract data from different companies' financial reports, specifically a Schedule RC-B (schedule of securities). The format it comes in is PDF, but there are options to download XBRL, SDF and Taxonomy. My computer doesn't recognize any of these other formats. Plus, I'm guessing each company will have a slightly different format, so I don't know if there'd be an efficient way to set up an automatic import. Any way for importing/extracting data from these files?
View 2 Replies View RelatedModules & VBA :: Extracting XML Data From Multiple Folders
Jul 1, 2015I have over 500 folders that contain XML files in each folder. I need to go through each folder and append the data from the XML files into my database.
View 1 Replies View Related