i'm guessing that this topic is related to another topic which was posted by forum user Lester '
Base Form to post Currency Category total to Table or Query'
i made one post in relation to this topic>>
viewtopic.php?p=393123#p393123
if you download the attachment from that post then the code below when executed will provide a single record result set.
i am not suggesting that this is the correct way to address the issue rather i'm providing an answer to your question.
1. use the form 'fOpen_Report(Annual_Donations)'
2. select a customer & hit save selection.
3. execute the code below using direct mode.
4. it may be necessary to create a view from this code if it is to be used for mail merge. i'm not certain but believe the base parser must be enabled?
Code: Select all
select
T."Title",
P."FName", P."LName",
A."No",
A."Street",
A."City",
A."State",
A."Zip",
M1."Jan", M1."Jan_Total", M2."Feb", M2."Feb_Total", M3."Mar", M3."Mar_Total",
M4."Apr", M4."Apr_Total", M5."May", M5."May_Total", M6."Jun", M6."Jun_Total",
M7."Jul", M7."Jul_Total", M8."Aug", M8."Aug_Total", M9."Sep", M9."Sep_Total",
M10."Oct", M10."Oct_Total", M11."Nov", M11."Nov_Total", M12."Dec", M12."Dec_Total",
"Total"."TotalForYear", "Total"."Year"
from
"tFilter" F
join "tPeople" P
on P.ID = F."People_ID_1"
left join "tTitle" T
on T.ID = P."Title_ID"
left join "tAddress" A
on A."People_ID" = P.ID
--jan
left join
(
select F."People_ID_1", 'JAN' "Jan", sum(D."Amount") "Jan_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 1
group by "People_ID_1"
) M1
on M1."People_ID_1" = F."People_ID_1"
--feb
left join
(
select F."People_ID_1", 'FEB' "Feb", sum(D."Amount") "Feb_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 2
group by "People_ID_1"
) M2
on M2."People_ID_1" = F."People_ID_1"
--mar
left join
(
select F."People_ID_1", 'MAR' "Mar", sum(D."Amount") "Mar_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 3
group by "People_ID_1"
) M3
on M3."People_ID_1" = F."People_ID_1"
--apr
left join
(
select F."People_ID_1", 'APR' "Apr", sum(D."Amount") "Apr_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 4
group by "People_ID_1"
) M4
on M4."People_ID_1" = F."People_ID_1"
--may
left join
(
select F."People_ID_1", 'MAY' "May", sum(D."Amount") "May_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 5
group by "People_ID_1"
) M5
on M5."People_ID_1" = F."People_ID_1"
--jun
left join
(
select F."People_ID_1", 'JUN' "Jun", sum(D."Amount") "Jun_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 6
group by "People_ID_1"
) M6
on M6."People_ID_1" = F."People_ID_1"
--jul
left join
(
select F."People_ID_1", 'JUL' "Jul", sum(D."Amount") "Jul_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 7
group by "People_ID_1"
) M7
on M7."People_ID_1" = F."People_ID_1"
--aug
left join
(
select F."People_ID_1", 'AUG' "Aug", sum(D."Amount") "Aug_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 8
group by "People_ID_1"
) M8
on M8."People_ID_1" = F."People_ID_1"
--sep
left join
(
select F."People_ID_1", 'SEP' "Sep", sum(D."Amount") "Sep_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 9
group by "People_ID_1"
) M9
on M9."People_ID_1" = F."People_ID_1"
--oct
left join
(
select F."People_ID_1", 'OCT' "Oct", sum(D."Amount") "Oct_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 10
group by "People_ID_1"
) M10
on M10."People_ID_1" = F."People_ID_1"
--nov
left join
(
select F."People_ID_1", 'NOV' "Nov", sum(D."Amount") "Nov_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 11
group by "People_ID_1"
) M11
on M11."People_ID_1" = F."People_ID_1"
--dec
left join
(
select F."People_ID_1", 'DEC' "Dec", sum(D."Amount") "Dec_Total"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
where month(D."Date") = 12
group by "People_ID_1"
) M12
on M12."People_ID_1" = F."People_ID_1"
join
(
select F."People_ID_1", sum(D."Amount") "TotalForYear", F."Year" "Year"
from "tFilter" F
join "tDonations" D
on D."People_ID" = F."People_ID_1" and year(D."Date") = F."Year"
group by "People_ID_1", "Year"
) "Total"
on "Total"."People_ID_1" = F."People_ID_1"
where F.ID = 0
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10