Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: MTBF Analysis in Access query

  1. #1
    Board Regular
    Join Date
    Aug 2009
    Posts
    134
    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,340
    Post Thanks / Like
    Mentioned
    8 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
    134
    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

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

    Default Re: MTBF Analysis in Access query

    Looks like my query basically works. I just changed the field names (which you really should have done yourself already).
    Code:
    SELECT 
    	t1.BarCode, 
    	abs(max(DateDiff("d", t3.Date_, t1.Date_Warranty))) as DateDifference
    FROM
    	Table1 t1
    	inner join
    	(
    		select 
    			t2.BarCode as BarCode_, 
    			Max(t2.Date_Warranty) as Date_
    		from 
    			Table1 t2
    		group by 
    			t2.BarCode
    	) t3
    	on t1.BarCode = t3.BarCode_
    WHERE
    	t1.Date_Warranty < t3.Date_
    GROUP BY
    	t1.BarCode
    Looks like it only works when there really are two records (so if you want to include records that have no match you'll need to adjust for that).

    Also, your date warranty field has dates and non-dates in it (for example, the first record is 3/4/2019, and the third record is "CW" for the warranty dates). This data is basically unusable as is so you'll need some plan to handle the dates and non-dates that are mixed up (I took out the non-dates when I tested the above query). Maybe this is just a mistake in how you posted the data?

    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

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

    Default Re: MTBF Analysis in Access query

    Thank you. I will try this and let you know how it works. I only have 1 table to work with but I can make two tables. I posted the data as seen and forgot that I put a filter identifier in the new jobs that let me know it should not be counted in the current data. This is taken out after the job is closed and has a date in the closed field.

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

    Default Re: MTBF Analysis in Access query

    Okay. But please note that there is only one table in this query. I am joining the table to itself (a self-join).

    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

  7. #7
    Board Regular
    Join Date
    May 2013
    Posts
    586
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MTBF Analysis in Access query

    Xenou,

    For my benefit please.
    How does Access know what table t3 is ?
    Office 2007
    Access novice. Sometimes trying to give something back

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

    Default Re: MTBF Analysis in Access query

    everything in parentheses is a subquery that makes up a "virtual table". Putting t3 after the closing right parenthesis gives it the alias t3 (I could put an AS in there too, same as with column aliases the "as" is optional).

    Note: to clarify the join strategy, the innermost query is for grouping and finding the date differences. Then this virtual table is joined back to the outer table on the barcode ids.

    Curiously I used max() twice. As long as one date is identified as the oldest or newest, then you only need to get the large absolute value of difference between this date and the other dates.

    I suppose another way would be to get the max and min and then use that difference directly - newest minus oldest.
    Last edited by xenou; May 23rd, 2019 at 03:17 PM.

    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

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

    Default Re: MTBF Analysis in Access query

    Also a note for reference - when you use virtual tables, the general rules is that the field names in the subquery or virtual table must be unique.

    FAIL (field ID is duplicated because it is in both tables and a star select is used):
    (select * from t1 inner join t2 on t1.ID = t2.ID) t3

    OKAY (field names are all unique):
    select t1.ID, t1.A, t1.B, t2.X, t2.Y from t1 inner join t2 on t1.ID = t2.ID) as t3

    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

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

    Default Re: MTBF Analysis in Access query

    xenou,
    Thank you so much for the help. I am getting an error when I run the code.
    The SELECT statement include a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect.

    Below is the code I am using. I am novice at this and appreciate the help

    SELECT
    tbl_MTBF_Multiple_Entries.BarCode,


    abs(max(DataDiff("d", tbl_MTBF_Multiple_Entries3.Date_Ent,tbl_MTBF_Multiple_Entries.Date_Warranty))) as DateDifference


    FROM tbl_MTBF_Multiple_Entries INNER JOIN


    (SELECT tbl_MTBF_Multiple_Entries2.BarCode as BarCode_
    Max(tbl_MTBF_Multiple_Entries2.Date_Warranty) as Date_


    FROM
    tbl_MTBF_Multiple_Entries tbl_MTBF_Multiple_Entries2


    GROUP BY
    tbl_MTBF_Multiple_Entries2. BarCode) tbl_MTBF_Multiple_Entries3
    on tbl_MTBF_Multiple_Entries.BarCode=tbl_MTBF_Multiple_Entries3.Barcode_


    WHERE
    tbl_MTBF_Multiple_Entries.Date_Warranty < tbl_MTBF_Multiple_Entries3.Date_


    GROUP BY
    tbl_MTBF_Multiple_Entries.BarCode

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
  •