Results 1 to 3 of 3
  1. #1
    whitechr is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2021
    Posts
    1

    Access Report Drill Down Multiple Layers

    Hello Everyone,

    I have been scouring the forum for an answer to this question and have found some close topics but not quite what I am looking for.

    My questions goes like this:

    I have two tables (Literature and Reference)

    Literature has two columns (Serial_Number(unique) and Title)
    Reference has three columns (Index(unique), SerialNumber and Reference)

    Some sample data looks like this:

    Serial_Number, Title
    HD456KH45, Rome Zoning Plans for 180.
    RTHS1964, Aquaducts: Who Needs Them
    SADA5464, Lessons From Alexandria
    444642SSG, Roman Warhorses Added to Batallion
    FAAG44, Senate Optimistic About Roman Construction Timeline
    FDSKJH452, Labor Strikes in Rome Delays Aquaduct
    002, Rome Built in a Day
    003, Roman Construction Unlikely to Meet Deadline
    456, Carthage Sends Envoy to Maximus

    And:

    Index, Serial Number, Ref001
    1, HD456KH45, RTHS1964
    2, HD456KH45, SADA5464
    3, HD456KH45, FAAG44
    4, SADA5464, FDSKJH452
    5, RTHS1964, 002
    6, 444642SSG, 456
    7, FAAG44, 002
    8, FAAG44, 003


    9, FDSKJH452, 003

    Please note the one to many relationship between Literature.Serial_Number and Reference.SerialNumber.

    After I create a one to many relationship using an inner join my query returns a dataset that I expect. So onto the question. I would like to be able to present this query result in a form or report that has a drill down function similar to an excel pivot table but with multiple layers. The +/- being an expand collapse control object. The output would look something like this:


    - HD456KH45
    - RTHS1964
    - 002
    Null
    + SADA5464
    + FAAG44


    Any information on how to display multilayer drill downs would be greatly appreciated.

    PS I also posted a similar question in an excel forum for pivot tables as I can try to create the same data structure but run into the multiple layer issue with both platforms.
    Last edited by whitechr; 06-15-2021 at 02:24 PM. Reason: Added Cross with Excel

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Access reports basically have no user interaction, so no drill down. Subreports are doable but they don't provide any sort of pivot table functionality. Access forms don't have pivot table capability either. Not sure if a tree view control would give you what you want but I suspect not. Even so, they are tricky to use and lack documentation and a full property/event listing in the property sheet.

    Another potential method is breaking up data into subforms which are then placed on a tab control. Last and probably least useful to you would be loading a table into a subform control where that table has subform data sheet enabled. However that would only provide one drill down level, unless perhaps the "child" tables also have subform data sheets on them. That is something I never use because I adhere to the principle of not exposing tables to users so I'm not sure of what you'd get with that. This might be a job for Excel.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.

    Something to be aware of:
    Quote Originally Posted by whitechr View Post
    I have two tables (Literature and Reference)

    Literature has two columns (Serial_Number(unique) and Title)
    Reference has three columns (Index(unique), SerialNumber and Reference)
    You should not have "Index" as an object name - it is a reserved word in Access, ODBC and SQL (JET reserved (kb248738);SQL Server reserved;ODBC (kb125948))
    A better field name would be "ReferenceID" or even better "ReferenceID_PK"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 08-24-2020, 09:01 PM
  2. Multiple Drill Down Filter using DoCmd.ApplyFilter
    By sparker75 in forum Programming
    Replies: 1
    Last Post: 07-30-2018, 10:40 AM
  3. Replies: 4
    Last Post: 12-09-2015, 09:02 AM
  4. Replies: 3
    Last Post: 07-22-2014, 11:37 AM
  5. List value query - 2 layers deep
    By blargh88 in forum Queries
    Replies: 12
    Last Post: 02-22-2012, 03:22 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums