Looking for the best way to query current year data with many lookup fields.

[rodeogal]

After many hours of cleanup on the database I am helping with, I have discovered that there is a very large annual report that needs to be compiled each year. As you can see in the attached example,
Views: 9 Size: 380.0 KB">Sample.accdb there are MANY lookup fields for this table. In the actual database, there are separate tables for the values in each of these lookup fields. The annual report only counts events that occurred during the current calendar year:

Number of wins/losses/mixed reviews at level #1, level #2, level #3 (each win/loss/mixed counted separately)
Number of cases settled at level #1
Number of hearings held
Total of all settlements/awards
And so much more.....

How would I accomplish this? The database continues to grow, (approx 600 cases at this time) as some cases can take years to work through the process. They may lose at Level#1 in 2017, but win Level#2 in 2018. If I add a date/time column ahead of each of these fields, that makes the database clumsy to use and increases the number of queries that must be designed to compile the annual report.

My friend that I am helping would like for all this to happen with the click of a button and send the report to excel. However, since I am not a programmer, I don't know if this can even be done, and I am just about ready to punt. Any ideas or advice would be greatly appreciated!

[isladogs]

You've only included one table with a few records.
It would makesense to include the report and all items needed to make it run ... otherwise all anyone can do is guess blindly in the dark

[orange]

In addition to Colin's comment, most responders would suggest that you should not use lookups at the table field level. Lookups on forms is a more global approach to solution.

Without knowing more details of the "business involved", it seems that normalization and better table structure may be issues with the current set up. This is more of a generalized guess than review of facts.
Good luck.

[rodeogal]

I'm sorry. I tried to upload more information in the database, but I got a message saying that the file was too large. Maybe I can offer more specific information:

This database is used by attorneys to track workplace legal cases, e.g. EEO, Whistle Blower, etc. There is a process that each case travels through, and they are wanting to track total events/processes for the calendar year.

The lookup fields are at the form level.

The report is simply a bunch of lines with totals on it:
Number of wins at level#1 for calendar year 8
Number of losses at level#1 for calendar year 6
Number of hearings held 4
Number of cases settled at level#1 5
Total of all settlements/awards $35,000.00

Since I need to be able to query by date on the majority of fields, should I place a date/time column with each field? My inexperienced mind tells me this is the only way to know what events happened in each year, e.g. 2017, 2018, etc.

I believe it is going to take about 35 separate queries to compile the actual report since each lookup value will need to be queried. At this point, I am only concerned with the most efficient way to design (redesign) the database to be able to extract the needed information.

I hope this additional information helps. I appreciate your time!

[orange]

Did you:
-do a compact and repair to remove unavailable space, then
-create a zip file with a copy of the database?

This should reduce the size considerably.

As for

I am only concerned with the most efficient way to design (redesign) the database to be able to extract the needed information.

This all starts with normalized tables and relationships. Then some test data and scenarios to retrieve the desired output from the stored data...and a few adjustments and iterations until output matches your needs/requirements.