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

Discussion created by mergatroid_1 on May 26, 2017
Latest reply on May 30, 2017 by mergatroid_1


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.