MTBF Analysis in Access query

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,649
Office Version
2013
Platform
Windows
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,649
Office Version
2013
Platform
Windows
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
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,649
Office Version
2013
Platform
Windows
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_
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
144
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,649
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Aug 15, 2009
Messages
144
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

Board Regular
Joined
Aug 15, 2009
Messages
144
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>
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,649
Office Version
2013
Platform
Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,099,559
Messages
5,469,423
Members
406,650
Latest member
cc4digital2

This Week's Hot Topics

Top