8 Replies Latest reply on May 30, 2017 12:17 PM by mergatroid_1

    Performing a date range search on a table that summarizes multiple portals



      I'm working on a database that tracks researcher usage for a university archive. One of my tables, called Collections, is used to summarize different types of collection usage for each collection over time. These summary fields come from 5 related tables (Researcher Visit Collection Usage, Class Visit Collection Usage, Publications Exhibitions, Digitization, and Digitization Collections Join), each of which has a date field included in them. My client would like to be able to run a date range search so that they can see this summarized information for specific periods of time (most likely by semester, though they'd like to be able to run a search of any length).


      Here's where each of the values from related table are coming from on the Collections layout:


      Staff Reference Hours: Researcher Visit Collection Usage

      Class Instruction: Class Visit Collection Usage

      Researcher Hours: Researcher Visit Collection Usage

      Publications/Exhibitions: Publications Exhibitions

      Digitization Staff Hours: Digitization

      Total Files Created Internal: Digitization Collections Join

      Number of Existing Files Furnished: Researcher Visit Collection Usage


      I'm having trouble figuring out how to construct a search that would allow me to see the collection usage values from all of these related tables in the Collection layout. In fact, I'm even having trouble performing a search that even limits the dates from one of the related tables.


      I'm attaching screenshots of the relationship map, the fields on the relevant tables, and the Collection layout. I would be extremely grateful for any help in sorting this out.