Access

質問

初心者なので質問の内容がわかり辛いかも知れませんが教えて下さい。
Accessを使って給与集計をしています。

社員ごとに支払額や交通費などを表示する賃金台帳フォームに、特定の社員だけ給与天引が発生するのでその金額を載せたいのですが、うまくできません。

天引のクエリには
社員ID、購入月、天引金額があります。

EXCELのLOOKUPのように、賃金台帳の社員IDを見て、天引のクエリの中から同じIDを検索し、その月の金額を、賃金台帳のあるフィールドの中に返す・・・ということをしたいのですが、方法はありますか？

分かりづらい質問ですがご回答いただければ助かります。
宜しくお願いします。

回答No.7

CHRONOS_0 · ベストアンサー

>天引テーブルに載っているIDだけに絞られてしまいました。
賃金台帳クエリには天引きテーブルにないレコードもあるということですね
だったら結合の種類を[賃金台帳クエリの全レコードを・・・]にしてください

天引きテーブルにレコードがないものについては天引き額はNullになりますから
計算に使用するときにはNz関数で0にしてやるといいでしょう

お礼

できました！！！！

経験者の方にはこんな簡単な事で・・・と思われる内容で恥ずかしかったのですが、聞いてよかったです。何度も回答していただいて本当にありがとうございます！！

回答No.6

noname#140971

102 は[社員ID]の値そのものです。
2000とか5000は、天引金額です。

お礼

何度も回答いただき本当にありがとうございます。

回答No.5

CHRONOS_0

>賃金台帳クエリと天引のテーブルを一緒にして
>月フィールドと社員IDフィールドを結合してクエリは作ってみたのですが、これ
>だと思うようにならなかった

どううまく行かなかったのかを説明していただいたほうがいいですね

その際
>賃金台帳は社員マスタテーブルや労働時間を入力したテーブルをクエリにして
元のテーブルの情報、クエリの情報もアップしてください

お礼

何度も回答頂き本当にありがとうございます。

>賃金台帳クエリと天引のテーブルを一緒にして
>月フィールドと社員IDフィールドを結合してクエリは作ってみたのですが、これ
>だと思うようにならなかった

賃金台帳クエリと天引のテーブルをくっつけて
社員ID・労働月・購入月・天引金額を出すクエリを作ったら天引テーブルに載っているIDだけに絞られてしまいました。

天引のテーブルを使ってクエリを作り天引金額が各ID・月ごとに集計されるクエリを作りました。（1人の社員が１ヶ月に１つ以上購入する場合もあるので）

賃金台帳クエリには
社員ID/社員名/労働月/労働時間/労働日数/支払金額/交通費/保険控除後金額　があり

天引クエリは
社員ID/購入月/購入金額/購入内容　があります。

賃金台帳クエリの保険控除後金額の横に天引クエリの購入金額を表示するようにしたいのです。

情報不足な質問ばかりで申し訳ございません。

回答No.4

noname#140971

イミディエイトウインドウでチェックしてから・・・

[イミディエイト]
? DBSum("天引金額","給与天引","社員ID=102")
5000
? DBSum("天引金額","給与天引","社員ID=102 AND 労働月='2007/06'")
2000

これが、第一関門。
次に、条件文をフォーム参照方式にするのが第二関門。
その次は、フォームに組み込むという最終関門。

それぞれのステップのテスト内容を回答者によく判るように書くこと。
そして、テストの要領と結果を示し問題点を指摘してもらうこと。
こういう質問を２、３回繰り返せば、自ずと解決します。

なお、Access の DSum関数、DLookup関数でテストし最後に関数名を変えても一向に構いません。

お礼

再度ご返答頂き本当にありがとうございます。

イミディエイトウインドウに上記式を貼り付けしました。

この時点で分からないことだらけなのですが、社員ID＝の後ろは検索したい社員IDなのでしょうか？それともそのまま102で良いのでしょうか？
またそれぞれの閉じ括弧の後の5000や2000は何を意味しているのでしょうか・・・？

その後また下で教えていただいた条件分をフォームのテキストのコントロールソースに打ち込んだのですが、やはりダメでした・・・

行き詰ってしまいました。。。

回答No.3

noname#140971

標準モジュールを開いて下記の関数をコピペして[データベース関数]などの名前で保存して下さい。
実は、Access も　DLookup関数、DSum関数を提供しています。
しかし、実行速度が業務アプリで使うには余りにも遅すぎます。
なお、以下の関数の要領で　DBCount関数、DBAve関数、DBMax関数、DBMin関数もポンポンと。
頑張って下さい。

Option Compare Database
Option Explicit

Public Function DBLookup(ByVal strField As String, _
　　　　　　　　　　　　 ByVal strTable As String, _
　　　　　　　　　　　　 Optional ByVal strWhere As String = "", _
　　　　　　　　　　　　 Optional ByVal ReturnValue = Null) As Variant
On Error GoTo Err_DBLookup
　　 Dim DataValue
　　 Dim strQuerySQL As String
　　 Dim rst　　　　 As ADODB.Recordset

　　 Set rst = New ADODB.Recordset
　　 strQuerySQL = "SELECT " & strField & " FROM " & strTable
　　 If Len(strWhere) > 0 Then
　　　　 strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　 End If
　　 With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　 .MoveFirst
　　　　　　 DataValue = .Fields(0)
　　　　 End If
　　 End With
Exit_DBLookup:
On Error Resume Next
　　 rst.Close
　　 Set rst = Nothing
　　 DBLookup = Nz(DataValue, ReturnValue)
　　 Exit Function
Err_DBLookup:
　　 MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　 Resume Exit_DBLookup
End Function

Public Function DBSum(ByVal strField As String, _
　　　　　　　　　　　ByVal strTable As String, _
　　　　　　　　　　　Optional strWhere As String = "") As Variant
On Error GoTo Err_DBSum
　　Dim N
　　Dim strQuerySQL As String
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　strQuerySQL = "SELECT SUM(" & strField & ") FROM " & strTable
　　If Len(strWhere) > 0 Then
　　　　strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　End If
　　With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　 .MoveFirst
　　　　　　 N = Nz(.Fields(0), 0)
　　　　 End If
　　End With
Exit_DBSum:
On Error Resume Next
　　 rst.Close
　　 Set rst = Nothing
　　 DBSum = N
　　 Exit Function
Err_DBSum:
　　 MsgBox "SELECT 文の実行時にエラーが発生しました。(DBSum)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBSum
End Function

回答No.2

noname#140971

＜社員購入履歴＞

ID___社員ID______購入年月日__天引金額
1___101____________2007/05/02__\1,000
2___102____________2007/06/01__\2,000
3___103____________2007/06/03__\3,000

このようなテーブルがあるとします。

Access のフォームのVBエディタを開きイミディエイトウインドウで[天引記録]を参照・表示してみます。

[イミディエイト]
? DBLookup("天引金額", "社員購入履歴", "社員ID=102 AND FORMAT(購入年月日, 'yyyy/mm')='2007/06'")
2000
? DBSum("天引金額", "社員購入履歴", "社員ID=102 AND FORMAT(購入年月日, 'yyyy/mm')='2007/06'")
5000

この場合、＜社員ID=102＞と＜FORMAT(購入年月日, 'yyyy/mm')='2007/06'＞という２条件で参照しています。
さて、[天引きクエリ]とは、テーブルから各月の天引き合計額を求めたものでしょう。
その場合は、DBLookup関数を使うことになるでしょう。

? DBLookup("天引金額", "天引きクエリ", "社員ID=102 AND 購入月='2007/06'")
5000

が、DBSum関数を使えば、そういうややこしい仕掛けは不要かと思います。

＜[データベース関数] 作成のすすめ＞

さて、ここで使用している DBLookup関数、DBSum関数は Access では提供されていません。
ですから、これらは[標準モジュール]に記述し[データベース関数]などのモジュール名で保存することになります。
こういう関数を一度だけ用意するならば、Excel のLookup 関数もビックリの検索が可能になります。

さて、いずれにしろ、今回の案件は DBLookup関数またはDBSum関数を利用することで解決です。

例えば、フォーム[賃金台帳]に[社員ID]と[管理月]というテキストボックスがあるとします。
そして、[天引金額総計] というテキストボックスも配置しているとします。
表示するだけならば、[天引金額総計].[コントロールソース] に次のように書けば事足ります。

=DBSum("天引金額","社員購入履歴","社員ID=" & [社員ID] & " AND " & FORMAT(購入年月日, 'yyyy/mm')='" & [管理月] & "'")

DBSum関数は、合計する列名、対象となるテーブルまたはクエリ名、条件文の３つを引数とするものです。
問題は、条件文でフォームに配置されている[社員ID]と[管理月]とを参照させればいい訳です。

と、まあ、こんな感じでいかがでしょうか？
なお、えらく長くなるので、DBLookup関数、DBSum関数は、別の回答とします。

お礼

上の回答と２回に分けてご丁寧に教えていただいて本当にありがとうございます。

教えていただいた式をそのままコピーし

=DBSum("天引金額","社員購入履歴","社員ID=" & [社員ID] & " AND " & FORMAT(購入年月日, 'yyyy/mm')='" & [管理月] & "'")

社員購入履歴を給与天引　とテーブル名に変え
管理月　を　労働月　と変えたのですが「指定できる文字列が2048文字までです」と出てしまいます・・たぶん私が何か間違えてしまっているのだと分かっていますが間違いも分からず・・ヘルプを見てもわからず・・ほんとACCESSは難しいです。。。

上で頂いた回答はほんと難しすぎてそのままコピーしていいのかも分からず・・・こんな素人ですがこうして回答いただけて嬉しかったです。

回答No.1

CHRONOS_0

分かりづらい質問というよりも、データベースの基本が全く分かっていない質問ですね

データベースでは複数のテーブルで物事を表現するのが基本ですから
他のテーブルを参照するのにExcelのように関数を使う必要などありません
もっと強力な機能が提供されています
それがクエリです

>天引のクエリには
これはクエリじゃなくテーブルですよね

賃金台帳テーブルの情報がありませんが
社員IDや月のフィールドがあるはずですよね

賃金台帳テーブルの社員IDフィールドと天引きテーブルの社員IDフィールド
賃金台帳の月フィールドと天引きテーブルの月フィールドを結合したクエリを作れば

>天引のクエリの中から同じIDを検索し、その月の金額を、
>賃金台帳のあるフィールドの中に返す
これができます

詳しくはリレーションシップ、参照性合成、クエリなどを入門書で勉強してください

お礼

回答ありがとうございます。

おっしゃるとおりよく分かっていない初心者なので基本的な質問ですみませんでした。

賃金台帳は社員マスタテーブルや労働時間を入力したテーブルをクエリにして作成したものなのでテーブルはなく賃金台帳クエリしかありません。

賃金台帳クエリと天引のテーブルを一緒にして
月フィールドと社員IDフィールドを結合してクエリは作ってみたのですが、これだと思うようにならなかったので他に方法があるか質問していました。

分かりづらい質問に回答頂きありがとうございました。