Weekly summary field

Question

Hello,I have one table called "Tests" which has one record for each Test and some results. One numeric result field is called "Efficiency." There is a field "Type" which is multiple-choice and can be "A", "B", "C", and each Test record also has a "Date Created," of course.&nbsp;I have a separate table called "Parts." This has two fields called "Ref Type" and "Ref Date." I want to have a field in "Parts" which shows the average Efficiency for ALL Tests where Test "Type" = Part "Ref Type" and where Test "Date Created" = "Ref Date."&nbsp;This seems straightforward but I am struggling with how to create this. I'm happy to have an intermediate table for the summary records, but the "Ref Type" and "Ref Date" fields in a summary record aren't available to select for matching criteria in the summary field.&nbsp;Please let me know if you have any suggestions. Thank you!Graeme------------------------------Graeme------------------------------

markshnier__you · Answer

Can you describe your tables in their relationships. Which is the one in which is the many for each relationship?

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

graemehoste · Answer

Mark,The "Tests" and "Parts" tables are currently related with a 1 Part &lt; many Tests relationship. However, I don't want to display the average Test "Efficiency" of the Tests currently related to this Part.&nbsp;Instead, I want to display the average Test "Efficiency" of ALL Test records of Type "A" (for example) where the Test "Date Created" matches the Part's "Ref Date" field or is within the same week as the Part's "Ref Date."&nbsp;So each "Part" record potentially has a different "Ref Date," and this would result in a different set of the full list of "Test" records to be averaged.&nbsp;Does this make sense?&nbsp;Thanks!------------------------------Graeme------------------------------

markshnier__you · Answer

It sounds to me that all you need to do is look up this field Part's "Ref Date"

down to testrs.

Then have a checkbox formula field for

FirstDayOfWeek([test date]) = FirstDayOfWeek([Part's Ref Date])

and limit the average in the summary field to where that checkbox is true.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

graemehoste · Answer

Thanks for your suggestion. I started implementing this but I don't think it will work in my situation. I want every Part to look at ALL Test records to get the average "Efficiency" for all Tests which have FirstDayOfWeek([Test Date]) = FirstDayOfWeek([Part Ref Date]).

So if I have the relationship one Part < many Tests, your process only works for the Part to summarize its related Tests, and not all of them. Perhaps I need some intermediate step to facilitate a many-to-many relationship and build up the summary fields, but I can't figure out how to do it.

To break the problem down to an earlier stage, what I really need is to automatically create a weekly summary field or record that summarizes the average Efficiency of all Tests from the previous week. Then I can hopefully relate this table of weekly summaries to my Parts table and pull the reference average Efficiency related to the week from which the Part was built. I'm not sure how to go about these steps though, or if they're possible with native QB capability.

------------------------------
Graeme
------------------------------

markshnier__you · Answer

Unfortunately I think that this is too complicated to solve on the forum. I am not clear what you really need. I think this will take some one on one Zoom time to sort out.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

Forum Discussion

Weekly summary field

6 Replies