Spliting A Column Into Several In Access 97

Sep 10, 2004

I have a field in a table that looks like this:

hahafdhzuiwre8878878s
agahh87878jkjkkjk

etc...

there is no set length to the bits between the slashes.

I want to write a query that moves the data into a new table in three separate fields using the slashes as field separators.

Can anyone help ??

View Replies


ADVERTISEMENT

Spliting A Zip +4

Feb 5, 2008

Can someone show me how I can split the Zip+4 in this query, if it can be done at all?

SELECT
RTrim([CLIENT TABLE].CITY)+' , '+RTrim([CLIENT TABLE].ST)+' '+RTrim([CLIENT TABLE].ZIP) AS [ADD],
RTrim([CLIENT TABLE].FN)+' '+RTrim([CLIENT TABLE].LN) AS NAME,
[CLIENT TABLE].MEMBER, [CLIENT TABLE].PIN,
[CLIENT TABLE].A1, [CLIENT TABLE].A2,
[CLIENT TABLE].MEMBER_CASE_STATUS,
[CLIENT TABLE].RELATION_CODE INTO [CLIENT TABLE 2]
FROM [CLIENT TABLE];

Thanks

View 9 Replies View Related

Spliting Text

May 29, 2005

Hi, Would it be possible to get the first word from a sentance.

For example

Peter James would Return Peter.
John Michael would Return John etc.

The first word could range from 1 to 50 characters.

Regards

Pete

View 2 Replies View Related

Spliting Values In Field

Sep 29, 2005

Hi

I have the field fullname in my database which is in the format

Mr John Smith

What I have been trying to do is to extract the Mr then the John then the Smith into 3 separate columns. Can anbody help please?

Thank you

Marcus

View 3 Replies View Related

Problem In Spliting Table, Please Help

Sep 21, 2006

Hi!

The structure is like:

C query is based on 2 tables, A and B.

Then D form is created based on query C.

What I did:

Now I split table A to 2 tables, A1 and A2 which are indepandant from each other, and I updated query C to retrive data from A1 and A2.

Problem:

Then the form D which is based on C doesn't work properly.

Question:

Do I need to recreate a Query C because I split table A?
or do I need to recreate a From D because Query C is updated?

Thanks very much

View 2 Replies View Related

Spliting Numeric From Letters

Jan 14, 2005

i have a field called Items, description

Items Description

1021 125BL/WH
1022 5RD
1023 1458BL/BU/YL
1024 2500GR
1025 5GR


i want to be able to split the numeric from the color code. and add make a new field with the color code, and then have the quantity as a record.

Any Help is apreciated
Mark

View 14 Replies View Related

Problem In Updating Query After Spliting Table, Help Please

Sep 21, 2006

Hi!

The structure is like:

C query is based on 2 tables, A and B.

Then D form is created based on query C.

What I did:

Now I split table A to 2 tables, A1 and A2, which are independant from each other, and I updated query C to retrive data from A1 and A2.

Problem:

Then the form D which is based on C doesn't work properly.

Question:

Do I need to recreate a Query C because I split table A?
or do I need to recreate a From D because Query C is updated?

Thanks very much

View 4 Replies View Related

Split Access Column In Two...

Feb 23, 2005

I have a column in my table which contains DATE information in the format: 23/02/2005

I would like to split this date into two additional columns:

A) the MONTH as a number eg. 02
B) the YEAR also as a number eg. 2005

I know this is possible in Excel but can it be done in Access 97? :confused:

Cheers for your help!

View 1 Replies View Related

Column Permission In Access Table

Jan 4, 2005

Hi,

I have a table in Access database. I would like to set read only permissions to some of the fields/columns in the table. I do not want user to alter or edit some fields/columns in a table. Could someone give a solution for this?

thanks in adavance

Jayashankar

View 2 Replies View Related

Filling Data Down A Column In Access.

Jan 12, 2005

Is there something fast to fill down a new column into approx. 900 records with the same data similar to using Excel (Fill Down)?

View 6 Replies View Related

Create A Column Chart In Access

Jun 4, 2012

I am trying to create a chart in Access but havent done it before.

1). I have the following data:

Code
CountOfType
Month

Q
3
3/1/2012

K
1
2/1/2012

CDSA
1
2/1/2012

[code]....

2). I dont to create a chart where I am showing the transaction count (countoftype) in the Y axis and the code and bill_date in the Y. I want the chart to show the transaction by code and show what month it occurred. Would this be a crosstab query?

View 1 Replies View Related

Row Duplication Using Column List MS Access

May 28, 2015

I would like to use Access in order to duplicate the content of a list of values into new rows based on the content of a column.

Here is an example below:

List1
List2

A
1

B
2

C
3

D
4

E
5

F
6

G
7

Duplication

A1
A2
A3
A4
A5
A6
A7

B1
B2
B3
B4
B5
B6
B7
Etc

How can I achieve this in Access?

View 3 Replies View Related

Export Access Data Into Excel Column

Sep 13, 2005

Hello,

I have a table in Access and would like to export it using code into specific fielfs of a template in Excel.

My table has 3 fields:

SSN
FIRSTNAME
LNAME

I would like to export the recorsed to a template named MyTemplate. This template has a workbook named MyWorkbook.

The only problem is that I need to copy the active recorset (meaning the one which I will select) in a column and not into a row.

Example: I will select a record using a combo and then data will be copied from my Table into the cells B1 (ssn), B2 (FIRSTNAME), B3 (LASTNAME)

Any idea or help? Thanks

View 3 Replies View Related

Tables :: How To Use Countif And Column Reference In Access

Feb 3, 2015

Basically I have this formula in excel and i'm trying to recreate it in access but i can't quite figure it out.

****a***********b**********c*********d*********e** ******f
3**Car***Cost Center****start****combining*****end*****miles
4**001*****aaa********021****=(A4&" "&C4)***030
5**002*****bbb********030****=(A5&" "&C5)***035
6**002*****aaa********035****=(A6&" "&C6)***040

f4: =IF(COUNTIF($D4:$D600,D4)=0,(E4-C4),(E4-C4)/COUNTIF($D4:$D600,D4))

f5: =IF(COUNTIF($D5:$D601,D5)=0,(E5-C5),(E5-C5)/COUNTIF($D5:$D601,D5))

f6: =IF(COUNTIF($D5:$D601,D5)=0,(E5-C5),(E5-C5)/COUNTIF($D5:$D601,D5))

So basically: If a car is using multiple cost centers for a trip it divides the total mileage among the cost center for said trip provided that the user enters the same start and end mileage on all the cost centers used. And if it doesn't have multiple cost centers it provides the appropriate amount.

