ACCESS FEED: Re: [belajar-access] query laporan inventory

Rabu, 07 September 2011

Re: [belajar-access] query laporan inventory

Anda membuat TOTAL query, sementara tidak semua field/kolom di-total-kan.

Field NoATK dan Tanggal paling tidak dijadikan Group By, atau jangan diikutkan dalam query kalau memang gak perlu.

Haer

On Thu, Sep 8, 2011 at 10:43 AM, siska <si_siska@yahoo.com> wrote:

Selamat pagi para master access, saya lagi coba-coba membuat laporan stok inventory, dengan query2 sbb:

1. Q untuk history transaksi :
SELECT NoATK, Tanggal,JumlahPenerimaan as inQty, 0 as outQty from QIn
UNION ALL select NoATK, Tanggal, 0 as InQty, JumlahPemakaian as outQty
FROM Qout
ORDER BY Tanggal;

2. Q report bulanan :
SELECT NoATK, Tanggal,sum(inQ) as inQty, sum(outQ) as outQty from Qhistory
WHERE Tanggal < [start date]
UNION ALL select NoATK, Tgl as Tanggal, inQ as inQty, outQ as outQty
FROM Qhistory
WHERE tgl between [start date] and [end date]
ORDER BY NoATK;

Sewaktu run di Q yg kedua ada peringatan
You tried to execute a query that does not include the specified expression 'NoATK' as part of an aggregate function. (Error 3122)

Kira2 apa yang salah ya? Ada yang bisa bantu?

Terima kasih.