adding a text string to data from one field in one database to another

[sellcraig]

Microsoft access
2 tables

table "data main" contains a field called "code"
table "ddw1" is created from a make table query of "data main"

Goal-
the data in "code" field in needs to be inserted into a standard web
address in the table (the filed name is link) in ddw1
Example address ---
"http://www.wedsite.com/[code].html"

to get this to work , what I did
I created a third table called webaddress using make table query with
only the field "code" from "data main"
I then went to the table and added field "link"
I then created update query using update to
"http:///www.webaddress.com/"& [code] &".html"
that did the merge of data

I then edited the make table query that creates "ddw1" to get the link
field from the webaddress table.

Ideally would like the field to be created when I do the create table
query so it is just one step to do everything.

since the field is not needed in where but table "ddw1" I do not want
to put it in "data main" also some data in code may change.

Any one who know how to do this ?

Thanks

[Bob Quintal]

se*******@aol.com wrote in
news:11**********************@74g2000cwt.googlegro ups.com:

>

Microsoft access
2 tables

table "data main" contains a field called "code"
table "ddw1" is created from a make table query of "data
main"

Goal-
the data in "code" field in needs to be inserted into a
standard web address in the table (the filed name is link) in
ddw1 Example address ---
"http://www.wedsite.com/[code].html"

[snipped]

>
Any one who know how to do this ?

Thanks

in the make table query, just past this into a field box in the
lower half of the design grid
link: "http://www.wedsite.com/" & [code] & ".html"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[sellcraig]

I tried it and nothing happened.

Bob Quintal wrote:

se*******@aol.com wrote in
news:11**********************@74g2000cwt.googlegro ups.com:

Microsoft access
2 tables

table "data main" contains a field called "code"
table "ddw1" is created from a make table query of "data
main"

Goal-
the data in "code" field in needs to be inserted into a
standard web address in the table (the filed name is link) in
ddw1 Example address ---
"http://www.wedsite.com/[code].html"

[snipped]

Any one who know how to do this ?

Thanks

in the make table query, just past this into a field box in the
lower half of the design grid
link: "http://www.wedsite.com/" & [code] & ".html"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[sellcraig]

Ignore my last reply, I did not look closley at what you had typed.
It worked as you put it.
Thanks

Two other things
1)
also another issue I came across is that all the data generated by
link: "http://www.wedsite.com/" & [code] & ".html"
need to be lower case
I can run an update query on the "code" field using StrConv(code, 2)
.. Can I also do it in the origional make table query ?

2) another field contains the exact same piece of information for
every record created. I would prefer to not waste a field in the
database when it is only needed for the table created . the info would
be "west2, west3" with the field name being "Locat" .
Net result - While doing create table query in the new table also add
a new field that does not exist anywhere in datebase containing the
data "west2, west3"



Bob Quintal wrote:

se*******@aol.com wrote in
news:11**********************@74g2000cwt.googlegro ups.com:

Microsoft access
2 tables

table "data main" contains a field called "code"
table "ddw1" is created from a make table query of "data
main"

Goal-
the data in "code" field in needs to be inserted into a
standard web address in the table (the filed name is link) in
ddw1 Example address ---
"http://www.wedsite.com/[code].html"

[snipped]

Any one who know how to do this ?

Thanks

in the make table query, just past this into a field box in the
lower half of the design grid
link: "http://www.wedsite.com/" & [code] & ".html"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

se*******@aol.com wrote in
news:11**********************@b28g2000cwb.googlegr oups.com:

Ignore my last reply, I did not look closley at what you had
typed. It worked as you put it.
Thanks

Glad it worked.

>
Two other things
1)
also another issue I came across is that all the data
generated by link: "http://www.wedsite.com/" & [code] &
".html" need to be lower case
I can run an update query on the "code" field using
StrConv(code, 2) . Can I also do it in the origional make
table query ?

sure!
link: "http://www.wedsite.com/" & StrConv([code], 2) & ".html"

2) another field contains the exact same piece of
information for every record created. I would prefer to not
waste a field in the database when it is only needed for the
table created . the info would be "west2, west3" with the
field name being "Locat" .
Net result - While doing create table query in the new table
also add
a new field that does not exist anywhere in datebase
containing the data "west2, west3"

If it contains the same information for every field, it does not
belong in the table.

If you need it for a query, do the same thing as for link
local: "west2, west3"

In a form or report, just put it in a label.

Bob Q

>

Bob Quintal wrote:

>se*******@aol.com wrote in
news:11**********************@74g2000cwt.googlegr oups.com:

>

Microsoft access
2 tables

table "data main" contains a field called "code"
table "ddw1" is created from a make table query of "data
main"

