MTBF Analysis in Access query

[Tcurtis]

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.

 

[xenou]

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).

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:

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

 

[welshgasman]

Probably the word Date which is a reserved word.?

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/do...e/how-to-break-and-combine-statements-in-code

 

[xenou]

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:

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:

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

 

[welshgasman]

oops, my apologies, and thanks for the clarification.

 

[xenou]

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:

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_

 

[Tcurtis]

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.

 

[xenou]

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).

 

[Tcurtis]

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.

 

[Tcurtis]

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.

[TABLE="width: 271"]
<colgroup><col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2872;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2247;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3555;"> <col width="42" style="width: 31pt; mso-width-source: userset; mso-width-alt: 1479;"> <tbody>[TR]
[TD="width: 81, bgcolor: transparent"]RA_Num[/TD]
[TD="width: 63, bgcolor: transparent"]BarCode[/TD]
[TD="width: 76, bgcolor: transparent"]Date_Ent[/TD]
[TD="width: 100, bgcolor: transparent"]Date_Warranty[/TD]
[TD="width: 42, bgcolor: transparent"]MTBF[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RS08280184[/TD]
[TD="bgcolor: transparent"]A-335663[/TD]
[TD="bgcolor: transparent, align: right"]8/28/2018[/TD]
[TD="bgcolor: transparent, align: right"]1/31/2019[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT02060109[/TD]
[TD="bgcolor: transparent"]A-335663[/TD]
[TD="bgcolor: transparent, align: right"]2/6/2019[/TD]
[TD="bgcolor: transparent, align: right"]3/28/2019[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT02190119[/TD]
[TD="bgcolor: transparent"]A-335757[/TD]
[TD="bgcolor: transparent, align: right"]2/19/2019[/TD]
[TD="bgcolor: transparent, align: right"]3/14/2019[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT03190145[/TD]
[TD="bgcolor: transparent"]A-335757[/TD]
[TD="bgcolor: transparent, align: right"]3/19/2019[/TD]
[TD="bgcolor: transparent, align: right"]4/22/2019[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RS12280034[/TD]
[TD="bgcolor: transparent"]A-335780[/TD]
[TD="bgcolor: transparent, align: right"]12/28/2018[/TD]
[TD="bgcolor: transparent, align: right"]3/8/2019[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT03220089[/TD]
[TD="bgcolor: transparent"]A-335780[/TD]
[TD="bgcolor: transparent, align: right"]3/22/2019[/TD]
[TD="bgcolor: transparent, align: right"]4/30/2019[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT05060079[/TD]
[TD="bgcolor: transparent"]A-335780[/TD]
[TD="bgcolor: transparent, align: right"]5/6/2019[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[xenou]

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

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).