Access tips – Drill down reports

Comments

A common problem in Access is the lack of drilldown reports.  Our approach to this is to build something where you get a similar drilldown result but using clickable links to more detailed reports relative to a record being selected.  YES, you can build drilldown reports that act and feel more like a drilldown report (we do this using forms actually, which respond based on clicks).  But for a quick drilldown experience, try this:

Create a master report, include the generalized data you want, and be sure to include the primary key on the record as a hidden field (usually your auto number).

 

Then take the field you want to act as a drill down, change it’s format to “Hyperlink”, and create an event on click for it like this:

This will result in you opening up a different detailed report which is filtered to ONLY this record from your main report.  SIMPLE!  But effective!

Note, I like to open these forms or reports in acFormReadOnly to prevent users from accidentally editing their data.

Related Posts

Access 2016 Relationships

Short blog today, this is a great read on relationships in Access 2016: Access 2016 Relationships Thanks to Microsoft for this one!

Microsoft Access Union Query from Hell. Skill Level: Demi-God

The worst query I ever had to write which powers a master report for the database user.  Here it is.  I don't think I can make it smaller, there were too many weird rules, aggregates, and translations in it.  And we needed to pull different columns based on criteria. ...

0 Comments

0 Comments

Submit a Comment