Grand totals with limited group contribution to total

[Flick Olmsford]

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?

 

[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

 

[Flick Olmsford]

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