f



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


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

0
sellcraig (6)
8/29/2006 11:22:48 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

9 Replies
904 Views

Similar Articles

[PageSpeed] 52

sellcraig@aol.com wrote in
news:1156893768.307146.106880@74g2000cwt.googlegroups.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

0
rquintal (987)
8/29/2006 11:20:42 PM
  I tried it and nothing happened.

Bob Quintal wrote:
> sellcraig@aol.com wrote in
> news:1156893768.307146.106880@74g2000cwt.googlegroups.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

0
sellcraig (6)
8/31/2006 5:56:16 PM
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:
> sellcraig@aol.com wrote in
> news:1156893768.307146.106880@74g2000cwt.googlegroups.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

0
sellcraig (6)
8/31/2006 6:21:31 PM
sellcraig@aol.com wrote in
news:1157048491.801970.213020@b28g2000cwb.googlegroups.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:
>> sellcraig@aol.com wrote in
>> news:1156893768.307146.106880@74g2000cwt.googlegroups.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

0
rquintal (987)
8/31/2006 8:58:10 PM
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:
> sellcraig@aol.com wrote in
> news:1157048491.801970.213020@b28g2000cwb.googlegroups.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:
> >> sellcraig@aol.com wrote in
> >> news:1156893768.307146.106880@74g2000cwt.googlegroups.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

0
sellcraig (6)
9/10/2006 10:09:28 PM
sellcraig@aol.com wrote in
news:1157926168.235881.183950@q16g2000cwq.googlegroups.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:
>> sellcraig@aol.com wrote in
>> news:1157048491.801970.213020@b28g2000cwb.googlegroups.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:
>> >> sellcraig@aol.com wrote in
>> >> news:1156893768.307146.106880@74g2000cwt.googlegroups.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.

0
rquintal (987)
9/10/2006 10:20:45 PM
> > 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.

0
sellcraig (6)
9/11/2006 12:58:11 AM
sellcraig@aol.com wrote in
news:1157936291.383155.321180@e3g2000cwe.googlegroups.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.

0
rquintal (987)
9/11/2006 9:30:35 AM
not many?
they may be few, but they effect a lot
try these addresses
http://news.yahoo.com/s/nm/20060912/hl_nm/whole_grain_dc
http://news.yahoo.com/s/nm/20060912/hl_nm/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.



sellcraig@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

0
sellcraig (6)
9/13/2006 7:47:54 AM
Reply: