Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2010)
一つのセルにコロン(:)で区切られた複数の値を置換したい。
投稿日時: 17/11/11 11:42:07
投稿者: 馬車道停留所

こんにちは
 
件名に関する質問です。
下記の2つの選択クエリがあります。
AフィールドCは、フィールドBの値そのものをフォーマットして表示しております。
この、フィールドCの値を、@のフィールドCの値で置き換える方法をご教授ください。
 
@グループ(選択クエリ)
フィールドA: 所属コード
フィールドB: グループ名
フィールドC: 所属
 
例:@の入力値
A: 111
B: kanto123_12
C: 横浜
 
A登録済みグループ(選択クエリ)
フィールドA:アカウント
フィールドB: 所属グループ
フィールドC: 登録済みグループ: Format([所属グループ],"<")
 
例:Aの入力値
A: 1234567
B: kanto123:kanto123_12:kanto:123_23:中部_234_23
C: kanto123:kanto123_12:kanto:123_23:中部_234_23
 
AのフィールドCの値は、@のフィールドBに登録されている所属グループの値です。
AのフィールドCの値は、1つのこともありますし、3つ、4つ、5つと、複数ある場合もありますし、また、ない場合もあります。
最後の所属グループの値の後ろには、コロンはつきません。入力値がない場合も、コロンはつきません。
中部_234_23は、chubu_234という登録もあり、ローマ字と漢字が入り混じていますが、全て、@のフィールドBに登録されているものです。
 
AのフィールドCの処理後のイメージです。
C: 関東:戸塚:湘南:名古屋
 
このように処理する方法をご教授お願いします。
 
 
 

回答
投稿日時: 17/11/11 22:48:00
投稿者: mayu.

UPDATE文で実現する方法をご紹介します。
( 回答の都合上、選択クエリはテーブルに置き換えます )
 
■ テーブル定義

CREATE TABLE T_所属
(
      所属コード INT            NOT NULL PRIMARY KEY
    , グループ名 VARCHAR( 50 )  NOT NULL
    , 所属       VARCHAR( 100 ) NOT NULL
);

CREATE TABLE T_入力
(
      アカウント   VARCHAR( 50 ) NOT NULL PRIMARY KEY
    , 所属グループ VARCHAR( 255 )
    , 所属地区     VARCHAR( 255 )
);

 
■ データ例
< T_所属 >
----------------------------------------
 所属コード  グループ名        所属
----------------------------------------
    100          kanto123	  関東
    111          kanto123_12	  横浜
    112          kanto:123_23	  川崎
    200          中部_234         中部
    205          中部_234_23    名古屋
    207          中部_234_15	  戸塚
    209          中部_234_1       湘南

< T_入力 >
---------------------------------------------------------------------
 アカウント               所属グループ                     所属地区
---------------------------------------------------------------------
  0246855   中部_234:中部_234_15:中部_234_23:中部_234_1    ( Null )
  1234567   kanto123:kanto123_12:kanto:123_23:中部_234_23  ( Null )
  9876543   中部_234                                       ( Null )
  9999999   ( Null )                                       ( Null )

 
■ SQL ( 順番に実行 )
UPDATE
(
    SELECT y.アカウント
         , InStr( 1, ':' & y.所属グループ & ':', ':' & x.グループ名 & ':', 1 ) As num
         , x.所属
         , y.所属地区
    FROM T_所属  x
       , T_入力  y
    WHERE InStr( 1, ':' & y.所属グループ & ':', ':' & x.グループ名 & ':', 1 ) > 0
    ORDER BY y.アカウント
           , InStr( 1, ':' & y.所属グループ & ':', ':' & x.グループ名 & ':', 1 )
) q
SET 所属地区 = 所属地区 & ':' & 所属 ;

UPDATE T_入力
   SET 所属地区 = Mid$( 所属地区, 2 )
 WHERE 所属地区 Is Not Null ;

 
■ 結果
< T_入力 >
---------------------------------------------------------------------------------
 アカウント                所属グループ                         所属地区
---------------------------------------------------------------------------------
  0246855   中部_234:中部_234_15:中部_234_23:中部_234_1    中部:戸塚:名古屋:湘南
  1234567   kanto123:kanto123_12:kanto:123_23:中部_234_23  関東:横浜:川崎:名古屋
  9876543   中部_234                                       中部
  9999999   ( Null )                                       ( Null )

