split a column into two seperate columns

I was wondering if it possible to use an expression in a query to split one column in a table into two seperate columns. Column X is Description and looks like this, for example.
2,500 @ $21.55. The @ symbol is always present so I essentially want to wind up with two columns Quantity (2,500) and Price

Sponsored Links:

Related Topics

How to split the content of one column into 4 columns?
How to split the content of one column into 4 columns?

Please, see the attached image that shows my question
Attached also the table
i need to split the column that contains the N E values into 4 columns
Why I need this?

Im given the coordinates in long-lat and I need to convert them into XY, so first I need to convert them into Decimal degrees then to be converted to xy.


Macro to export to word?
I have an access table that has 2 columns. I need each line exported to a seperate word document. Column A and Column B (where A goes on top of word doc and B near the bottom of the word doc).

I will have about 2000 lines that need to go to seperate word doc


Split string into Date and Time Columns?
I am looking for assistance in a sql query in MS Access 2007.

I have imported a table into access. One of the columns is a column called DTG_LOCAL (which is short for Date/time group local time). This column is a string column with the format DDMMMYYYY:HH:MM:SS or 01JAN2010:13:40:00.

I am looking for a sql query that will take the string DTG_LOCAL column and break out DDMMMYYYY and HH:MM to their own columns with an additional column with just HH for a total of three new columns with the broken out information. The three columns would be DATE, TIME, and HOUR; Lastly, convert the columns from a text column to DATE/TIME respectively.

DTG_LOCAL --> DATE / TIME / HOUR or
01JAN2010:13:40:00 --> 01JAN2010 / 13:40:00 / 13

The other columns in the file are not deleted.

The table name is GPS_AI_Import


Default Split Form Column Layout
I have a form displayed in Split Form view. A certain number of columns show in the datasheet portion of the form. How do I set which default columns show and in what order? I know how to unhide and move columns around, but it changes everytime you leave the form.


Default Split Form Column Layout
I have a form displayed in Split Form view. A certain number of columns show in the datasheet portion of the form. How do I set which default columns show and in what order? I know how tounhide and move columns around, but it changes everytime you leave the form.


Splitting One Column Into Multiple Columns
I have an Excel workbook, where a language column has 1 to 5 languages. For example:
Language
Record 1: English
Chinese
Spanish

The 3 languages are separated by hitting the enter key, and not the space bar key. I have about 400 records of this issue.

Is there a way I can split the language column into 5 columns at most? And then, if a record only has 3 languages, only the first 3 columns are populated?

FYI, I have MS Access


Split Data in a column in Excel, via VBA Code in Access
I have an excel file I need to read and update a column in an access table. But the data I need is in a combined format like AA-000, BB-001 etc.
I need to separate the values AA, BB as one column and 000,001 as another column.

So the tasks I need to do is:
read in the Excel file
split the data in a column in to two columns
update the already existing access table with the data in the two new columns.


Splitting positive and negative figures into seperate columns
hoping someone can help

I have a table called Table1 and within it are various columns including a Transactions column that contain both credit and debit (positive and negative) transactions. As well as the Transactions column in Table1 there is also an Account Number column. There are several account numbers and each account number has several transactions.

The end goal is to create a query that splits the debit/credit transactions into separate columns and then totalled for each account.

Iv tried creating one query with <0 criteria under the Transactions field for negative and vice versa for positive then a third to bring the two columns together but its not really working!

Ideally it would be great to do from one query but think it may be unlikely to do from the query design view.

Whilst I have no experience with SQL, I am confident of following any clear guidance offered


