Results 1 to 7 of 7
  1. #1
    cyliyu is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14

    DateDiff Based on Serial Number

    How to make a query to calculated 3MonthWTY, NoOfDay and RejectFreq when the Product, UnitSN and UnitDC in the same tblData are matched?




    Click image for larger version. 

Name:	Capture.JPG 
Views:	11 
Size:	65.6 KB 
ID:	42008

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  3. #3
    cyliyu is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    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?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  5. #5
    cyliyu is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Thanks, June for the links.
    I will find out more on the nested query
    The 3MonthWty was based on the NoOfDay < 90 days.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  7. #7
    cyliyu is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Thanks, June7.
    Will read out more about the nested query.
    Just started to learn Access, is different after getting used to Excel Spreadsheet.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-04-2017, 12:37 PM
  2. Replies: 7
    Last Post: 05-30-2016, 06:56 AM
  3. serial number
    By akellaavss in forum Reports
    Replies: 12
    Last Post: 06-21-2015, 11:07 PM
  4. Replies: 6
    Last Post: 02-10-2014, 01:04 PM
  5. Processor Serial Number
    By Azeez_Andaman in forum Programming
    Replies: 2
    Last Post: 08-16-2011, 11:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums