[belajar-access] paramater data tahun pada crosstab query

----- Original Message -----
From: "Sumiyanto -" <sumiyanto@yahoo.com>
To: <belajar-access@yahoogroups.com>
Sent: Monday, November 23, 2009 6:02 PM
Subject: Re: [belajar-access] paramater data tahun pada crosstab query

@ Muhibudin Fahmi, Terima kasih mas....Problem Solved.

Sumiyanto Soerabaia

--- On Mon, 11/23/09, Muhibudin Fahmi <muhibudin@gmail.com> wrote:

From: Muhibudin Fahmi <muhibudin@gmail.com>
Subject: Re: [belajar-access] paramater data tahun pada crosstab query
To: belajar-access@yahoogroups.com
Date: Monday, November 23, 2009, 1:02 PM

sekedar share mas, tolong dibenerin kalo salah..
yang pernah saya coba untuk parameter pada crosstab query harus ditentukan
type datanya, yang ada di menu query parameters. (pada design view) ato
kalo disql sebelum transform tambahin parameters
untuk mas sumiyanto coba pada design view ditambahin
field Format([Tanggal Terima],"yyyy") (asumsi kalo ini ada tahunnya)
terus dikasih criteria misalnya between [tahun1] and [tahun2]trus ke menu
query parameters.. . ditambahin parameter | data type
tahun1 | texttahun2 | text
sqlnya kira2 kayak gini....
PARAMETERS tahun1 Text ( 255 ), tahun2 Text ( 255 );
TRANSFORM Sum([Bank Receive qr].[Pokok Angsuran (Rp)]) AS [SumOfPokok
Angsuran (Rp)1]
SELECT [Bank Receive qr].[Nama Kolektor], Sum([Bank Receive qr].[Pokok
Angsuran (Rp)]) AS [SumOfPokok Angsuran (Rp)], Count([Bank Receive qr].ID)
AS [Total Of ID]

FROM [Bank Receive qr]
WHERE (((Format([tgllhr] ,"yyyy")) Between [tahun1] And [tahun2]))GROUP BY
[Bank Receive qr].[Nama Kolektor], Format([tgllhr] ,"yyyy")

PIVOT Format([Tanggal Terima],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

salam jabat erat