人に聞けない痔の悩み、これでスッキリ >>

お世話になります。
表題の通り、ACCESSクエリで連番を付けるにはどうすれば宜しいでしょうか?
調べたのですが、よく分からない為、
関数又は、SQLに詳しい方、よろしくお願いいたします。
(下記もDCOUNT関数でできますか?)

テーブル名:T_A
---------------------------
番号   名前   ランキング
1     佐藤    1
2     鈴木    2
3     佐藤    1
4     鈴木    2
5     佐藤    2
---------------------------
上記のテーブルがあったとして、特産品及び、ランキングを昇順すると、
下記の通りとなります。
---------------------------
番号   名前   ランキング
3     佐藤    1
1     佐藤    1
5     佐藤    2
4     鈴木    2
2     鈴木    2
---------------------------
ここで、連番という項目を追加して、上から1,2,3と連番を付けるにはどうすれば
宜しいでしょうか。
↓↓↓↓↓↓↓
(得たい結果)
------------------------------.
番号   名前   ランキング 連番←追加項目
3     佐藤    1       1
1     佐藤    1       2
5     佐藤    2       3
4     鈴木    2       4
2     鈴木    2       5
------------------------------
分かる方、教えてください。
よろしくお願い致します。

このQ&Aに関連する最新のQ&A

A 回答 (4件)

------------------------------.


番号   名前   ランキング 連番←追加項目
3     佐藤    1       1
1     佐藤    1       2
5     佐藤    2       3
4     鈴木    2       4
2     鈴木    2       5
------------------------------

要は、当該のId(=番号)が何番目に出現しているのか?
3--->1番目
1--->2番目

ならば、次のようなクエリを作成すれば目的は達成できます。

SELECT
  T1.ID,
  T1.名前,
  T1.ランキング,
  IdFound("SELECT ID FROM T1 ORDER BY [ランキング], 名前",[ID]) AS 連番
FROM T1
ORDER BY T1.名前, T1.ランキング;

さて、問題は IdFound() が提供されていないこと。
そこで、自作します。

Public Function IdFound(ByVal strSQL As String, ByVal Id As Integer) As Integer
  Dim N As Integer
  Dim M As Integer
  Dim rst As ADODB.Recordset
  
  Set rst = New ADODB.Recordset
  With rst
     .Open strSQL, _
        CurrentProject.Connection, _
        adOpenStatic, _
        adLockReadOnly
     If Not .BOF Then
       M = .RecordCount
       .MoveFirst
       For N = 1 To M
         If .Fields(0).Value = Id Then
           Exit For
         End If
        .MoveNext
       Next N
     End If
   End With
   IdFound = N
End Function

祈、成功!
「クエリで複数項目を昇順後、連番を付ける方」の回答画像1
    • good
    • 1

「番号」に重複がない長整数である事を前提にすると


一番簡単なのは、
テーブル「T_A」にフィールド「連番」(長整数)を設けて
VBA で「連番」を設定する事だと思います。


Public Sub Samp1()
  Dim rs As DAO.Recordset
  Dim sSql As String
  
  sSql = "SELECT 連番 FROM T_A ORDER BY 名前, ランキング, 番号 DESC;"
  Set rs = CurrentDb.OpenRecordset(sSql)
  While (Not rs.EOF)
    rs.Edit
    rs(0) = rs.AbsolutePosition + 1
    rs.Update
    rs.MoveNext
  Wend
  rs.Close
  Set rs = Nothing
End Sub


※ 上記 DAO の AbsolutePosition は、0 スタートの連番になるので
これを利用して単純に設定するものです。
(ADO に書き換える場合は、1 スタートなので + 1 は不要になります)
注意するのは、どの順番で得ておくか、だけです。
( ORDER BY 名前, ランキング, 番号 DESC )

フィールド「連番」を設けずにクエリだけで・・・とした場合、
1レコード毎に OpenRecordset すると、それだけでも遅くなると思うので
この「番号」の「連番」は何・・・
1度求めたものを Dictionary に格納しておいて、その中の情報を返す様にしてみます。


Dim dic As Object

Public Function myNoInit() As Boolean
  Dim rs As DAO.Recordset
  Dim sSql As String

  If (dic Is Nothing) Then
    Set dic = CreateObject("Scripting.Dictionary")
  End If
  dic.RemoveAll

  sSql = "SELECT 番号 FROM T_A ORDER BY 名前, ランキング, 番号 DESC;"
  Set rs = CurrentDb.OpenRecordset(sSql)
  While (Not rs.EOF)
    dic(rs(0).Value) = rs.AbsolutePosition + 1
    rs.MoveNext
  Wend
  rs.Close
  Set rs = Nothing
  myNoInit = True
End Function

Public Function myNo(vNum As Variant) As Long
  If (IsNull(vNum)) Then Exit Function
  If (dic Is Nothing) Then Call myNoInit
  myNo = dic(vNum)
End Function


上記を標準モジュールに記述しておいて、クエリでは以下のように

SELECT 番号, 名前, ランキング, myNo(番号) AS 連番
FROM T_A
WHERE myNoInit()
ORDER BY 名前, ランキング, 番号 DESC;


※ 不都合あれば修正してください。
    • good
    • 0

お詫びと訂正:



× IdFound("SELECT ID FROM T1 ORDER BY [ランキング], 名前",[ID])
〇 IdFound("SELECT ID FROM T1 ORDER BY 名前, [ランキング],",[ID])

当然に、[ID]が出現する位置を確認する為の SQL文とクエリの並び(ORDER BY XXX, XXX)は同じでなければなりません。先の回答では、それが一致していませんでした。当然に、名前→ランキングの順で並べないと正しく連番は付与されません。ちょっと、単純なミスがありましたので訂正しておきます。

PS、IdFound()→InRecord()

なお、関数名ですが Instr()に近い働きですので InRecord()と命名すべきだったかも知れません。
    • good
    • 0

  rst.Close


  Set rst = Nothing
End Function

最後は、推奨スタイルに直してください。
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QACCESSのテーブルで、昇順並び替え後、連番フィールドを作成したい

表題の通りなのですが、
今現在ACCESS2000を使ってデータベースを作成しています。
その際、
----------------------
連番  項目
00001  りんご
00002  ばなな
00003  みかん
00004  もも
----------------------
というようなテーブルで[項目]を昇順で並び替え後、
連番が自動的に振られるようなクエリ(無理そうならその他の方法でも)を作成したいと思っています。

今現在はクエリで以下のように昇順で並び替えられたものを、
----------------------
連番  項目
00002  ばなな
00003  みかん
00004  もも
00001  りんご
----------------------
一旦エクセルにエクスポートして、オートフィルで[連番]を書き換え後、再度ACCESSにインポートし以下のような完成形にもっていっています。
----------------------
連番  項目
00001  ばなな
00002  みかん
00003  もも
00004  りんご
----------------------

しかし、頻繁に[項目]書き換え・追加のあるデータベースなので、結構な時間を費やしてしまい、とても効率が良いとはいえない状態です。

どうにかACCESS内で完結できる方法はないものでしょうか?

ご享受の程よろしくお願いいたします。

表題の通りなのですが、
今現在ACCESS2000を使ってデータベースを作成しています。
その際、
----------------------
連番  項目
00001  りんご
00002  ばなな
00003  みかん
00004  もも
----------------------
というようなテーブルで[項目]を昇順で並び替え後、
連番が自動的に振られるようなクエリ(無理そうならその他の方法でも)を作成したいと思っています。

今現在はクエリで以下のように昇順で並び替えられたものを、
----------------------
連番  項目
00002  ばなな
...続きを読む

Aベストアンサー

連番は他のテーブルと関連付けられていないのですよね。
今一つ、連番の使い道がわからないのですが・・・

連番は数値型で、書式が 00000 になっているものと仮定します。
テーブル名が「Table_A」と仮定します。
また、項目に重複するものがないものとします。

クエリのSQLビューで以下を記述してみてください。

UPDATE Table_A SET 連番 = DCount("項目","Table_A","項目<='" & 項目 & "'");

そして、必要に応じてこの更新クエリを実行します。


連番がテキスト型なら、DCount 部分を Format を使って桁を揃えてみてください。

QACCESSでクエリで選択した行番号をつけたい

選択結果に1~順に番号をつけたいのですが、過去ログを見ると 番号: DCount("*","データ","[NO]<=" & [NO])とすればいいと書かれてありました。これはNOが昇順になっている場合にしか1~順になりません。
NOはユニークですが、他のフィールドで昇順にしたい場合はどうすればいいのですか?
例)
NO フリガナ
312 タナカ
322 イトウ
333 マツダ
・・・・
番号 フリガナ(昇順)
2 イトウ
1 タナカ
3 マツダ
となってしまうのです。
ACCESS2002

どうぞよろしくお願いします。

Aベストアンサー

 ん~多分、

番号: DCount("*","テーブル名","[フリガナ]&[NO]<='" & [フリガナ] & [NO] & "'")

でいけると思いますが、だめだったら、

番号: DCount("*","テーブル名","[フリガナ]<'" & [フリガナ] & "' OR ([フリガナ]='" & [フリガナ] & "' AND [NO] <= " & [NO] & ")")

かな?

QAccess DCountでの連番について

DCountで連番を追加したいのですが演算対象条件の記述方法が良く分かりません。
調査用品別販売データクエリの商品コードフィールドに対して連番を振りたいです。
商品コード、連番
00110 1
00110 2
00120 1
00120 2
上記のようにしたいのですが
式1: DCount("商品コード","調査用品別販売データ","商品コード=" & [調査用品別販売データ]![商品コード])
これではエラーが出てしまいます。

Aベストアンサー

商品コードと同じようにしてやればいいのです

連番:式1: DCount("*","調査用品別販売データ","商品コード='" & [商品コード] & "' and ID <='" & ID & "'")

Qaccessテーブル作成クエリを実行したときにオートNo(のような)振る方法

お知恵をかしてください。
access2000でテーブル作成クエリを実行した時、作成したテーブルのデーターにIDを1番から振りたいのですが(オートNoのような感じで)、方法がわかりません。

計算式・SQLステートメントでの記述でも、どちらでも良いので、お分かりになる方教えてください★

Aベストアンサー

[TEST1]
ID Field_1
1  110
2  110
3  120

[TEST1]を基に[TEST2]を生成し、更に、オートナンバー型の列を生成するコードです。

? CNNExecte("SELECT * INTO TEST2 FROM TEST")
True
? CNNExecute("ALTER TABLE TEST2 ADD COLUMN AUTO_ID autoincrement")
True

[TEST2]に列[AUTO_ID]が追加されます。
列[AUTO_ID]の型は、オートナンバーです。
番号は自動的に発生します。

[TEST2]
ID Field_1 AUTO_ID
1  110    1
2  110    2
3  120    3

QAccess2000で抽出したデータに連番をふるには

下表のようにIDが1から10まであるテーブルからID3、6を抽出しました。これにID3には連番1、ID6には2と抽出したデータに連番をふりたいです。どうしたら良いですか。教えてください。

ID 会社名 連番
3 A社
6 B社

 

Aベストアンサー

すみません。中間の説明が、かなり手抜いてますので、申し訳ありませんでした。

> マクロを組むということでしょうか。

えっと、違います。
下記の文章のことを言っているものだと思いますが・・・。

> SELECT
> クエリ.ID,
> クエリ.会社名,
> DCount("ID","クエリ","ID <=" & [クエリ]![ID]) AS 連番
> FROM クエリ;

これは、SQL文と言って「Select ~ From ~ Where ~ ;」で構成されるクエリーの文章です。
クエリーをデザインビューで開き、表示-SQLビューにすると、これに似た文章が出てきます。

最初の抽出したクエリー
> ID 会社名
> 3 A社
> 6 B社
の名前を「クエリ」で保管してみて、その後、新規にクエリを作成し、SQLビューにて「Select ~ ;」の部分をコピー&ペーストで貼り付けて、デザインビューに直すと、記述している意味が見慣れたデザインビューで確認できます。
(ユニオンクエリーの場合、デザインビューに変更して確認は出来ませんけどね。これは、ユニオンクエリーではありませんので・・・。)

GUI(グラフィカルユーザーインターフェース)の部分を文章で説明していくのは難しいので、手抜きでSQL文(クエリー文)だけUpしてました。

他にも、手はあるのですが・・・更新可能なクエリーを生成する場合は、思いつく手は、このくらいかなと^^;

すみません。中間の説明が、かなり手抜いてますので、申し訳ありませんでした。

> マクロを組むということでしょうか。

えっと、違います。
下記の文章のことを言っているものだと思いますが・・・。

> SELECT
> クエリ.ID,
> クエリ.会社名,
> DCount("ID","クエリ","ID <=" & [クエリ]![ID]) AS 連番
> FROM クエリ;

