Combining Lines In a Report

[BerkshireGuy]

Hello everyone,

I have an agency report where the lines looks something like this:

Agency Code Percent Sold Total Premium
------------------------------------------
J1 50% $1000
GT 45% $750
L2 20% $300

I need to create a way so that the user can select which agencies to
combine before running the report. They should be able to combine
2,3,4 agencies together and leave other agencies uncombined.

For instance, lets say they want to combine agencies (on runtime), L2
and GT in the above example. The report should now look like:
Agency Code Percent Sold Total Premium
------------------------------------------
J1 50% $1000
GT,L2 65% $1050

Can anyone suggest the best way of doing this? I was thinking a form
where they select which agencies get combined and then using recordsets
to combined and rewrite the reports dataset.

Hope I was clear enough.

Thanks,
Brian

[dedejavu@hotmail.com]

Re: Combining Lines In a Report

Brian,
That is a pretty ugly process.
How good are you at coding forms? If you can do that you are in good
shape. Here are my two cents.
Table1:
AgencyCode PercentSold TotalPremium
j1 $0.50 1000
gt $0.45 750
l2 $0.20 300
z3 $0.30 400

Table2:
AgencyCode
J1
Gt,l2

If you set up your form so that you have two list boxes side by side
with buttons between them like the form wizard when you choose fields.
Create a table to hold your combo agencies (table2 in this example).
List1 shows the agencies that are not used, List2 shows the
combinations, you can have the buttons move the data between them.
Moving an item from List1 adds it to the table that List2 shows.
your List1 qry could look like this:
SELECT Table1.AgencyCo de
FROM Table1, Table2
GROUP BY Table1.AgencyCo de
HAVING (((Sum(InStr([Table2].[AgencyCode] & ",",[Table1].[AgencyCode] &
",")))=0));

Your report recordset would look like this:
SELECT Table2.AgencyCo de, Sum(Table1.Perc entSold) AS PercentSold,
Sum(Table1.Tota lPremium) AS TotalPremium
FROM Table1, Table2
WHERE (((InStr([Table2].[AgencyCode] & ",",[Table1].[AgencyCode] &
","))<>0))
GROUP BY Table2.AgencyCo de;

If you can get most of the way there and you get stuck on the form
code, post it and ask again. :)
Hope that helps you
Pachydermitis