Grand totals with limited group contribution to total

G

Guest

I have a report that lists different companies with several records per
company. I need a total of all records in the report, but each company can
have only a maximum of two records count towards the grand total at the end.
E.g.

Company 1
Record 1
Record 2
Record 3

Company 2
Record 1
Record 2

Company 3
Record 1

Total records should be 5. Only two records of company 1 should count.

How do I do this?
 
R

Roger Carlson

This can be done, but it's kind of a workaround.

First of all, you need to create a Top Query by Group query. By that I mean
a query which shows the top 2 values for each company. On my website
(www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which illustrates how to do this. (Look at
'4.Advanced Queries'.)

Once you've built that and it's returning the correct records, you could use
an unbound textbox with the DSum domain aggregate function in its Control
Source to display the total of the values in the Top query.

Without more information like table and field names and datatypes, it's hard
to be more specific.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
G

Guest

Thanks. It does seem a bit involved but it does address my problem. I'll
study your solution.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top