Discussions

 View Only
  • 1.  Weekly summary field

    Posted 10-06-2021 08:20
    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. 

    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." 

    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. 

    Please let me know if you have any suggestions. Thank you!
    Graeme

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


  • 2.  RE: Weekly summary field

    Posted 10-06-2021 09:30
    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
    ------------------------------



  • 3.  RE: Weekly summary field

    Posted 10-06-2021 09:45
    Edited by Graeme Hoste 10-06-2021 09:48
    Mark,
    The "Tests" and "Parts" tables are currently related with a 1 Part < many Tests relationship. However, I don't want to display the average Test "Efficiency" of the Tests currently related to this Part. 

    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." 

    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. 

    Does this make sense? 
    Thanks!

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



  • 4.  RE: Weekly summary field

    Posted 10-06-2021 12:20
    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
    ------------------------------



  • 5.  RE: Weekly summary field

    Posted 10-06-2021 16:14
    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
    ------------------------------



  • 6.  RE: Weekly summary field

    Posted 10-06-2021 22:35
    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
    ------------------------------



  • 7.  RE: Weekly summary field

    Posted 10-07-2021 08:00
    Mark,
    No problem. I appreciate the help. I'm sure I'll figure out some workaround for this. 

    Thank you,
    Graeme

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