Parsing String Into Separate Columns Of A Table

Sep 3, 2007

:confused:

I have a Microsoft Access table with the following columns: A,B,C,D,E,F.
In first row of Column A, I have the following string value: "Al,Peggy,Kelly,Bud,Buck"
What I would like to do is parse this string as such:

Column B:"Al"
Column C:"Peggy"
Column D:"Kelly"
Column E:"Bud"
Column F:"Buck"

Is there a simple VB funtion to accomplish this?

View Replies


ADVERTISEMENT

General :: Query To Separate Columns In Table

Aug 14, 2014

I have a table tblItemSold

ItemNum Date Sold
1111 Aug-10-2014 25
1111 Aug-9-2014 24
1111 Aug-8-2014 23
2222 Aug-10-2014 11
2222 Aug-9-2014 12
2222 Aug-8-2014 13
3333 Aug-10-2014 5
3333 Aug-8-2014 3

I want to write a query and transform above into

ItemNum Aug-10-2014 Aug-9-2014 Aug-8-2014
1111 25 24 23
2222 11 12 13
3333 5 3

View 3 Replies View Related

Queries :: Create Separate Columns From Same Field And Table

Apr 9, 2013

I 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.

View 3 Replies View Related

String Parsing.....

Feb 7, 2006

hi, im new to both access and sql and was hoping i could get some help...

i have a string of numbers seperated by commas stored in my database. i need to count how many numbers there are in each string and then display all the results with less than 36 numbers contained.

am i able to do this with an sql statement, or am i likely to need vba??

(example string: 9.89007472991943, 12.3332061767578, 14.4694166183472, 16.287145614624, 17.5347270965576, 17.6327610015869, 16.1364498138428, ...)

thx in advance

View 1 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Parsing The Left And Right Characters Of A String

Aug 19, 2005

I have the following string: NE1220904
I need to remove the left two characters and the right four characters of a string in a query. I have found how to remove one side or the other, but not from both sides. Thanks in advance for the help!

View 6 Replies View Related

Queries :: Parsing String With Multiple Delimiters?

Aug 13, 2015

I have a field in our database with five distinct strings, separated by a delimited (a slash or "/").

I've been able to successfully write the formula to parse the first field:

ARL: Left$([pipeline]![Short Description],InStr(1,[pipeline]![Short Description],"/")-1)

The second field:

BRANCHMGR: Mid(Left([pipeline]![Short Description],InStr(InStr(1,[pipeline]![Short Description],"/")+1,[pipeline]![Short Description],"/")-1),InStr(1,[pipeline]![Short Description],"/")+1)

and the last field:

DATE: Mid$([pipeline]![Short Description],InStrRev([pipeline]![Short Description],"/")+1)

how to get the third and fourth strings parsed. I know its going to include some nested InStr or InStrRev commands -- just having trouble figuring out the structure. Could potential need a LEN command as well.

Typical field might look like this:

"Name1/Name2/Description/Update/Date"

View 7 Replies View Related

Parsing Through A String And Counting Number Of Occurrence

Feb 12, 2013

I have about 150k rows that look like this

SHORT_DESCRIPTION
unable to view
unable to send out some stuff
what is going on

Is it possible for access to query through each field, and tell me the total number of occurence for each word? In this small example, the output would be:

Unable 2
to 2
view 1
send 1
out 1
some 1
stuff 1
what 1
is 1
going 1
on 1

Is this possible?

View 6 Replies View Related

Queries :: Find Latest Date In A Table Where Dates Are In 2 Separate Columns And Multiple Rows

May 19, 2015

I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)

I have a table "Assessment tracker" with the following structure

Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date

My Data:

Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15

I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.

Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit

[Code]....

Output:

CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015

It does this by using a function shamelessly copied from the web somewhere...

Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.

[Code]....

This is working well (I think)

I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.

Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;

But this is returning

Candidate Unit MaxOfAchdate
TH1 1025/05/2015

I expect it to return

Candidate UnitMaxOfAchdate
TH1 10 07/06/2015

It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)

View 14 Replies View Related

Separate Protections For Columns In Excel

Jan 11, 2005

Hey, does anyone know how to set up a formula that will allow me to turn either one column on, or another, but not both at the same time for data entry eg. one column for dollar figures and another for sterling currencies?

Much appreciated!

View 2 Replies View Related

Splitting Text Values From A String Into Separate Field

Jan 25, 2005

I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5."
Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"

I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.

I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.

Can someone show me the syntax for this.

Thanks!

View 1 Replies View Related

Queries :: Delimited Text To 10 Separate Columns?

Sep 8, 2014

I'm trying to build a query that can parse Delimited text to columns, for example I have the following:

ID,Name,Tel,Fax,Email,Directorate,DOB,AOCD,Reg,CD

I would like to convert the above in 10 seperate columnns within a query?