Goal-
the data in "code" field in needs to be inserted into a
standard web address in the table (the filed name is link)
in ddw1 Example address ---
"http://www.wedsite.com/[code].html"

[snipped]

>
Any one who know how to do this ?

Thanks

in the make table query, just past this into a field box in
the lower half of the design grid
link: "http://www.wedsite.com/" & [code] & ".html"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[sellcraig]

Thanks, your help has allowed me to eliminate a lot of waste in
database.

>From 27042kb to 3474 kb Is that a 92% reduction in size?

3 ISSUES

1 validation for code field
If I want the code field to contain only test or numbers - (NO symbols
- ie: /? -;+=space) and also text only in lower case only. The data
in the code field contains product codes of various lengths
I think there are two was I can think of this working,
1) Not permitting the text to be entered upon entry. using some
validation rule
2) Ignoring the non accepted info and correcting to lower case when
leaving field. Ie: wAsrH 12-4 becomes wasrh124

I looked at
Validation rule suspect- StrConv([code], 2) plus a function that
blocks anything but numbers or letters. ( I do not know which one)
Format do not know
Input mask do not know
I also want to make the field a unique field eliminating any duplicates
of the item code being entered twice.

2 export
I Create 2 tables using 2 different queries Table A and Table B (each
one with different data)
After running the create table query, I save Table A as text file, TAB
Delimited with the first line as field names. Once the text file is
created, table A is no longer needed.

After running the other create table query, I save Table B as text
file, COMMA Delimited with the first line as field names. Once the text
file is created, table B is no longer needed.
The purpose of each query is to ultimately create the corresponding
text files.
3 qty discounts
I have items with price breaks a various quantity levels each of the
data is in different fields on the spread sheet
Qty price at that qty price for that qty
1 16 16
20 15.20 304
50 14.78 739
100 14.33 1433
qtys may continue beyond this (or not -depending on item)

Currently I have this data in an Excel spreadsheet created based upon
calculations, sometimes manually changed.
In the database the data needs to be entered into a field (not in
$form) like so: 1 20 304 50 739 100 1433 and another field containing
the single qty pricing 16

the format is (qty 1 pricing)space (number on 2nd level
pricing)space(number on 2nd level pricing Times each price for that
level)space (number on 3rd level pricing)space(number on 3rd level
pricing Times each price for that level)space(number on 4th level
pricing)space(number on 4th level pricing Times each price for that
level)...

Can I automate the task of inputting this data from the spreadsheet to
the database?

Any ideas?
As always, help is much appreciated.




Bob Quintal wrote:

se*******@aol.com wrote in
news:11**********************@b28g2000cwb.googlegr oups.com:

Ignore my last reply, I did not look closley at what you had
typed. It worked as you put it.
Thanks

Glad it worked.

Two other things
1)
also another issue I came across is that all the data
generated by link: "http://www.wedsite.com/" & [code] &
".html" need to be lower case
I can run an update query on the "code" field using
StrConv(code, 2) . Can I also do it in the origional make
table query ?

sure!
link: "http://www.wedsite.com/" & StrConv([code], 2) & ".html"

2) another field contains the exact same piece of
information for every record created. I would prefer to not
waste a field in the database when it is only needed for the
table created . the info would be "west2, west3" with the
field name being "Locat" .
Net result - While doing create table query in the new table
also add
a new field that does not exist anywhere in datebase
containing the data "west2, west3"

If it contains the same information for every field, it does not
belong in the table.

If you need it for a query, do the same thing as for link
local: "west2, west3"

In a form or report, just put it in a label.

Bob Q

Bob Quintal wrote:

se*******@aol.com wrote in
news:11**********************@74g2000cwt.googlegro ups.com:

Microsoft access
2 tables

table "data main" contains a field called "code"
table "ddw1" is created from a make table query of "data
main"

Goal-
the data in "code" field in needs to be inserted into a
standard web address in the table (the filed name is link)
in ddw1 Example address ---
"http://www.wedsite.com/[code].html"

[snipped]

Any one who know how to do this ?

Thanks

in the make table query, just past this into a field box in
the lower half of the design grid
link: "http://www.wedsite.com/" & [code] & ".html"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

se*******@aol.com wrote in
news:11**********************@q16g2000cwq.googlegr oups.com:

Thanks, your help has allowed me to eliminate a lot of waste
in database.

>>From 27042kb to 3474 kb Is that a 92% reduction in size?

3 ISSUES

1 validation for code field
If I want the code field to contain only test or numbers - (NO
symbols - ie: /? -;+=space) and also text only in lower case
only. The data in the code field contains product codes of
various lengths I think there are two was I can think of this
working, 1) Not permitting the text to be entered upon
entry. using some validation rule
2) Ignoring the non accepted info and correcting to lower
case when leaving field. Ie: wAsrH 12-4 becomes wasrh124

I looked at
Validation rule suspect- StrConv([code], 2) plus a function
that blocks anything but numbers or letters. ( I do not know
which one) Format do not know
Input mask do not know
I also want to make the field a unique field eliminating any
duplicates of the item code being entered twice.

I strongly suggest UPPERCASE letters, for technical reasons...
I would put this in VB code in the textbox AfterUpdate event.

private sub ItemCode_AfterUpdate() as string
Dim stAllowedChars as string
dim stFrom as string stTo as string
dim iPtr as integer.

stAllowedChars ="ABCDEFGHJKLMNPRTUVWY0123456789"
'do not allow IOQSXZ, they can be confused with numbers.
stFrom = me.Itemcode.value

For iPtr = 1 to len(stFrom)
If instr(1,stAllowedChars,mid(stFrom,iptr,1))>0 then
stTo = stTo & ucase(mid(stFrom,iptr,1))
end if
next iPtr
me.Itemcode.value = stTo
end sub

2 export
I Create 2 tables using 2 different queries Table A and Table
B (each one with different data)
After running the create table query, I save Table A as text
file, TAB Delimited with the first line as field names. Once
the text file is created, table A is no longer needed.

After running the other create table query, I save Table B as
text file, COMMA Delimited with the first line as field names.
Once the text file is created, table B is no longer needed.
The purpose of each query is to ultimately create the
corresponding text files.

Just save the query as text file, you do not need to make table
in between.

>
3 qty discounts
I have items with price breaks a various quantity levels each
of the data is in different fields on the spread sheet
Qty price at that qty price for that qty
1 16 16
20 15.20 304
50 14.78 739
100 14.33 1433
qtys may continue beyond this (or not -depending on item)

Currently I have this data in an Excel spreadsheet created
based upon calculations, sometimes manually changed.
In the database the data needs to be entered into a field (not
in $form) like so: 1 20 304 50 739 100 1433 and another
field containing the single qty pricing 16

No, data in a related child table should be
Itemcode Qty Price TTLPrice
-------- ---- ----- --------
asd123 1 16 16
asd123 20 15.20 304
asd123 50 14.70 739
vbg007 1 22 22
vbg007 5 21 105

etc.
The reasons for this are many.

the format is (qty 1 pricing)space (number on 2nd level
pricing)space(number on 2nd level pricing Times each price for
that level)space (number on 3rd level pricing)space(number on
3rd level pricing Times each price for that level)space(number
on 4th level pricing)space(number on 4th level pricing Times
each price for that level)...

Can I automate the task of inputting this data from the
spreadsheet to the database?

Any ideas?

see above.

Q

As always, help is much appreciated.




Bob Quintal wrote:

>se*******@aol.com wrote in
news:11**********************@b28g2000cwb.googleg roups.com:

Ignore my last reply, I did not look closley at what you
had typed. It worked as you put it.
Thanks

Glad it worked.

>
Two other things
1)
also another issue I came across is that all the data
generated by link: "http://www.wedsite.com/" & [code] &
".html" need to be lower case
I can run an update query on the "code" field using
StrConv(code, 2) . Can I also do it in the origional make
table query ?

sure!
link: "http://www.wedsite.com/" & StrConv([code], 2) &
".html"

2) another field contains the exact same piece of
information for every record created. I would prefer to not
waste a field in the database when it is only needed for
the table created . the info would be "west2, west3" with
the field name being "Locat" .
Net result - While doing create table query in the new
table also add
a new field that does not exist anywhere in datebase
containing the data "west2, west3"

If it contains the same information for every field, it does
not belong in the table.

If you need it for a query, do the same thing as for link
local: "west2, west3"

In a form or report, just put it in a label.

Bob Q

>

Bob Quintal wrote:
se*******@aol.com wrote in
news:11**********************@74g2000cwt.googlegr oups.com:

Microsoft access
2 tables

table "data main" contains a field called "code"
table "ddw1" is created from a make table query of
"data main"

Goal-
the data in "code" field in needs to be inserted into a
standard web address in the table (the filed name is
link) in ddw1 Example address ---
"http://www.wedsite.com/[code].html"

[snipped]

Any one who know how to do this ?

Thanks

in the make table query, just past this into a field box
in the lower half of the design grid
link: "http://www.wedsite.com/" & [code] & ".html"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from
http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com
Warning: Do not use Ultimate-Anonymity
They are worthless spammers that are running a scam.

[sellcraig]

1 validation for code field
If I want the code field to contain only test or numbers - (NO
symbols - ie: /? -;+=space) and also text only in lower case
only. The data in the code field contains product codes of
various lengths I think there are two was I can think of this
working, 1) Not permitting the text to be entered upon
entry. using some validation rule
2) Ignoring the non accepted info and correcting to lower
case when leaving field. Ie: wAsrH 12-4 becomes wasrh124

I looked at
Validation rule suspect- StrConv([code], 2) plus a function
that blocks anything but numbers or letters. ( I do not know
which one) Format do not know
Input mask do not know
I also want to make the field a unique field eliminating any
duplicates of the item code being entered twice.

I strongly suggest UPPERCASE letters, for technical reasons...
I would put this in VB code in the textbox AfterUpdate event.

private sub ItemCode_AfterUpdate() as string
Dim stAllowedChars as string
dim stFrom as string stTo as string
dim iPtr as integer.

stAllowedChars ="ABCDEFGHJKLMNPRTUVWY0123456789"
'do not allow IOQSXZ, they can be confused with numbers.
stFrom = me.Itemcode.value

For iPtr = 1 to len(stFrom)
If instr(1,stAllowedChars,mid(stFrom,iptr,1))>0 then
stTo = stTo & ucase(mid(stFrom,iptr,1))
end if
next iPtr
me.Itemcode.value = stTo
end sub

What if I am not using forms?
and the data needs to be lower case with all characters because they
are part numbers and also will be part of an web address address and
some sites do not support capitals and lower case letters as the same.

[Bob Quintal]

se*******@aol.com wrote in
news:11**********************@e3g2000cwe.googlegro ups.com:

>

1 validation for code field
If I want the code field to contain only test or numbers -
(NO symbols - ie: /? -;+=space) and also text only in lower
case only. The data in the code field contains product
codes of various lengths I think there are two was I can
think of this working, 1) Not permitting the text to be
entered upon entry. using some validation rule
2) Ignoring the non accepted info and correcting to
lower case when leaving field. Ie: wAsrH 12-4 becomes
wasrh124

I looked at
Validation rule suspect- StrConv([code], 2) plus a
function that blocks anything but numbers or letters. ( I
do not know which one) Format do not know
Input mask do not know
I also want to make the field a unique field eliminating
any duplicates of the item code being entered twice.

I strongly suggest UPPERCASE letters, for technical
reasons... I would put this in VB code in the textbox
AfterUpdate event.

private sub ItemCode_AfterUpdate() as string
Dim stAllowedChars as string
dim stFrom as string stTo as string
dim iPtr as integer.

stAllowedChars ="ABCDEFGHJKLMNPRTUVWY0123456789"
'do not allow IOQSXZ, they can be confused with numbers.
stFrom = me.Itemcode.value

For iPtr = 1 to len(stFrom)
If instr(1,stAllowedChars,mid(stFrom,iptr,1))>0 then
stTo = stTo & ucase(mid(stFrom,iptr,1))
end if
next iPtr
me.Itemcode.value = stTo
end sub

What if I am not using forms?

How will you be entering data? If you intend to allow entry of
part numbers in the table, you are doing something dangerous.

If you are planning to import from a file generated outside of
Access, then place the code in a user defined function, and call
that function in a query

and the data needs to be lower case with all characters
because they are part numbers and also will be part of an web
address address and some sites do not support capitals and
lower case letters as the same.

because they are part numbers? The D.O.D. in the usa and NATO
High Command require part numbers in UPPERCASE only, with the
letters excluded as shown in my code.
Some sites? not many, as the web has evolved. If you still
insist, then add one function to the code to convert the string
to lowercase
Change from
me.Itemcode.value = stTo
To
me.Itemcode.value = StrConv([stTo], 2)


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com
Warning: Do not use Ultimate-Anonymity
They are worthless spammers that are running a scam.

[sellcraig]

not many?
they may be few, but they effect a lot
try these addresses
http://news.yahoo.com/s/nm/20060912/...whole_grain_dc
http://news.yahoo.com/s/nm/20060912/...whole_grain_dC
they do host business website accounts. It has the same effect.
I think the upper / lower case thing depends upon what OS the hosting
server uses.

se*******@aol.com wrote:

Microsoft access
2 tables

table "data main" contains a field called "code"
table "ddw1" is created from a make table query of "data main"

Goal-
the data in "code" field in needs to be inserted into a standard web
address in the table (the filed name is link) in ddw1
Example address ---
"http://www.wedsite.com/[code].html"

to get this to work , what I did
I created a third table called webaddress using make table query with
only the field "code" from "data main"
I then went to the table and added field "link"
I then created update query using update to
"http:///www.webaddress.com/"& [code] &".html"
that did the merge of data

I then edited the make table query that creates "ddw1" to get the link
field from the webaddress table.

Ideally would like the field to be created when I do the create table
query so it is just one step to do everything.

since the field is not needed in where but table "ddw1" I do not want
to put it in "data main" also some data in code may change.

Any one who know how to do this ?

Thanks