Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: MTBF Analysis in Access query

  1. #1
    Board Regular
    Join Date
    Aug 2009
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MTBF Analysis in Access query

    I am trying to determine the amount of days between two dates on a matching serial number. My data has serial numbers that were seen in the past and I am only using the same serial number if it appears twice. I want to know the amount of days in between the date fields we saw these last. The dates are on different line entries in order by serial number and oldest to newest date.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,304
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: MTBF Analysis in Access query

    Do you want one record per serial number (the last time this was seen)? Or many records (for each record, the last time it was seen before that one)? Also can you give some sample data otherwise I'll be making it up on my own and that will be less clear. Which actually I've already done so here is my sample query with my own made up data:

    Code:
    SELECT 
    	t1.SerialNumber, 
    	abs(max(DateDiff("d", t3.Date_, t1.TransDate))) as DateDifference
    FROM
    	Table1 t1
    	inner join
    	(
    		select 
    			t2.SerialNumber as SerialNumber_, 
    			Max(t2.TransDate) as Date_
    		from 
    			Table1 t2
    		group by 
    			t2.SerialNumber
    	) t3
    	on t1.SerialNumber = t3.SerialNumber_
    WHERE
    	t1.TransDate < t3.Date_
    GROUP BY
    	t1.SerialNumber

    Sample data:
    ----------------------------
    | SerialNumber | TransDate |
    ----------------------------
    | CAT1         |  1/5/2019 |
    | CAT1         |  1/6/2019 |
    | CAT1         |  1/8/2019 |
    | CAT2         |  1/1/2019 |
    | CAT2         |  1/1/2019 |
    | CAT2         |  1/1/2019 |
    | CAT3         |  1/5/2019 |
    | CAT3         | 1/10/2019 |
    | CAT3         | 1/11/2019 |
    ----------------------------


    Results:
    ---------------------------------
    | SerialNumber | DateDifference |
    ---------------------------------
    | CAT1         |              2 |
    | CAT3         |              1 |
    ---------------------------------

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Aug 2009
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MTBF Analysis in Access query

    Sorry for the delayed reply.
    Below is some data. I need to match the Barcodes with each other, then subtract the Date_Warranty from the Date_Ent.
    Since this data comes in is not sort by Barcode, I need to match the Barcode then subtract he next highest date from the previous Date_Warranty.

    RA_Num BarCode Date_Ent Date_Cls Date_Warranty MTBF
    RT01210082 A-402731 1/21/2019 2/7/2019 3/4/2019 1
    RT03050181 A-402731 3/5/2019 4/1/2019 4/8/2019 2
    RT04100127 A-402731 4/10/2019 O DW #N/A
    RS10110055 A-402823 10/11/2018 11/13/2018 3/21/2019 0
    RT03210036 A-402823 3/21/2019 O DW #N/A
    RT02060116 A-403318 2/6/2019 2/22/2019 4/22/2019 11
    RT05030125 A-403318 5/3/2019 O DW #N/A
    RS09050117 A-362926 9/5/2018 2/21/2019 3/4/2019 1
    RT03050203 A-362926 3/5/2019 C DW #N/A
    RT03010112 A-365385 3/1/2019 4/1/2019 4/9/2019 7
    RT04160177 A-365385 4/16/2019 C DW #N/A
    RS07090131 A-365722 7/9/2018 3/14/2019 4/12/2019 29
    RT04120118 A-365722 4/12/2019 C DW #N/A
    RT02070058 A-366077 2/7/2019 2/25/2019 3/4/2019 28
    RT04010115 A-366077 4/1/2019 4/23/2019 4/29/2019 3
    RT05020115 A-366077 5/2/2019 C DW #N/A

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •