Caption on the report of Union Query

[Frank Situmorang]

Hello:

The field of DateType of the following crosstab Query, will show the field
name on the report,
TRANSFORM Count(FrankS.TheDate) AS CountOfTheDate
SELECT Replace([DateType],"Date","Number") AS Activity,
Format([TheDate],"yyyy") AS [Stats Year]
FROM FrankS
WHERE (((Format([TheDate],"yyyy"))=[Enter year (2005)]))
GROUP BY Replace([DateType],"Date","Number"), Format([TheDate],"yyyy")
PIVOT Format([TheDate],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");

FrankS is a Union Query:

The Date Type consisits of the following, but I want to show up in the
report not the actual name of the field, but I want it as follows:

DateType shown in the report:
---------------- -----------------------
"TGLLAHIR"= " Born"
TGLBPTIS = " Baptism"
TGL_pen = "Member transfer IN"
ATSPERCA_M= " Admission of Faith"
ATSSUR1_K= member transfer OUT"
"KMATIAN_K="deceaced"
KELMURT_K="Backsliding"
KELHILA_K= " Reported as Lost"

Thanks in advance

 

[John Spencer]

It seems to me that you need a translation table and that you would include
that table in your queries within the union query. OR if you have a table to
of DateTypes (add another column with the equivalent values)

DateTypes (name of table)
DateType (current values - "TGLLAHIR")
EnglishVersion (equivalent - "Born")

Very simple SQL might look like the following:
SELECT EnglishVersion as Activity
, FieldA, FieldB, etc
FROM DateTypes INNER JOIN SomeOtherTable
ON DateTypes.DateType = SomeOtherTable.DateType



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County