=IF(COUNTIF($D4:$D600,D4)
=IF(COUNTIF($D4:$D600,D5)
=IF(COUNTIF($D4:$D600,D6)

and so on

Any way to reference a column in access while referencing a specific point within that column.

View 2 Replies View Related

Modules & VBA :: MS Access Insert Into SQL - Add Timestamp Column

Jan 12, 2015

I have the below SQL statement... In table2 there is another field called timestamp1... Is it possible to have the timestamp1 included in the below statement so that I will have a record of time the moment the records were inserted in table2? There is no timestamp1 field in table1.

Code:
strSQL = "Insert Into Table2(Business_Unit, Account) Select Business_Unit, Account From Table1"

View 1 Replies View Related

Modules & VBA :: How To Delimit Column Into Several Columns In Access

Nov 24, 2014

I have an imported table and within this table contains a column that needs to be further delimited. I've read that it is possible to delimit columns using the left, right and mid functions, however, I need to delimit it to more than 3 columns. Maybe an estimated 6.

Examples of the data contained in the column are as follow:
ITM~W01GGASPAPP1B:W6400~12.34.56.78~~W01GGASPAPP1B ~W01GGASPAPP1B~ACK~

ITM~a01gibapp3a:AC900~12.34.56.78~~a01gibapp3a~a01 gibapp3a~ACK~

TEC~~01.234.567.89~~~~ACK~

I need to delimit it by "~". It must also be similar to Excel's text-to-column feature whereby if there're no values between 2 "~", it will be recorded as null.

The main thing I require from each record in the column is contained within the first and second "~" (even if it's null).

View 3 Replies View Related

Converting Matrix To Column In Access 2010

Aug 13, 2012

I'm trying to convert a matrix within a table (m x n) to a single column. So basically

1 2 3
4 5 6
7 8 9

to

1
2
3
4

5
6
7
8
9

I would do this in excel, but the problem is I have already over 1 million rows, and I'm converting 48 columns into one (too much for excel). Is there a way to do this via a macro or query?

The other catch is that I want to repeat Column A (which has the username) with each row of data, for example

Joe 1 2 3
Sam 4 5 6
Jeff 7 8 9

to

Joe 1
Joe 2
Joe 3
Sam 4
Sam 5
Sam 6
Jeff 7
Jeff 8
Jeff 9

View 4 Replies View Related

Access Report Views A New Column The Same As A New Page?

Jun 22, 2015

I have run a query that reports the following information. I am using Access 2007

Model (Text)
Start Time (Time)
Step (1-5)

I have a report that is grouped by step and I have Force new page selected for this group. So the idea is that each page will show the models and start times for each step on separate pages.

Since the width of what I am showing is not very wide I want two columns so that when there is too much data the data will just continue in the next column rather than create a new page. This way all the information for each step will be contained on one page.

I am running into problems when there isn't enough data for a given step to fill up the entire first column. Access is placing the next data set (Step #2 for example) in the next column rather than on its own page. Does access view a new column as a new page? I thought that selecting "Force new page" for my group would force a new physical page and not just dump the next data set into the next column.

View 3 Replies View Related

Transferring Spreadsheet From Excel To Access. Problem With Column Name

Nov 15, 2007

hi
i am transferring spreadsheet from Excel to Access table


pathbgc = Path & "X.xls"
DoCmd.TransferSpreadsheet acImport, , "tableX", pathbgc, vbYes


pathbgc = Path & "Y.xls"
DoCmd.TransferSpreadsheet acImport, , "tableY", pathbgc, vbYes



This works fine as long as column names in Excel do not have periods. (.)
Other then asking whoever is sending this not to put periods, is there painless way ignore that fact that excel has it, un just do it without it ?

View 7 Replies View Related

Queries :: Column Total In Access 2003 Query

Apr 19, 2015

how to have our Access 2003 query total our report column "ProfitLoss`and produce on our report a Total Value of -$420.17

View 13 Replies View Related

Tables :: MS Access Setting Relationship And Bound Column

Jun 14, 2013

I have three tables.

Table 1: Group

Field 1: Group Text field ( Primary key)
Field 2: Group Description Text field
Field 3: Uidgroup( Autonumber)

Table 2: Subgroup Text field

Field 1: Group ( I want to bound this column to Table 1's Group field that is column 1) I have set bound column property to 1 and column count 1 and the Subgroup table is showing group fields as input perfectly no issues in that )

Field 2: Subgroup, Text field( Primary Key )

Field 3: uidsubg( Autonumber)

Table 3: Email

Field 1: Group ( Bound to Table1's Group ; showing values in combo box, setted bound column property to 1 and showing group field perfectly, no issue in that )

Field 2: Subgroup (I want to bound Table2's subgroup field, which is column number 2, so I wrote 2 in bound column property and row source is table subgroup ; Here is some error comes up, values from subgroup field of subgroup table not being shown up in Email Table's subgroup field as combo box. )

Field 3: Email Text field

Field 4: uideml (Autonumber ) primary key

I want to prepare a Data entry form should have all these fields from all the tables. That should work in following way, first user selects Group then User selects Sub Group and write Email and save the record.

What relationship should I set, or shall I change the table structure.

View 1 Replies View Related

Modules & VBA :: Lookup Column In Text Box Access Form

Apr 5, 2015

I have 1 combo box and 1 text box i look up 2 columns in the combo box from that combo box i want to look up 2 column to text box

example:
Table values:
Col 1 Col 2
A 1
A 2

combo box successfully look up 2 columns but i look up to text box

Formula: =combo1.column(1)

But the text box look up the first row always even i choose the second row A

Also look-up first row 1

Any solution to look up 2nd row?

View 3 Replies View Related

Create A Table In MS Access - Name In First And Validation Dates In Second Column

Feb 24, 2014

How to create a table in MS Office. Current table has name in first column and start & finish dates in other two columns. It is necessary to create a table in MS Access which has name in the first column and validation dates in the second column. For Example:

currently

1. White 16 xxxx19
2. Black 1 xxxx 5

required

1. White 17
1. White 18
2. Black 2
2. Black 3
2. Black 4

View 1 Replies View Related

Split Data In A Column In Excel Via VBA Code In Access

Jun 6, 2012

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. How do I write code to accomplish this in a module in VBA.

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.

View 2 Replies View Related

How To Avoid Duplicate Column Values In Access Reports

Dec 31, 2010

I have a report which has say two columns - staff number and visit date. For a staff number (which is unique) there may be more than one visit date. But I want to list a staff number only once and not multiple times in the column (but want all the visit dates of course). How can I do that?

View 14 Replies View Related

Find Exact Match In Access Table Column

Oct 12, 2013

How to find an exact match in a Access DB table using Sql Query in VB6 ?I know that "Like" keyword will give out all those rows which contain the search-for-string. But I want exact match.

View 4 Replies View Related







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