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 Replies


ADVERTISEMENT

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

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 :: 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

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

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

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

Queries :: How To Split Colum Value (with Comma) To Multiple Columns

Mar 8, 2015

how to split this , I am new Access DB, I worked in Sql Server but access new to me

I have the below data

Appid Names
100 John,Bob,Kondya, Lima
200 Shor,James,Kim
300 Cinem

I want to convert to

Appid Names Name1 Name2 Name3 Name4
100 John Bob Kondya Lima NULL
200 Shor James Kim NULL NULL
300 Cinem NULL NULL NULL Null

View 2 Replies View Related

Split 1 Column Data Into 2 Separate Column

Oct 5, 2005

Hi,

I need a query that could split data into 2 different field.
The table pic is attached for your ready reference. I tird with Left & Right funtions with no results.

Any one can help me please.

Table name is Agcad and field on which I need to split is Board_Size. The problem is Board_Size data is sometime in 3 digit (first half part) and sometime second half part is in 4 digit. It is a text data type field.

Thanks in advance...
Ashfaque

View 1 Replies View Related

Queries :: Separate 6 Digit Data In Same Field

Jun 10, 2015

In 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)

View 2 Replies View Related

Queries :: Extracting Data From List In Separate Field

Sep 19, 2014

695515*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?

View 1 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

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 1 Replies View Related

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

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

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

Tables :: Can Split A Column Values Into 2 Columns

Jul 3, 2014

I have a column that's called "Date" which stores values for begining to end date as 'Text' like: 070314-073114 So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114?

This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.

View 14 Replies View Related

Forms :: Hide Columns On Datasheet Of Split Form

Jun 3, 2015

I am pretty new to VBA and I am having the same problem of hiding and un-hiding columns on a datasheet of a split form. I am building a system that will be used in front of customer and therefore wish to hide columns that contain cost sensitive data. The same forms need to be able to show the Sales Rep the hidden columns simply by ticking a box. Basically a toggle on and off of hidden columns.

I have been playing around with the code below (which i found in another forum) to hide the Field called COO when I click the tick box called chkHIdeFields check box. This works on a Single form but not on a datasheet of a split form.

Private Sub chkHideFields_Click()
' Note: vbTrue = -1
If Me.chkHideFields = vbTrue Then
Me.COO.Visible = True
Else: Me.COO.Visible = False
End If
End Sub

I have been able to hide the column of my split form by using the ColumnHidden property of the On Load event just as a proof of concept that the ColumnHidden property actually works to hide a column, which it does. This is the code that im using to hide the column called COO.

Me.COO.ColumnHidden = True

However if I then set it to = False and then close and open the form, it doesn't unhide the column. the only way I can unhide the column is to do it from the Form view via the un-hide dialogue pop up box.

I have two issues here, the first one is getting the form to recognize when to hide and when to show the column of the datasheet on the split form and the second is to get the code to for the check box method.

Lastly once I get it working for 1 field I need to be able to define a list of 7 or 8 other fields all at the same time.

View 4 Replies View Related

General :: Split Form And Subform Columns Not Saving

Oct 25, 2013

For some reason when I change the order around of the columns in a split form or subform 90% of the time it wont save (right click save, file save, etc..) and have to constantly redo it until it finally works. Am I missing something obvious as to why this is happening?

View 3 Replies View Related

Forms :: Position Of Columns In Table Below A Split Form

Dec 3, 2013

I've created several Split forms that have the data input fields in the form with the relevant query datasheet shown below. As you tab through the form fields, the various cells in the datasheet are highlighted and move across the data row (as one would expect!). I want to put a particular field / Column at the start of the datasheet so that it's always available for view, but it seeme that what ever I do the column ends up back at the very far end of the data row!

I've sussed out the "Freeze Fields" facility which will keep the first column visable whichever cell is highlighted across the data row.

The column I want as the first column currently sits at the far end of the data row. So far I have dragged the row to the first column position; I've arranged the Query driving the form so that the column is at the front of the row, both in design view and in datasheet view, but to no avail. Everything I do to put the column at the start of the row in the datasheet shown below the form ends up with it back at the far end of the row the next time I open the form.

View 1 Replies View Related

Modules & VBA :: Trying To Split A Dashed Number Into Multiple Columns

Jul 22, 2013

I am trying to split a number sequence (Ex. 25372-400-E10-0000-D0021) Where the third number in the sequence can be either 2 or 3 numbers/letters.

The idea is to have a query that will split this number into its individual parts. I have seen several forums about splitting two numbers and such but I can't seem to make that work when I need to create 5 columns from this one field.

View 7 Replies View Related

Queries :: Get Rid Of Duplicate Records That Show Different Data In Multiple Columns

Sep 8, 2013

I have 10 tables linked in one query. 9 tables are linked to one main table (one to many relationship).I want to get rid of duplicate records that show different data in multiple columns. I want only one record of this but retain with different data under different columns to be separated by commas. For example: I want this...

Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training 1/1/2010 5/1/2010
1 John Smith Python Training 1/30/2011 4/1/2011
1 John Smith Leadership Development 6/27/2013 9/1/2013
1 John Smith Sensitivity Training 9/5/2010 -
2 Hank Joel MS Office Training 8/1/2010 10/1/2010
2 Hank Joel Sensitivity Training 8/1/2010 10/1/2010
2 Hank Joel WHMIS Training 11/15/2010 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012

To turn like this:

Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training, Python Training, Leadership Development, Sensitivity Training 1/1/2010, 1/30/2011, 6/27/2013, 9/5/2010 5/1/2010, 4/1/2011, 9/1/2013, -
2 Hank Joel MS Office Training, Sensitivity Training, WHMIS Training 8/1/2010, 8/1/2010, 11/15/2010 10/1/2010, 10/1/2010, 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012

I am using two tables to find the data (main - "employee tbl" and "courses tbl")I have been trying to follow Allen Browne`s method, but I`m unsuccessful. This is the code I've put in SQL of this query:

Quote:

SELECT [Employee #], ConcatRelated("[Courses]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Start Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Completion Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl];

However, I am prompt with "Syntax error in query expression".It also prompts another expression to be in error when I include the above but it runs okay when I don't do the above:

Quote:

WHERE (([Employee Tbl].[Employee #]) Like [Enter Employee ID or leave blank for ALL employees] & "*")

I placed the following in VBA module:

Code:
Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _

[code]....

View 4 Replies View Related

Single Data Form Put Data Into Separate Specific Tables

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

Queries :: Consolidate Multiple Columns Into Two Columns

May 14, 2014

I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.

What the simple query looks like: [URL] ...

Second what I want the query to look like: [URL] ....

View 2 Replies View Related

Separate Data Into Two Fields

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

How To: Separate Data In One Field Into 2 Fields

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







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