[Solved] Need help with mail-merge query

[UnklDonald418]

I have 2 tables
TableA includes ID, Name, Address … etc.
TableB contains transaction data including TDate, Tamt, TblA_ID
and there is a 1 to many relationship between TableA and TableB linked by TableA.ID and TableB.TblA_ID

I need to create a mail-merge document that will show the TableA information and the matching TableB information on a single page.
When I create a query

Code: Select all

SELECT “TableA”.*, “TableB”.*  FROM  “TableA”, “TableB”  WHERE “TableA”.ID = “TableB”.”TblA_ID”;

I see all the results I need, but of course it displays 1 row for each value in TableB which results in mail-merge generating a new page for each row.

A report would be the simple solution, but they have a nice looking mail-merge document that includes text columns in the body of the document alongside the TableB information. Since the Oracle Report Builder extension isn't able to do columns we're back to mail-merge.

Is there some way to write a query that puts all the data onto a single row?
Any other ideas?

[FJCC]

I'm not sure you need to do a mail merge. Using the Data Sources tool (F4), you can drag the query into a text document and it will be displayed as a table. Will that work?

[RPG]

Hello

I do not understand the problem complete of UnklDonald418 but I have the idea: you can do it with the report builder. The data in the reportbuilder makes no different more, between table A en table B.

What you have to do is: sort and group the data how you want have it. Then place the headers on the good place and also the data on the good place. Insert on the good places breaks. This must be done with the group property.

The report builder is a programming tool. I think it is good to start with a small data set. With a small data set you can easy see what happens.

Romke

[chrisb]

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

[UnklDonald418]

Thanks FJCC.
When I drag the query onto the document it does allow the choice of which columns, but I don't see where I can apply a condition to limit the rows to show only those that are relevant. Without that all the rows from TableB appear on a single document.
If they were generating just a few documents a modified query might work but they will be generating 200+ documents at a time so I'm not sure that approach will work for them.

Thanks Romke. Actually I have generated a report that contains all the data from the tables. But as I mentioned, due to some of the formatting limitations of the report builder it doesn't look quite the same as the mail-merge document they designed. The biggest issue is that I've not found a way to include a column of text alongside the list of TableB information.

Thanks ChrisB Wow! Creating a view and using your code it works great. Yes I've also been working with Lester trying to get him to abandon his single table model and use a relational model. Since I couldn't quite duplicate his mail-merge document using the report builder I was back to mail-merge. I thought it might be possible to create a workable query using SQL but it was beyond me. With a couple of tweaks he should be up and running.