MTBF Analysis in Access query

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
149
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.
 
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
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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_
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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_NumBarCodeDate_EntDate_WarrantyMTBF
RS08280184A-3356638/28/20181/31/20196
RT02060109A-3356632/6/20193/28/20190
RT02190119A-3357572/19/20193/14/20195
RT03190145A-3357573/19/20194/22/20190
RS12280034A-33578012/28/20183/8/201914
RT03220089A-3357803/22/20194/30/20196
RT05060079A-3357805/6/2019 0
<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> </tbody>
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top