l******n
发帖数: 9344 | 1 a table(15k records) with codes to indicate state. now I have to compare it
with another table, so need to convert all state codes to state
abbreviations.
any quick way to do it? thanks | x****y
发帖数: 252 | | l******n
发帖数: 9344 | 3 My case is a little complicated
because in some data, the state code is abbreviation. So it is mixed up,like
08 CA 21
99 TX LA NY 01
BTW, another question is if one column is Yes/No, how to count the number of
Yes?
Thanks
【在 x****y 的大作中提到】
: any quick way to do it?
| B*****g
发帖数: 34098 | 4 不明白。
like
of
【在 l******n 的大作中提到】
: My case is a little complicated
: because in some data, the state code is abbreviation. So it is mixed up,like
: 08 CA 21
: 99 TX LA NY 01
: BTW, another question is if one column is Yes/No, how to count the number of
: Yes?
: Thanks
| l******n
发帖数: 9344 | 5 OK, Beijing
This is my problem.
Table 1 has Year, STATES, Event. STATES may contain many states and use abv,
event contains many events
Table 2 has Year, STATE1, STATE2,..., STATE7, Event1,...,Event10. But STATE1
is using abv, but STATE2 use code, which has to use table 3 to know the abv
. Each state1(to 7) only contains 1 state or nothing.
Table 3 is Statecode, State.
now I want to know how the relationship between table 1 and table 3.
I want to know in year 2008 and 2007, in each state how man
【在 B*****g 的大作中提到】
: 不明白。
:
: like
: of
| n********6
发帖数: 1511 | 6 Is it for production? Or just for your research?
If it is just for your research, change the full state name to abv and put
them in a temp table might be a way with clear logic for later process.
abv,
STATE1
abv
【在 l******n 的大作中提到】
: OK, Beijing
: This is my problem.
: Table 1 has Year, STATES, Event. STATES may contain many states and use abv,
: event contains many events
: Table 2 has Year, STATE1, STATE2,..., STATE7, Event1,...,Event10. But STATE1
: is using abv, but STATE2 use code, which has to use table 3 to know the abv
: . Each state1(to 7) only contains 1 state or nothing.
: Table 3 is Statecode, State.
: now I want to know how the relationship between table 1 and table 3.
: I want to know in year 2008 and 2007, in each state how man
| B*****g
发帖数: 34098 | 7 好像有点复杂。
state3-7是word，还是code？
你用那个DB？ version？
abv,
STATE1
abv
are
【在 l******n 的大作中提到】
: OK, Beijing
: This is my problem.
: Table 1 has Year, STATES, Event. STATES may contain many states and use abv,
: event contains many events
: Table 2 has Year, STATE1, STATE2,..., STATE7, Event1,...,Event10. But STATE1
: is using abv, but STATE2 use code, which has to use table 3 to know the abv
: . Each state1(to 7) only contains 1 state or nothing.
: Table 3 is Statecode, State.
: now I want to know how the relationship between table 1 and table 3.
: I want to know in year 2008 and 2007, in each state how man
| l******n
发帖数: 9344 | 8 15k data!
【在 n********6 的大作中提到】
: Is it for production? Or just for your research?
: If it is just for your research, change the full state name to abv and put
: them in a temp table might be a way with clear logic for later process.
:
: abv,
: STATE1
: abv
| l******n
发帖数: 9344 | 9 state 1- 7 all use text, but some of them are abv, some are codes. And the
positions are not fixed.
I am using access and it is the only one I can use.
【在 B*****g 的大作中提到】
: 好像有点复杂。
: state3-7是word，还是code？
: 你用那个DB？ version？
:
: abv,
: STATE1
: abv
: are
| B*****g
发帖数: 34098 | 10 cft。
这里都是大牛，每天搞得一个table数据都是几十至上百个Millions。
Access可能难搞一些，我先看看。
put
【在 l******n 的大作中提到】
: 15k data!
| B*****g
发帖数: 34098 | 11 想了想，还是再建个table吧。
Table4
Table4 has ID, Year, STATES, Event, STATES_MIX
(ID is autonumber)
INSERT INTO Table4 (Year, Event, STATES_MIX)
SELECT Year, Event(1-10), STATE(1-7)
WHERE Event(1-10) is not null
AND STATE(1-7) is not null
***70个insert
Option: delete the dup records in table4
Table4 create index on state_mix
UPDATE table4 t4 inner join table3 t3 on t4.state_mix = t3.Statecode set t4.
state = t3.state.
UPDATE table4 t4 inner join table3 t3 on t4.state_mix = t3.State set t4.stat
e = t3.s
【在 l******n 的大作中提到】
: state 1- 7 all use text, but some of them are abv, some are codes. And the
: positions are not fixed.
: I am using access and it is the only one I can use.
| l******n
发帖数: 9344 | 12 就是就是,呼唤大牛现身
【在 B*****g 的大作中提到】
: cft。
: 这里都是大牛，每天搞得一个table数据都是几十至上百个Millions。
: Access可能难搞一些，我先看看。
:
: put
| j*****n
发帖数: 1781 | 13 不好整，先解决 table1 再说其他：
In States column of table1, the states are delimited by space, so you may
have to come out a string process to read characters one by one, from begin
to the end. put each state as a element of an array. |
|