これは、SQL文と言って「Select ~ From ~ Where ~ ;」で構成されるクエリーの文章です。
クエリーをデザインビューで開き、表示-SQLビューにすると、これに...続きを読む

QACCESS DCOUNTの抽出条件について

式1: DCount("コード","テーブル","コード=" & [テーブル]![コード])

結果の2以上のものを抽出したいのですが、どのように記述するのでしょうか?

>1または>=2またはnot 1でもいいですが・・・
記述方法が良く分かりません。

Aベストアンサー

No.3です。

> コード=の構文エラー 演算子がありません。
> 不明。
> とエラーメッセージが表示されます。

そのエラーメッセージの内容からすると、『コード』が「Null」(空白)のものがある、
ということだと思います(その事態は想定していませんでした)。
一応、こちらで作成したサンプルにて、DCountを使用した場合に、同様の
メッセージ(計2件)が表示されるのは確認しました。
(補足の文意を「集計クエリで解決はできたが、DCountの方はエラーとなる」
 と取りました。もしも集計クエリの方でも上記エラーが出ているようでしたら、
 元となるテーブルとクエリの情報(フィールド名とデータ型)をお知らせ下さい)
※なお、コードは数値型と想定しています。もしも文字列方の場合は、併せて
 式を「"コード='" & [テーブル]![コード] & "'"」と変更する必要があります。

上記のエラーを回避するには、DCountの第1引数を「*」にします。
また、コードがNullのレコードで、クエリの表示結果が「#エラー」となるのを
回避するためには、第3引数にNz関数を使用します。
(コードの最小値は0か1でしょうから、「最小値-1」(=-1か0)にするのがよいかと
 思います)

【Nullは重複数に含めない場合】
DCount("*","テーブル","コード=" & Nz([テーブル]![コード],-1))
※コードがNullの場合は、第3引数の結果がTrueにならない(Null=-1)ので
 0件扱いとなるため、抽出条件の「>1」で弾かれます。
 また、第3引数にNz関数を使用する代わりに、クエリの『コード』の抽出条件に
 「Is Not Null」を指定する(→最初から集計の対象外にする)方法もあります。

【Nullも1つのコードとして重複扱いする場合】
DCount("*","テーブル","Nz(コード,-1)=" & Nz([テーブル]![コード],-1))

No.3です。

> コード=の構文エラー 演算子がありません。
> 不明。
> とエラーメッセージが表示されます。

そのエラーメッセージの内容からすると、『コード』が「Null」(空白)のものがある、
ということだと思います(その事態は想定していませんでした)。
一応、こちらで作成したサンプルにて、DCountを使用した場合に、同様の
メッセージ(計2件)が表示されるのは確認しました。
(補足の文意を「集計クエリで解決はできたが、DCountの方はエラーとなる」
 と取りました。もしも集計クエリの方でも...続きを読む

QACCESSで空白のデーターをクエリで判定/識別する方法を教えてくださ

ACCESSで空白のデーターをクエリで判定/識別する方法を教えてください。
EXCELでは空白を""で判定/識別表していますがACCESSではどうなるのでしょうか。

下の例はフィールドに試験番号があればその番号を、空白なら”欠席”と表示しようとしています。
IIf(([試験番号]="空白の場合何を入れる?"),[試験番号],"欠席")

Aベストアンサー

もうひとつの書き方は
式1: IIf([試験番号] Is Not Null,[試験番号],"欠席")
第2、第3引数の、質問の順序にあわせるとこうなる。

QACCESS IIF関数 複数条件の設定について

 選択クエリにおいて、あるフィールド「 X」 のレコード数値が
 
  0<[X]<=50   であれば A 
  50<[X]<=100  であれば B 
  100<[X]<=150  であれば C

 と表記させるフィールド「Y」を追加したいと考えています。
 この場合、Yのフィールド設定で指定する数式についてご教授願います。

 一応、
  IIf(0<[X]<=50,"A","") Or IIf(50<[X]<=100,"B","") Or IIf(100<[X]<=150,"C","")

  としましたが、実行すると、Yの列がすべて「-1」と表記されてしまいます。

 何卒よろしくお願いいたします。

Aベストアンサー

a)IIF関数で対応する場合:
 IIF関数の第2引数または第3引数に、IIF関数を入れ子とする必要があります。

式: IIF([X]<=0, "", IIF([X]<=50, "A", IIF([X]<=100, "B", IIF([X]<=150, "C", ""))))


b)別の組込関数を使用する場合:
 IIF関数は二択ですので入れ子にする必要がありますが、Accessのクエリで
 使用できる、似た機能の関数に、Switch関数というものもあります。
 これであれば、入れ子にする必要がなくなります。
 (詳しくは、Accessのヘルプを参照下さい)

式: Switch([X]<=0, "", [X]<=50, "A", [X]<=100, "B", [X]<=150, "C", True, "")


なお、数学あるいは一般生活では確かに「0<[X]<=50」という表記をしますが、
Accessの関数などではこれだと正しい結果が得られません。
正しく認識させるためには、等号・不等号を挟んだ式は、「左辺」と「右辺」の
2つの要素からなる形にする必要があります。
(「0<[X]<=50」は「[X]>0 And [X]>=50」と分割してやる、と)

但し、今回のご質問のような『連続した範囲』であれば、前(左)で既出となる
式の要素で「[X]<=0」が「真(=-1)」とならなかった時点で、「[X]>0」が保証
されますので、上記のように、「[X]>0」を省略して「[X]<=50」だけを条件として
指定すればOk、ということになります。
(「0<[X]<=50」の次が「80<[X]<120」などのように、「50~80」が欠けた
 場合は、省略ができない、と)


【参考】
「0<[X]<=50」は、このままだと「0 < ([X]<=50)」という式と解釈されます。
この場合、「[X]<=50」は、「真(=-1)」か「偽(=0)」か「Null」のいずれかに
なります。
何にせよ、「0<[X]<=50」全体として「正(=-1)」となることがないため、
ご質問の式では、IIF関数は常に第3引数に指定された値「""」を返す
ことになります。

この結果、式全体としては「"" Or "" Or ""」という形となり、「0かNull」
以外なので、「真(=-1)」という値が返された、ということです。

なお、Nullについては、以前、他の方の質問につけた回答も、併せて参考までに:
http://oshiete.goo.ne.jp/qa/4850675.html

a)IIF関数で対応する場合:
 IIF関数の第2引数または第3引数に、IIF関数を入れ子とする必要があります。

式: IIF([X]<=0, "", IIF([X]<=50, "A", IIF([X]<=100, "B", IIF([X]<=150, "C", ""))))


b)別の組込関数を使用する場合:
 IIF関数は二択ですので入れ子にする必要がありますが、Accessのクエリで
 使用できる、似た機能の関数に、Switch関数というものもあります。
 これであれば、入れ子にする必要がなくなります。
 (詳しくは、Accessのヘルプを参照下さい)

式: Switch([X]<=0, "", [X]<=50, "A"...続きを読む

QAccessのRefresh・Requery・Repaintの違い

Requeryはもう一度ソースレコード(テーブル)を読み込むようです。このとき、テーブルの先頭レコードに移動してしまいます。
Refreshは最新のレコード(テーブル)を再表示するような気がします。レコードの移動は起こらない気がします。
Repaintは、VBAでキャプションなどを変更したとき使っています。
でも、よくわかっていません。
どんなときにどんなメソッドを使えばいいのでしょうか?
詳しい方、よろしくお願いいたします。

Aベストアンサー

たびたびすみません。
調べてたらこんなのがでてきました。
http://www.nurs.or.jp/~ppoy/access/access/acF007.html

参考URL:http://www.nurs.or.jp/~ppoy/access/access/acF007.html

QAccessでテキスト型→数値型への変換方法

Access2000を使用しており、クエリを作成しています。
テーブルから[回答]というフィールドをひっぱってきています。
フィールド[回答]にて、最初の3文字に対応時間を入れており、
Mid関数にて対応時間を抽出しました。

対応時間:Mid([回答],1,3)

しかし、Mid関数でとったフィールドは文字列として認識されています。(Ex:001など)
この文字列を数値に変換する関数、又は方法などお教えくださいませ。
よろしくお願い致します。

以下にイメージ図を記載致します。
=================================
回答            対応時間
001 ●●●~        001
=================================
(対応時間を数値で扱えるようにしたい、ということです)

Aベストアンサー

対応時間が、整数(-32,768 ~ 32,767)であれば、

対応時間:CLng(Mid([回答],1,3))


また、長整数(-2,147,483,648~2,147,483,647)であれば、

対応時間:CLng(Mid([回答],1,3))

とすればokのハズです。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング