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.