Some records do not show up in the report using crosstab query

[Frank Situmorang]

Hello,
I appreciate your help, the following is my cross tab query used in the
report, however some of the records do not show up, could you help me how can
I solve it?. I tried to make the detail can grow, but the result is still the
same. ( some records do not show up)

This is my query:
PARAMETERS [Forms]![frmTahundipilih]![cboEndYear] Short;
TRANSFORM First(Concatenate("SELECT NamaPel FROM PelayanJemaat WHERE
BidangPelayanan='" & [BidangPelayanan] & "' AND TahunPel =" & [TahunPel] & "
ORDER BY ID_pel",Chr(13) & Chr(10))) AS Expr2
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
PIVOT "Yr" & Forms!frmTahundipilih!cboEndYear-[TahunPel] In
("Yr0","Yr1","Yr2","Yr3","Yr4");


Thanks in advance

 

[John Spencer]

The SQL looks good. So can you be a bit more specific about which records do
not show up. IF you run a simple SELECT query do the records you are missing
show up? Does this query show all the records? If so, what is different
about the records that you get with the crosstab and the records that appear
in this query

PARAMETERS [Forms]![frmTahundipilih]![cboEndYear] Short;
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
, Concatenate("SELECT NamaPel FROM PelayanJemaat WHERE
BidangPelayanan='" & [BidangPelayanan] & "' AND TahunPel =" & [TahunPel] & "
ORDER BY ID_pel",Chr(13) & Chr(10)) AS Expr2
, "Yr" & Forms!frmTahundipilih!cboEndYear-[TahunPel] as TheYear
FROM PelayanJemaat

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County