Mail merge with Aggregate func (1 Viewer)

[jazi]

Morning!

I have quite a few aggregate functions within a report I produced based on 2 queries. I need to do a mail merge with this information, but it is not all in one single query, because I cannot get an expression using totals in a field. How do you get these aggregate functions to be a part of the query, so it can eventually be part of the mail merge? It is unfortunate that you can't just use a report to mail merge, because all my equations are in the report.

 

[namliam]

Have a search on "Running sum" and I am sure you will find some examples

 

[jazi]

What if I need more than just the totals? I have an expression that needs to divide one total by another, but again, that is in the report, not in the query.

 

[GaryPanic]

2 options

the first get it all sorted in Access first (and if you can get your results into a temp table then the super easy word option will work wonders

failing this - pass what you have already in to word and use the limtied switch functions within word itself - check the help files within word - (although they are limited)

 

[namliam]

Still if you have the totals you can do anything to them, even devide them again.

 

[namliam]

A second alternative would be to get some coding going, fill a staging table with the data required. Then mailmerge using that table... or an export of that table.

In code ANYTHING can be done

 

[jazi]

Mail merge with aggreg func

first off, thanks for answering.

I dont understand what you mean by sort it out in access? Everything is sorted out already, it is just that the values I need are not in any field of any query or datatable. ]

All it is, is one field's total divided by another field's total. Unforunatley that is all word will use to make a mail merge.

for example
field a field b field c
a=1 2; total for field a:1+2=3
b=3 4; total for field b:3+4=7
c=5 6; total for field c:5+6=11

Function = sum ([a])/sum () = New value
x = 3/7

I cannot have that x value to come up in a field in a query, since it is based on a total of one field divided by the total for another.

 

[namliam]

You can do that in a group by / Union query.

I am getting you want to total for A, B and C seperatly, then devide the total of A/B
a b c
1 3 5
2 4 6

3 7 11 => Totals

Required: 3/7 = 42.8%...

Now create a group by query on the fields that you need to be unique and sum your columns. These sum products you can devide to get 3/7.

Depending upon your needed results you can then create a Union query or join to get all the information into their respective places ready for the word mail merge...

Anything is possible... if sometimes a little hard or beyond our understanding at the time.
Sometimes tho... things are possible to do inside a week, but have to be done inside a DAY ... Now that is impossible ! Except if your name is Scotty

(Scotty is the Engeneer in Star Trek Original series, in case you are not a trecky )

 

[namliam]

Hi,

Instead of you sending me your DB as per your request via PM..., I compiled a small sample for you...

I think what you are looking for is either in Query1 or Query3...

Good luck!

 

[jazi]

Thanks!

The first one looks like it is it, I will try it. Thanks for your help!

 

[jazi]

SELECT Table1.Group, Table1.Month, Sum(Table1.Value1) AS SumOfValue1, Sum(Table1.Value2) AS SumOfValue2, Sum(Table1.Value3) AS SumOfValue3, Sum(Table1.Value4) AS SumOfValue4, Round(IIf([SumofValue1]=0,0,[SumofValue2]/[SumofValue1])*1000000,2) AS Expr1, Round(IIf([SumofValue3]=0,0,[SumofValue4]/[SumofValue3])*1000000,2) AS Expr2
FROM Table1
GROUP BY Table1.ID, Table1.Group, Table1.Month
HAVING (((Table1.ID) In (Select top 6 ID from Table1 as s where s.Group=Table1.Group order by s.Month desc)))
ORDER BY Table1.Month;

How do I get this to only have one line per group? I have tried different things, nothing works.

 

[namliam]

HI Jazi

Sorry to leave you hanging, please see this post

http://www.access-programmers.co.uk/forums/showthread.php?p=693775#post693775

why I disappear(ed).

Regards