do you know dBase and Access?

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Hi, I guess you know dBase and Access since you are reading this post ;)

I have some small command files (.prg) that I've written in dBase and now am
looking how to do the same procedures in Access 2002. Below is a sample of
one of these command files. If you could help me get started with getting
Access to do this function, I'd be ever so grateful.
------------
use c:\dbase\sales\hist05 exclu
replace all market with 'A1' for (territ>='13'.and.territ<='18').and.
(commcode='0062' or commcode='0066')
replace all market with 'A2' for (territ>='13'.and.territ<='18').and.
(commcode='0063' or commcode='0044)
replace all market with 'A3' for (territ>='13'.and.territ<='18').and.
(commcode='0034' or commcode='0037')
replace all market with 'A4' for (territ>='13'.and.territ<='18').and.
(commcode='0020' or commcode='0021')
 
I would backup my database first.
Then create an update table like --
Field1 Field2 Field3 Field4
A1 13 18 0062
A1 13 18 0066
A2 13 18 0044
A2 13 18 0063
A3 13 18 0034
A3 13 18 0037
A4 13 18 0020
A4 13 18 0020

Join your table and the Update table on the common fields that contain A1,
A2, A3, etc. Enter criteria directly (if they are all >=13 and <=18) or
reference the update table values. Enter update as [Field4] in the Update To
column of the design grid.
 
The equivalent SQL in Access for the first example would be:

"UPDATE hist05 SET market='A1' WHERE territ BETWEEN '13' AND '18' AND
(commcode='0062' OR commcode='0066')"
 
Hi, I guess you know dBase and Access since you are reading this post ;)

I have some small command files (.prg) that I've written in dBase and now am
looking how to do the same procedures in Access 2002. Below is a sample of
one of these command files. If you could help me get started with getting
Access to do this function, I'd be ever so grateful.
------------
use c:\dbase\sales\hist05 exclu
replace all market with 'A1' for (territ>='13'.and.territ<='18').and.
(commcode='0062' or commcode='0066')
replace all market with 'A2' for (territ>='13'.and.territ<='18').and.
(commcode='0063' or commcode='0044)
replace all market with 'A3' for (territ>='13'.and.territ<='18').and.
(commcode='0034' or commcode='0037')
replace all market with 'A4' for (territ>='13'.and.territ<='18').and.
(commcode='0020' or commcode='0021')
Click to expand...

dBase is one good database program.
Access is A DIFFERENT good database program.

Access is not a flawed implementation of dBase. It is different, and
requires that you UNLEARN "the way you always do things" and learn a
new way. It may be worthwhile to work through some of the suggestions
in Jeff Conrad's terrific resources page

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

and to perhaps study the Northwind sample database which comes with
Access. You'll find that the whole mindset is quite different - far
more SQL queries, far less programming, very rarely creating new
tables, etc.

YOu would accomplish these actions with one or more Update queries; if
(as it appears) each value of Commcode requires a different new value
of Market, I'd suggest creating a Table tblTrans with the
correspondence, e.g.

Commcode Market
0062 A1
0066 A1
0063 A2
0044 A2
0034 A3
.... <etc>

Make Commcode the Primary Key of this table; and then run a single
Update query:

UPDATE [Hist05] INNER JOIN [tblTrans]
ON tblTrans.Commcode = Hist05.Commcode
SET Hist05.Market = tblTrans.Market
WHERE Hist05.Territ >= '13' AND Hist05.Territ <= '18';

John W. Vinson[MVP]


John W. Vinson[MVP]
 
Just as a note, the code you write would look like:

currentdb.Execute "update hist05 set market = 'A1' where " & _
"(territ > = '13' and territ<= '18') and " & _
"(commcode = '0062' or commcode = '0066')"

Note carefull use of the "quotes". Further, if territ is actauly a
"number" field, and not a "text" field, then you need to remove
the quotes. The below example assume that both territ and
commcode are number type fields. So:

currentdb.Execute "update hist05 set market = 'A1' where " & _
"(territ > = 13 and terri t<= 18) and " & _
"(commcode = 62 or commcode = 66)"
 
Back
Top