投稿日時: 17/11/16 23:45:35
投稿者: 馬車道停留所

mayuさん
 
ご回答ありがとうございました。
 
その後、両方のテーブルを作成クエリで作成しました。
質問なのですが、所属テーブルの所属コードは、主キーでないといけないでしょうか。
 
質問の理由は、所属テーブルの所属コード(重複あり)が、主キーになりえなかったので、次の処理を行ったところ、Fつめのステップで、エラー(インデックス、主キー、またはリレーションシップで値が重複しているのでテーブルを変更できませんでした。)が出てしまったからです。
 
数字のナンバリング上は、一見、重複がないようにみえるが、重複があると表示されるので、エクセルに番号フィールドの値350件ほどを、コピーして、重複を削除すると11の重複があり、削除されます。
 
原因は、現在調査中です。
グループ名は、唯一無二なので、主キーに設定できるかと思い、主キーに設定したのですが、リレーションシップとかが重複するので、主キーに設定できないというようなエラーが出ました。
 
なお、アカウントは、テキスト型で、主キーに設定できます。
 
【処理の内容】
@所属テーブル作成に用いたテーブル作成クエリを、追加クエリに変更
A追加クエリのグループ名(重複なし)を、DCount関数で、カウントした番号フィールドを作成
Bテキスト型から数字型に変換するために、Val関数で、Dcount関数を更にくくる
C番号のプロパティの書式に、000と入力し、数字型に変更
D追加クエリを実行し、所属テーブルに、全てのレコードを入力
E所属テーブルのデザインビューで番号を数値型にし、書式を000に設定
F番号を主キーに設定
 
よろしくお願いします。

回答
投稿日時: 17/11/17 09:39:25
投稿者: mayu.

引用:
質問なのですが、所属テーブルの所属コードは、主キーでないといけないでしょうか。
いいえ。
 
引用:
グループ名は、唯一無二なので、
本当に断言できますか。
グループ名がユニークである限り、
2017/11/11 22:48:00 の UPDATE文 で問題なく置換できますけど
データの生成元は DBMS ではなく
整合性制約を付与できないエクセルデータ等ではないかと推測します。
 
 
CREATE TABLE T_所属
(
      rid        AUTOINCREMENT  NOT NULL PRIMARY KEY
    , 所属コード INT            NOT NULL
    , グループ名 VARCHAR( 50 )  NOT NULL
    , 所属       VARCHAR( 100 ) NOT NULL
);

< T_所属 >
-------------------------------------------
 rid   所属コード   グループ名       所属
-------------------------------------------
  1       100        kanto123        関東
  2       111        kanto123_12     横浜
  3       112        kanto:123_23    川崎
  4       200        中部_234        中部
  5       205        中部_234_23   名古屋
  6       207        中部_234_15     戸塚
  7       209        中部_234_1      湘南
  8       100        okinawa000      沖縄
  9       333        中部_234        中部

グループ名もユニークになっていない場合は、以下のようなSQLで対応可能です。
 
UPDATE
(
    SELECT y.アカウント
         , InStr( 1, ':' & y.所属グループ & ':', ':' & x.グループ名 & ':', 1 ) As num
         , x.所属
         , y.所属地区
    FROM T_所属  x
       , T_入力  y
    WHERE InStr( 1, ':' & y.所属グループ & ':', ':' & x.グループ名 & ':', 1 ) > 0
      AND NOT EXISTS
          (
              SELECT 1 FROM T_所属 z
               WHERE z.グループ名 = x.グループ名
                 AND z.rid > x.rid
          )
    ORDER BY y.アカウント
           , InStr( 1, ':' & y.所属グループ & ':', ':' & x.グループ名 & ':', 1 )
) q
SET 所属地区 = 所属地区 & IIf( 所属地区 Is Not Null, ':', '' ) & 所属 ;

投稿日時: 17/11/17 13:24:52
投稿者: 馬車道停留所

Mayuさん
 
ご回答ありがとうございました。
 
所属テーブルの所属コードは、主キーでなくても良いのですね。了解しました。ご教授いただいた箇所については、後ほど確認をさせていただきます。
 
確認したところ、所属のテーブルのグループ名に重複したレコードがありました。
 
したがって、クエリで、DCount関数を用いて、オートナンバー的なものを振ろうとした際に、グループ名は、一意の値を
もつフィールドではなかったため、重複したグループ名には、重複した番号が振られて、それが、11件の重複レコードの原因でした。
 

投稿日時: 17/11/21 20:51:51
投稿者: 馬車道停留所

Mayuさん
 
期待通りの値が入力されました。
ありがとうございました。
 
参考までにお聞きしたいのですが、
今は、所属地区の値が、所属グループの値の後に入力されていますが、所属グループの値を、所属地区の値で置換してしまう方法もあるのでしょうか。

回答
投稿日時: 17/11/21 23:18:57
投稿者: mayu.

引用:
参考までにお聞きしたいのですが
今は、所属地区の値が、所属グループの値の後に入力されていますが、
所属グループの値を、所属地区の値で置換してしまう方法もあるのでしょうか。

はい。 残念ですが、あります。
キー項目を非キー項目へと置き換える{ 暴挙以外の何物でもない行為 }のため、
実行したが最後、データベースとしての体を微塵も成していないゴミデータ に成り果てますが。
 
■ テーブル定義
CREATE TABLE T_所属
(
      rid         AUTOINCREMENT  NOT NULL PRIMARY KEY
    , 所属コード  INT            NOT NULL
    , グループ名  VARCHAR( 50 )  NOT NULL
    , 所属        VARCHAR( 100 ) NOT NULL
);

CREATE TABLE T_入力
(
      アカウント   VARCHAR( 50 ) NOT NULL PRIMARY KEY
    , 所属グループ VARCHAR( 255 )
);

■ データ例
< T_所属 >
-------------------------------------------
 rid   所属コード   グループ名       所属
-------------------------------------------
  1       100        kanto123        関東
  2       111        kanto123_12     横浜
  3       112        kanto:123_23    川崎
  4       200        中部_234        中部
  5       205        中部_234_23   名古屋
  6       207        中部_234_15     戸塚
  7       209        中部_234_1      湘南
  8       100        okinawa000      沖縄
  9       333        中部_234        中部
 10       333        chubu_234       中部

< T_入力 >
------------------------------------------------------------
 アカウント                  所属グループ                  
------------------------------------------------------------
  0246855     中部_234:中部_234_15:中部_234_23:中部_234_1
  1234567     kanto123:kanto123_12:kanto:123_23:中部_234_23
  9876543     chubu_234:中部_234_1
  9999999     ( Null )

■ SQL
UPDATE T_入力
   SET 所属グループ = ':' & 所属グループ & ':?'
 WHERE 所属グループ Is Not Null ;

UPDATE
(
    SELECT y.アカウント
         , InStr( 1, y.所属グループ, ':' & x.グループ名 & ':', 1 ) As num
         , x.所属
         , y.所属グループ
    FROM T_所属  x
       , T_入力  y
    WHERE InStr( 1, y.所属グループ, ':' & x.グループ名 & ':', 1 ) > 0
      AND NOT EXISTS
          (
              SELECT 1 FROM T_所属 z
               WHERE z.グループ名 = x.グループ名
                 AND z.rid > x.rid
          )
    ORDER BY y.アカウント
           , InStr( 1, y.所属グループ, ':' & x.グループ名 & ':', 1 )
) q
SET 所属グループ = 所属グループ & ':' & 所属 ;

UPDATE T_入力
   SET 所属グループ = Mid$( 所属グループ
                          , InStrRev( 所属グループ, '?', -1, 0 ) + 2
                      )
 WHERE 所属グループ Is Not Null ;

■ 実行結果
< T_入力 >
------------------------------------
 アカウント        所属グループ     
------------------------------------
  0246855     中部:戸塚:名古屋:湘南
  1234567     関東:横浜:川崎:名古屋
  9876543     中部:湘南
  9999999     ( Null )

投稿日時: 17/11/21 23:36:23
投稿者: 馬車道停留所

Mayuさん
 
ご丁寧にご説明いただきありがとうございました。
大変、参考になりました。
 
感謝します。