This data looks familiar. Didn't you have another thread somewhere about calculating RejectFreq and I provided some code? Well, think I found it.
What you want now is more difficult.
This RejectFreq is essentially a running sum by group. Review https://docs.microsoft.com/en-us/off...g-totals-query
The NoOfDay is calculating elapsed period between 2 records. Pulling value from another record in same table needs nested query. Review http://allenbrowne.com/subquery-01.html#AnotherRecord
I don't know what criteria for 3MonthWTY is.
I am not sure can do all of this in one query.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I am using
=DCount("*","tblData","[Product]='" & [Product] & "' AND [UnitNo]='" & [UnitNo] & "' AND [UnitDC]='" & [UnitDC] & "'") to calculate the rejectFreq.
How can I obtain 3MonthWty and NoOfDay values?
I edited my previous post probably after you read it. Review again.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thanks, June for the links.
I will find out more on the nested query
The 3MonthWty was based on the NoOfDay < 90 days.
Running sum would be fairly easy to do on a report where textbox has RunningSum property.
Calculate 3MonthWTY in textbox on report: =IIf(NoOfDay < 90, "Yes", "No")
Build nested query to accomplish the NoOfDay calc then use that query as source for report. Use report Sorting & Grouping features.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thanks, June7.
Will read out more about the nested query.
Just started to learn Access, is different after getting used to Excel Spreadsheet.