Is this possible? I know you can import delimited text to columns but that is not what i'm after for other reasons.

View 14 Replies View Related

Queries :: How To Split Data Into Separate Columns

Nov 11, 2013

I 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.

View 4 Replies View Related

Forms :: Inserting Data From Multi Rows Into Separate Columns

Oct 17, 2013

I have a database with a form that has 3 fields in a subform. I need to paste there data from multi rows. Now I have to do this column by column and paying attention that I select them correctly. Is there a way to directly insert the data by selecting only 1 field and have the data directly there as it would in excel?

Now we use excel as a "between" road to paste it there and then copy it from excel and paste it into access.

View 9 Replies View Related

Adding Values In Separate Columns Dependent On Text In Another Column?

May 16, 2013

I'm trying to make a database to track inventory or several items. Basically, I have four tables:

1) RawMaterialList - includes a list of all raw materials.
2) PartList - includes a list of all finished product using said raw materials.
3) RawMaterialRecieving - contains details from each packing slip of incoming raw materials.
4) ShipmentRecord - contains details of daily shipments.

Each of these tables is fed by a form of the same name. I should note at this point that I basically taught myself how to use Access and I imagine I'm in the dark about quite a few things it can do. I've made several databases over the last few years, but I'm stumped at this point.

Here's my problem. In the form RawMaterialReceiving, I have several fields aside from basic information:

1) Item - a list of of raw materials from table RawMaterialList
2) Description - also dependent on info entered into table RawMaterialList
3) Quantity

But, I have 12 of these instances.

Item1, Item2...Item12;
Description1, Description2...Description12;
Quantity1, Quantity2...Quantity12.

My problem is I want to add up the quantities of each raw material and I'm not sure how to go about that. Lets say on May 13, I received 15pcs of Part A and 20pcs of Part B. I enter this information as Item1 and Item2 respectively. On May 14, I received 30pcs of Part B. I enter this information under Item1. Now I want to add up all of Part B (50 pcs). But Part B has one value listed in the field Quantity1 and one value listed in the field Quantity2.

Example:

1st Entry:
May 13
Item1 = PartA Description1 = PartA's description Quantity1 = 15
Item2 = PartB Description2 = PartB's description Quantity2 = 20

2nd Entry:
May 14
Item1 = PartB Description1 = PartB's description Quantity1 = 30

How do I get it to add up Part B to get 50pcs?

View 10 Replies View Related

Combining 2 Query Columns Into 1 Column With Data In Separate Rows

Mar 29, 2012

I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.

The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.

I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.

Current:

ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0

Needed:

ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg

I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.

Access 2007, Windows 7.

View 5 Replies View Related

Access Reports: Adding Lines To Separate Rows And Columns Like Excel

Nov 4, 2004

Ive been converting .xls files to Access database files. I would like to use ACCESS to develop the Reports but the client wants the Reports to look like those in EXCEL..eg. where you have lines between rows and columns. If I could give my Access Reports the same look and feel, I could wean these guys off of Excel and into the wonderful world of relational models.

Does anyone have sqlcode or tricks I might use to create the Excel 'look alike' report in Access?

thks in advance...and I will sum.

the ravenman.

View 1 Replies View Related

Modules & VBA :: Importing / Parsing From PDF Into Table

Aug 27, 2014

We publish a PDF file every week full of taskings that our subordinate organizations need to accomplish for the following week. I would like to have parse the PDF and store the data in a table. I think the only way to start this operation is to first save the PDF to Text (Plain) as this create some way to delimit and parse the data. So, once it's saved as a txt file, it basically looks something like this:

Code:
//
Column 1 Data/StuffIDontCareAbout/
StuffIDontCareAbout/StuffIDontCareAbout//Column 2 Data
StuffIDontCareAbout/StuffIDontCareAbout/
Column 3 Data

[Code] ....

...and so on and so forth anywhere between 50-100 more times

The entire text file will always start and end with "//" at the top and bottom. You also see that each tasking paragraph (starting with "Column 1 Data" and ending with "Column 3 Data") is preceded by a "//" on its own line, and also followed by a "//" on its own line. The text file will always look exactly like this with each tasking paragraph having the same number of forward slashes.

Column 1 Data always starts on a new line under "//" and runs until the first "/" (single forward slash).
Column 2 Data always starts after the "//" on the 2nd paragraph line and runs until the end of the paragraph line
Column 3 Data always starts on the 4th paragraph line and runs until the end of the paragraph line

Here's a quick example of what I'm talking about:

Code:
//
37 NOS001/DCO/
TaskPer/TBD//310001ZAUG2014-292359ZSEP2014
GenText/Remarks/
(U/FOUO) This will contain the actual task description and details. You can see that "(U/FOUO)" contains a forward slash.

[Code] ....

So after it's parsed, my table would have the following new rows:

Code:
Column 1 | Column 2 | Column 3
37 NOS001 |310001ZAUG2014-292|(U/FOUO) This will contain
582 NOS012 |280001ZAUG2014-022|(U/FOUO) This another task

View 3 Replies View Related

Cannot Get Columns To Add Up - Query Display It As Text String

Apr 28, 2014

For whatever reason when I try to add up two columns in a query instead of adding up the two numbers it displays it as a text string. So if one column has a 5 and the other is a 2 I am looking for the calculated column to reflect 7, currently it is showing 5,2.

I have added up items in the past so I am unsure what the problem is

should be [rev bid amount 1]+[rev bid amount 2].

View 3 Replies View Related

Queries :: Joining 3 Queries And Displaying Results In Separate Columns

Jul 31, 2013

I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).

My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)

Using this SQL:

Code:

SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION

[code]...

Which returns a table that looks like this:

Final Equipment, Duration

Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004

And so on.What I need the table to look like is this

Final Equipment, 3 Mth, 6 Mth, Historical

Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004

And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.

View 6 Replies View Related

General :: Listbox - Use Columns Data As Where Clause Of SQL String To Populate Another Form

Oct 5, 2012

I have a listbox that is populated with data from a table. I would like to use one of the columns data as the where clause of a sql string that will populate another form, how do I get the selected items column data that I need into a variable? This is in Access 2010 vba.

View 9 Replies View Related

Tables :: 2 Table To Generate Separate Matrix Table

Mar 9, 2014

I have 2 tables

- Staff Position(Unique Position Name, Description, Hierarchy)
- Training (ID, Name, Description etc.)

I essentially want a table with Staff Position as the Field, and Training as the Rows. The intersecting entries/matrix will be Yes/No to say whether that staff positions requires that training.

Simple Example

..................... Worker ........ Senior ........ Principal
IT Training ......YES ............. YES .............. YES
Accounts ......... NO ............. YES .............. YES
Management ... NO ............. NO ............... YES

I need the user to be able to add as many training entries and as many staff positions as they want. It doesn't HAVE to be that sort of format...

View 4 Replies View Related

Separate Table For Dates?

Oct 27, 2005

I am designing a database to enter daily/monthly performance numbers for employees and department totals. However, I don't want to have to enter the month and/or day for every category for each employee entered. In looking through these forums, nobody recommends a separate date table. But it seems time-consuming to have to enter the reporting month for every sales category for every employee. To wit:

TblEmp
EmplID
EmplName
HireDate
TerminationDate

TblCategories
CatID
CatName
CatType

TblDate
MonthYr
DayMonth

TblMonthlyPerformance
AutoID
EmplName
CatName
MonthYr
Amount

TblDailyPerformance
AutoID
EmplName
CatName
DayMonth
Amount


If I don't have a table for dates, then for every category for every employee, I would have to enter the date...right or wrong? Or am I thinking flat.

View 14 Replies View Related

Lookup Or Separate Table?

Aug 19, 2004

While trying to "normalize" a developing database, a question has come up. I have two sets of information that will probably never change. Hair color and Eye color. Should these be put into a separate table or can I include those two fields in the table containing information about the person and then use the lookup wizard function in the table design and "type in the values I want"? I am using '97. Thanks.

View 1 Replies View Related

Lookup Wizard Or Separate Table

Jun 8, 2005

I've been doing a lot of research and reading on databases and normalization and things of that sort because I need to create database from scratch. I've been maintaining a db that someone else made, but it turns out I will have to recreat the whole thing because it's not useful anymore and the users need a more user friendly db. I have a couple of days some are:

tblTO
------
TONumber
Contract
Product Directorate <- combo box 20 items
Document Type <- combo box 15 items
TOManager <- combo box 10 items

tblTOItem
----------
Product <- combo box 13 items
JobNo
BasicDate
ChangeDate
ChangeLevel
TotalBillablePgs
TotalTextPgs
TotalArtPgs
Standard <- combo box 22 items
TemplateVersion <- combo box 20 items
SourceFormat <- combo box 15 items
SourceLocation <- combo box 10 items

The ones I have the arrows for will be a combo box on my form.
My question is should I make each of these field a table of its own?
For instance make the Product field a table and list all the 13 products there?
Or keep the Product field in the tblTOItems and in design view of tblTOItems use the Lookup Wizard and type in my values there to make a lookup column.

I have seen it done both ways, and I was just wondering which way is more efficient.

View 5 Replies View Related

Exluding Records For Separate Table

Apr 29, 2006

I created a report where is lists sales volume by day. I was asked to create a table where data can be excluded from this report. The exclusion table consists of only three fields:

Date Department and a check box

What do I need to do in the query to have it exclude the data listed in this exclusion table. The table only holds data we want excluded, not all dates and departments.

I originally added the table to the source query and made the criteria "False" for the check box but when the query runs it does return any data at all.

Please help!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved