appending

[Guest]

I have this file where it appends to a table.
one of the columns, i need to get rid of some data
what i need to get rid of is the first 3 letters that are in this column
example of what the column looks like

"LOC 12345678"

it alway have LOC in the begining, followed by 8 digits. please help

 

[Guest]

Try this

Mid(FieldName,4)

 

[Guest]

It will be better to use that
Trim(mid(fieldName,4))

That way it will remove the space in the beggining.

 

[Guest]

Hi, Justin.

I have this file where it appends to a table.
one of the columns, i need to get rid of some data
what i need to get rid of is the first 3 letters

You can use an append query that prevents the unwanted text in the file from
being appended to the table. For example:

INSERT INTO tblTextImport ( FullName, Address )
SELECT FullName, LTRIM(MID(Address, 4, Len(Address) - 3)) AS ShortAddr
FROM [TEXT;DATABASE=C:\Work\].Import.txt;

.... where Address is the field that needs the first three characters
eliminated, tblTextImport is the name of the table that the records are
appended to, and C:\Work\Import.txt is the text file's path and file name
where the records come from, and the text file contains headers for the
FullName and Address columns.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[John Vinson]

I have this file where it appends to a table.
one of the columns, i need to get rid of some data
what i need to get rid of is the first 3 letters that are in this column
example of what the column looks like

"LOC 12345678"

it alway have LOC in the begining, followed by 8 digits. please help

Use a calculated field

Mid([fieldname], 4)

to extract the portion of the string starting with the fourth
character.


John W. Vinson[MVP]

 

[Guest]

ok, i have another question

In my temp table, an example i have a problem is this

cash checks cash Pull
$100 $4000 $3000
$4000 $2000
$200 $2000 $1500
$200 $2000 $1430

now, i want to append to the main table where it would take
cash, checks and cash pull, cash pull 2, cash pull 3, cash pull 4 as columns
and then append the data as follow

cash checks cash pull, cash pull 2, cash pull 3, cash pull 4
100 4000 3000 2000
200 2000 1500 1430

im looking at somethin like this

Hi, Justin.

I have this file where it appends to a table.
one of the columns, i need to get rid of some data
what i need to get rid of is the first 3 letters

You can use an append query that prevents the unwanted text in the file from
being appended to the table. For example:

INSERT INTO tblTextImport ( FullName, Address )
SELECT FullName, LTRIM(MID(Address, 4, Len(Address) - 3)) AS ShortAddr
FROM [TEXT;DATABASE=C:\Work\].Import.txt;

... where Address is the field that needs the first three characters
eliminated, tblTextImport is the name of the table that the records are
appended to, and C:\Work\Import.txt is the text file's path and file name
where the records come from, and the text file contains headers for the
FullName and Address columns.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I have this file where it appends to a table.
one of the columns, i need to get rid of some data
what i need to get rid of is the first 3 letters that are in this column
example of what the column looks like

"LOC 12345678"

it alway have LOC in the begining, followed by 8 digits. please help

 

[Guest]

Hi, Justin.

Am I right in assuming that the data currently in the temp table is from
repeated appends of data from the external file? Please let me know.

If I understand your external data structure, your table data structure and
your desired data structure, then I agree with you. You have a problem.
Fortunately, it's not too hard to fix, as long as you haven't built a lot of
your database objects that rely on this current structure.

It appears to me that the external data structure has multiple columns for
"Cash Pull," which perhaps is to identify which person, office, store, et
cetera, pulled the cash. Regardless of which column of these four columns
it's currently located in, the amount should be stored in your table in one
only column, "Amount," and the identifier for who/where it was pulled should
be in another column, named something such as "TypeCapital," with the name
"Steve" or "Main Office" or "Valencia," et cetera, as the value for that
record. That way, the actual cash amounts and check amounts could be stored
in two columns, too: the amount in the Amount column and the word "Cash" or
"Check" in the "TypeCapital" column, as appropriate.

In other words, you don't want to expand the number of columns, you want to
expand the number of records in order to structure the data so that you can
create very powerful, accurate queries that provide meaningful information to
your business from a bunch of data.

The data would be stored much like this:

Amount TypeCapital LogDate
$100 Cash 9/27/2005
$4000 Checks 9/27/2005
$3000 Cash pull - Valencia 9/27/2005
$2000 Cash pull - Malibu 9/27/2005
$200 Cash 9/28/2005
$2000 Checks 9/28/2005
$1500 Cash pull - Valencia 9/28/2005
$1430 Cash pull - Malibu 9/28/2005

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

ok, i have another question

In my temp table, an example i have a problem is this

cash checks cash Pull
$100 $4000 $3000
$4000 $2000
$200 $2000 $1500
$200 $2000 $1430

now, i want to append to the main table where it would take
cash, checks and cash pull, cash pull 2, cash pull 3, cash pull 4 as columns
and then append the data as follow

cash checks cash pull, cash pull 2, cash pull 3, cash pull 4
100 4000 3000 2000
200 2000 1500 1430

im looking at somethin like this

Hi, Justin.

I have this file where it appends to a table.
one of the columns, i need to get rid of some data
what i need to get rid of is the first 3 letters

You can use an append query that prevents the unwanted text in the file from
being appended to the table. For example:

INSERT INTO tblTextImport ( FullName, Address )
SELECT FullName, LTRIM(MID(Address, 4, Len(Address) - 3)) AS ShortAddr
FROM [TEXT;DATABASE=C:\Work\].Import.txt;

... where Address is the field that needs the first three characters
eliminated, tblTextImport is the name of the table that the records are
appended to, and C:\Work\Import.txt is the text file's path and file name
where the records come from, and the text file contains headers for the
FullName and Address columns.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I have this file where it appends to a table.
one of the columns, i need to get rid of some data
what i need to get rid of is the first 3 letters that are in this column
example of what the column looks like

"LOC 12345678"

it alway have LOC in the begining, followed by 8 digits. please help

 

[Guest]

yes u are correct
I was told that i should also try to do a cross tab query, but unfor, when
the heading is expanding, i would like to just keep it simple.
or do you recommend some other alternative way

Hi, Justin.

Am I right in assuming that the data currently in the temp table is from
repeated appends of data from the external file? Please let me know.

If I understand your external data structure, your table data structure and
your desired data structure, then I agree with you. You have a problem.
Fortunately, it's not too hard to fix, as long as you haven't built a lot of
your database objects that rely on this current structure.

It appears to me that the external data structure has multiple columns for
"Cash Pull," which perhaps is to identify which person, office, store, et
cetera, pulled the cash. Regardless of which column of these four columns
it's currently located in, the amount should be stored in your table in one
only column, "Amount," and the identifier for who/where it was pulled should
be in another column, named something such as "TypeCapital," with the name
"Steve" or "Main Office" or "Valencia," et cetera, as the value for that
record. That way, the actual cash amounts and check amounts could be stored
in two columns, too: the amount in the Amount column and the word "Cash" or
"Check" in the "TypeCapital" column, as appropriate.

In other words, you don't want to expand the number of columns, you want to
expand the number of records in order to structure the data so that you can
create very powerful, accurate queries that provide meaningful information to
your business from a bunch of data.

The data would be stored much like this:

Amount TypeCapital LogDate
$100 Cash 9/27/2005
$4000 Checks 9/27/2005
$3000 Cash pull - Valencia 9/27/2005
$2000 Cash pull - Malibu 9/27/2005
$200 Cash 9/28/2005
$2000 Checks 9/28/2005
$1500 Cash pull - Valencia 9/28/2005
$1430 Cash pull - Malibu 9/28/2005

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

ok, i have another question

In my temp table, an example i have a problem is this

cash checks cash Pull
$100 $4000 $3000
$4000 $2000
$200 $2000 $1500
$200 $2000 $1430

now, i want to append to the main table where it would take
cash, checks and cash pull, cash pull 2, cash pull 3, cash pull 4 as columns
and then append the data as follow

cash checks cash pull, cash pull 2, cash pull 3, cash pull 4
100 4000 3000 2000
200 2000 1500 1430

im looking at somethin like this

Hi, Justin.

I have this file where it appends to a table.
one of the columns, i need to get rid of some data
what i need to get rid of is the first 3 letters

You can use an append query that prevents the unwanted text in the file from
being appended to the table. For example:

INSERT INTO tblTextImport ( FullName, Address )
SELECT FullName, LTRIM(MID(Address, 4, Len(Address) - 3)) AS ShortAddr
FROM [TEXT;DATABASE=C:\Work\].Import.txt;

... where Address is the field that needs the first three characters
eliminated, tblTextImport is the name of the table that the records are
appended to, and C:\Work\Import.txt is the text file's path and file name
where the records come from, and the text file contains headers for the
FullName and Address columns.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

I have this file where it appends to a table.
one of the columns, i need to get rid of some data
what i need to get rid of is the first 3 letters that are in this column
example of what the column looks like

"LOC 12345678"

it alway have LOC in the begining, followed by 8 digits. please help