[mso] Access Report & Subreports

I'm working up a report that will hopefully contain a bucket load of
information on about 30,000 persons based on about 12 tables.  This is
within the vendor controlled version of Access so I can't create queries
in query design but have to use/save the query design within a report.

So far, I've got a master report on tblPerson which is info such as
First, Last, Address then sub reports for additional data including a
sub-report based on tblActivity where I get the TOP 5 and a sub-report
based on tablInterest where I get the TOP 3. =20

Telephone and eMail is contained in one table which a 1 to many...a
single person can have many phone numbers or email addresses.  I created
a sub-report to get the maximum (latest) email address (thanks Jim for
helping) and another to get the maximum phone number.  Is this actually
the best way?  Is there a way to get the lateste email and the latest
phone in one sub-report? Would it be better to create unbound controls
instead of sub-reports?

The reason I ask is there is test information contained in a single
table.  A person could have any of a dozen or so tests with the related
scores.  Do I need a separate sub-report for each possible test or is
there a way to define that in one expression?  My objective is to get
the Official Highest Test Score for each Test Name.

The test table looks like this:

UID        Test name    Test Date       Test Score      Official
Abc123  reading 05/07/2003      92              y
Abc123  reading 06/12/2005      98              y
Abc123  math            05/07/2003      87              y
Abc123  math            06/12/2005      58              y
Abc123  math            07/05/2005      94              n
Abc123  history 04/04/2004      91              n

In this example for this UID I would want this result:
Abc123  reading 98
Abc123  math            87

...hmmm...looking at it like this, maybe SELECT TOP could work?

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: