I've tried building it with an employee table, an auction table and an inspection table between the two (with foreign keys from both employee and auction). I'm still not able to make it work right.
That sounds right to me. When you say "I'm still not able to make it work right" what exactly do you mean?
For the Inspections per week you can create a calc field using WeekOfYear () function and run your report sorted/subsummary on that field.
I think I'm just making myself confused with the fields. For example, the field 'Week' is just a text field and will simply say "Week 1", "Week 2", "Week 3", etc. It isn't a date field.
That being the case, would the "Week" field belong in the Inspection table or the Auction table?
In Week 1, there could be 50 inspections done by 5 different inspectors at 3 different auctions.
It seems like I'm not doing a great job at getting rid of the many-to-many relationships, even though I thought I did : /
Here is the basic table structure.
In Inspection, Week is a text field and Number of Inspections is a number field.
In Auction, Site is a text field and Year is a number field.
Are these three tables enough to be able to show the numbers of inspections per week; the number of inspections per week per site; the number of inspections per week, per site, per employee?
I haven't had any luck making that work and I'm sure this is fairly simple.
Add Auction Date to the Auction table and move Week to that table as well. I'd have Week automatically populate with the WeekOfYear () function.
The Number of Inspections can be a summary field. Base the report (or table view) on Inspection and do a sub summary part based on Auction::Week. Add a new sub summary section for Auction::Site and another new one for Employee::Last.
Inspection::Number of Inspections can go on each of those sub summary lines
I would like to be able to accomplish this with the least amount of tables and complexity that is possible. To keep it simple and clean.
This always smells like "Use ExecuteSQL"
Yes, that absolutely helps! I was trying to set it up with no Date field and found it impossible to do. Kudos for breaking it down for me. Thank you!
I've never used "ExecuteSQL", although I've read about it plenty. I must admit, anything with "SQL" in it still seems rather intimidating to me.
I don't want to eat up your time, but how would that be most useful for keeping the table structure minimal and clean?
Thank you for sharing your insight!
Great - glad it was helpful.
Good luck with your project.
instead of creating a relationship - which means a new TO and a new line in your diagram - and getting data through it, you get the same data without a relationship via SQL.