Split a field
Have a table column which has data like 1, First and Last name. Trying to split that data into 2 columns: Team_Num (which is the above 1) and Name (which is above First and Last name


Auto resize columns in split forms
Just to confirm, is it true you can't auto resize column widths in Split Forms using VBA code?

For example :

Me.[nameOfColumn].Columnwidth = -2

This doesn't seem to work in my split form


The Columns of a Table
Earlier, we introduced a database as an application made of one or more lists. We also mentioned that, to make a list easy to view and explore, its items should be organized in categories.
Topics: Columns Fundamentals, Programmatic Column Creation, Column Creation With DAO, Column Creation With SQL, The Name of a Column, The Types of Columns, Column Maintenance and The Fields of a Fields Collection.


Calculating the Sum of Mulitple Columns
I want to be able to pull a query, and it have instead of False positive for A B and C one column of False Positives (sum of abc). I want to be able to combine certain columns to make the query I pull much smaller, but need the information to be seperate due to other reports.

I've tried everything that should work, and it just doesnt


Split Cell Contents
I have a table with two columns in it:

Column 1 = 1123002287 8000071204D03 20061793
Column 2 = 20061793

I need to verify that the last 8 digits of column 1 match the contents of column 2. Is there a way to create another column that will show the last 8 digits of column 1? Or is there a way I can take the contents of column 2 and search the entire spreadsheet for the exact same string of characters? These serial numbers are automatically scanned into the computer so I can't manipulate them, I just need to verify that the serial numbers match up.


Iif Code Help
I've been trying to do something with an Iif formula for awhile now and I just can't get it to work. Here's what I'm doing:

I have 4 columns of data where only one column ever holds a number out of the four. In other words, I have 4 columns that might look something like this:
ColOne ColTwo ColThree ColFour
1 0 0 0
0 5 0 0
0 0 9 0
0 0 0 7

I need all this data consolidated into one column, so the above data would look like this:

ColAll
1
5
9
7

What I've been trying to do is the following:

If columns 1, 2, and 3 are null, enter column 4.
If columns 1, 2, and 4 are null, enter column 3.
If columns 1, 3, and 4 are null, enter column 2.
Otherwise, enter column 1.

However, when I do this, it ends up leaving everything null except the "false" values, which is the column 1 data.


Filter a subform based on a list box
I'm relatively new to access, and I'm trying to create a form where if I click on a selection of a list box the subform below it will be filtered automatically on click (I was hoping I didn't need a seperate "Filter" command button on the side, but I guess that would work just as well).

The list box is titled List46, and has two columns (let's just say column A and column B), and the subform is titled [Query1 Subform2], and has 10 columns that are based on a previous query of 3 tables. I would like it so that the subform would filter so that it's second column would match column A from the list box once I click on the listbox


problem with summing 2 columns
I have a crosstab query which uses a NZ function to replace with 0. I have another query where I am doing by totals since it uses multiple queries. My problem is the 2 columns, when I put [column 1] + [column 2] which would be represented by (column 1) being 10 and (column 2) being 5, my outcome is 105.

The query just merges the 2 columns. When I do a sum, I get a # that I don't recognize at all. what am I missing?


Two Remove those rows in a table which has already occured for a particular value
I have one table which consits of 5 columns . I have imported it in access database.

I have two columns say column A and D which consists of integer values and the values of the columns are say in the range 1 - 100 ( both the columns ). so there is a row say in column A value is 38 and in column D corresponding value is 37.

So now in the output there should not be any other record with having value 38 and 37 either in column A or D .ie. all those rows which contains these values other than this rows should be omitted.

Similarly for other rows and values also .I tried with the following query but didn't worked


Union two columns & If then conditions
When I have 2 columns in one table and into 3rd column I need to have their union (see example):
1st Column 2nd Column 3rd Column
ABC 123 ABC123

Please help how to solve it in sql query or also using VBA in Ms Access.

2. When I need to compare two columns -> process: at beginning compare first field in 1st column with all fields from 2nd column, then 2nd field in 1st column with all fields from 2nd column,.
Result should be True(field from 1st column match with any field from 2nd column)/False in new column for each field from 1st column


Use data in one column if another Column is Null
I am trying to do a simple calculation - subtracting one value in one column from another value in another column.

However, the curly bit is that the value I subtract from, comes from one of two columns. which column I use depends on whether the main column has data in it, if not then I use the secondary column.

So let's say Column A (the column I want to use UNLESS there is no data in it) is empty - in which case I would use column B.

I want my function to be something like [column A] - [Column C] (Or [Column B] - [Column C] (if Column A is Null)

ps. need to take into account that some cells might be null so must use either (isNull) or Nz


Adding Two Columns
I have two columns of data that I want to add together. When I add the columns, I get output only when both columns contain data. I want output for all of the rows even if both columns don't contain data.

This is what I am getting:
Column A Column B Column C
2 4 6
3
5
6 2 8

This is what I want:
Column A Column B Column C
2 4 6
3 3