Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: MTBF Analysis in Access query

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

    Default Re: MTBF Analysis in Access query

    Yes, you really mangled that.


    Here's it is again. I discovered that my abs(max(...)) should have been max(abs(...)) to work properly (so we don't need the where clause and as a bonus the barcode that are unique get a "zero" as being zero days different from themselves (this doesn't really distinguish between two records that are on the same day and only one record (which is always on the same day as itself - so this could be good or bad depending on your needs. IF you put the where clause back in then you only get results where there are at least two records on different dates).
    Code:
    SELECT 
    	t1.BarCode,
    	max(abs(DateDiff("d", t3.Date_,t1.Date_Warranty))) as DateDifference
    
    FROM 
    	tbl_MTBF_Multiple_Entries t1
    	INNER JOIN
    	(
    	SELECT t2.BarCode as BarCode_, Max(t2.Date_Warranty) as Date_
    	FROM tbl_MTBF_Multiple_Entries t2
    	GROUP BY t2.BarCode
    	) t3
    	on t1.BarCode=t3.Barcode_
    
    GROUP BY
    	t1.BarCode
    with the WHERE clause:
    Code:
    SELECT 
    	t1.BarCode,
    	max(abs(DateDiff("d", t3.Date_,t1.Date_Warranty))) as DateDifference
    
    FROM 
    	tbl_MTBF_Multiple_Entries t1
    	INNER JOIN
    	(
    	SELECT t2.BarCode as BarCode_, Max(t2.Date_Warranty) as Date_
    	FROM tbl_MTBF_Multiple_Entries t2
    	GROUP BY t2.BarCode
    	) t3
    	on t1.BarCode=t3.Barcode_
    
    WHERE
    	t1.Date_Warranty < t3.Date_
    
    GROUP BY
    	t1.BarCode

    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

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

    Default Re: MTBF Analysis in Access query

    Probably the word Date which is a reserved word.?

    Code:
    Max(tbl_MTBF_Multiple_Entries2.Date_Warranty) as Date
    


    Also I believe continuation character should have a space before it?

    https://docs.microsoft.com/en-us/dot...ements-in-code
    Office 2007
    Access novice. Sometimes trying to give something back

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

    Default Re: MTBF Analysis in Access query

    Hi welshgasman, that is not a continuation character. It is a regular underscore character that is part of the field name alias (i.e., just an ordinary character). Actually you should have realized also that this is not VBA, it is plain vanilla SQL

    One can argue it is ugly but I find that Access does not handle subqueries very well when columns have the same names as containing queries so I often append an underscore to the names of aliases in subqueries. Note, however, that I wouldn't worry about this in other databases - it's more a quirk of Access then something for SQL generally.

    Might Confuse Access:
    Code:
    select 
        t1.ID, 
        t2.MyField as MyField
    from
        Table1 t1
        inner join
        (select ID, Max(MyField) as MyField from Table1 group by ID) t2
        on t1.ID = t2.ID
    Always Works:
    Code:
    select 
        t1.ID, 
        t2.MyField_ as MyField
    from
        Table1 t1
        inner join
        (select ID, Max(MyField) as MyField_ from Table1 group by ID) t2
        on t1.ID = t2.ID

    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

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

    Default Re: MTBF Analysis in Access query

    oops, my apologies, and thanks for the clarification.
    Office 2007
    Access novice. Sometimes trying to give something back

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

    Default Re: MTBF Analysis in Access query

    I suppose another way would be to get the max and min and then use that difference directly - newest minus oldest.
    Here for kicks is SQL for doing this with an alternative strategy:
    Code:
    SELECT 
    	t1.BarCode,
    	DateDiff("d", t3.MinOfDateWarranty, t3.MaxOfDateWarranty) as DateDifference
    FROM 
    	tbl_MTBF_Multiple_Entries t1
    	INNER JOIN
    	(
    		SELECT 
    			t2.BarCode as BarCode_, 
    			Min(t2.Date_Warranty) as MinOfDateWarranty, 
    			Max(t2.Date_Warranty) as MaxOfDateWarranty
    		FROM 
    			tbl_MTBF_Multiple_Entries t2
    		GROUP BY t2.BarCode
    	) t3
    	on t1.BarCode = t3.Barcode_

    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

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

    Default Re: MTBF Analysis in Access query

    This works great but only if there are two records for one barcode. Looking at it I would think that it could handle multiple entries for each barcode.

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

    Default Re: MTBF Analysis in Access query

    It should work for 1, 2, or more...

    With only 1, min and max are the same so difference is zero.
    With two or more, min and max should work also with largest difference resulting (also would be difference of zero if there are two records but they have the same date).

    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

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

    Default Re: MTBF Analysis in Access query

    I knew I was not clear, I usually am not clear. The sql works great for that but I need to know the difference between each date. I need to load up an excel file to show the data better. Let me figure out how to do that and I will load it. Thank you for all the help.

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

    Default Re: MTBF Analysis in Access query

    xenou,
    I need to know the day interval of each line where a barcode matches. The sql gives me the interval between the min Date_Warranty and the max Date_Ent.

    RA_Num BarCode Date_Ent Date_Warranty MTBF
    RS08280184 A-335663 8/28/2018 1/31/2019 6
    RT02060109 A-335663 2/6/2019 3/28/2019 0
    RT02190119 A-335757 2/19/2019 3/14/2019 5
    RT03190145 A-335757 3/19/2019 4/22/2019 0
    RS12280034 A-335780 12/28/2018 3/8/2019 14
    RT03220089 A-335780 3/22/2019 4/30/2019 6
    RT05060079 A-335780 5/6/2019 0

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

    Default Re: MTBF Analysis in Access query

    as a starter, that should be a simple join (self join, technically):

    Code:
    select distinct 
    	t1.BarCode, 
    	DateDiff("d", t1.DateWarranty, t2.DateWarranty) as DateDifference
    from tbl_MTBF_Multiple_Entries t1
    inner join
    from tbl_MTBF_Multiple_Entries t2
    on t1.BarCode = t2.BarCode
    where
    	t1.RA_Num <> t2.RA_Num
    	and t2.DateWarranty >= t1.DateWarranty
    In the criteria excluding RA Nums that are equal so as not to get a records date difference compared to itself. Also the datewarranty greater than DateWarranty criteria is to get one result per pair (record A difference compared to record B, but not record B difference compared to record A ... but won't work if they are different records but same day so that why's DISTINCT).

    Other things could be considered (null dates, especially).

    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

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
  •