MTBF Analysis in Access query

Tcurtis

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

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

xenou

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

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
144
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_NumBarCodeDate_EntDate_ClsDate_WarrantyMTBF
RT01210082A-4027311/21/20192/7/20193/4/20191
RT03050181A-4027313/5/20194/1/20194/8/20192
RT04100127A-4027314/10/2019ODW#N/A
RS10110055A-40282310/11/201811/13/20183/21/20190
RT03210036A-4028233/21/2019ODW#N/A
RT02060116A-4033182/6/20192/22/20194/22/201911
RT05030125A-4033185/3/2019ODW#N/A
RS09050117A-3629269/5/20182/21/20193/4/20191
RT03050203A-3629263/5/2019CDW#N/A
RT03010112A-3653853/1/20194/1/20194/9/20197
RT04160177A-3653854/16/2019CDW#N/A
RS07090131A-3657227/9/20183/14/20194/12/201929
RT04120118A-3657224/12/2019CDW#N/A
RT02070058A-3660772/7/20192/25/20193/4/201928
RT04010115A-3660774/1/20194/23/20194/29/20193
RT05020115A-3660775/2/2019CDW#N/A
<colgroup><col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3242;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2588;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3100;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;"> <col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4380;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2446;"> <tbody> </tbody>
 

xenou

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

Tcurtis

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

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,630
Office Version
2013
Platform
Windows
Okay. But please note that there is only one table in this query. I am joining the table to itself (a self-join).
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
779
Xenou,

For my benefit please.
How does Access know what table t3 is ?
 

xenou

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

xenou

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

Tcurtis

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

Watch MrExcel Video

Forum statistics

Threads
1,096,350
Messages
5,449,898
Members
405,578
Latest member
Bossie

This Week's Hot Topics

Top