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]

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:

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]

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.

[TABLE="width: 399"]
<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>[TR]
[TD="width: 91, bgcolor: transparent"]RA_Num[/TD]
[TD="width: 73, bgcolor: transparent"]BarCode[/TD]
[TD="width: 87, bgcolor: transparent"]Date_Ent[/TD]
[TD="width: 89, bgcolor: transparent"]Date_Cls[/TD]
[TD="width: 123, bgcolor: transparent"]Date_Warranty[/TD]
[TD="width: 69, bgcolor: transparent"]MTBF[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT01210082[/TD]
[TD="bgcolor: transparent"]A-402731[/TD]
[TD="bgcolor: transparent"]1/21/2019[/TD]
[TD="bgcolor: transparent"]2/7/2019[/TD]
[TD="bgcolor: transparent"]3/4/2019[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT03050181[/TD]
[TD="bgcolor: transparent"]A-402731[/TD]
[TD="bgcolor: transparent"]3/5/2019[/TD]
[TD="bgcolor: transparent"]4/1/2019[/TD]
[TD="bgcolor: transparent"]4/8/2019[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT04100127[/TD]
[TD="bgcolor: transparent"]A-402731[/TD]
[TD="bgcolor: transparent"]4/10/2019[/TD]
[TD="bgcolor: transparent"]O[/TD]
[TD="bgcolor: transparent"]DW[/TD]
[TD="bgcolor: transparent"]#N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RS10110055[/TD]
[TD="bgcolor: transparent"]A-402823[/TD]
[TD="bgcolor: transparent"]10/11/2018[/TD]
[TD="bgcolor: transparent"]11/13/2018[/TD]
[TD="bgcolor: transparent"]3/21/2019[/TD]
[TD="bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT03210036[/TD]
[TD="bgcolor: transparent"]A-402823[/TD]
[TD="bgcolor: transparent"]3/21/2019[/TD]
[TD="bgcolor: transparent"]O[/TD]
[TD="bgcolor: transparent"]DW[/TD]
[TD="bgcolor: transparent"]#N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT02060116[/TD]
[TD="bgcolor: transparent"]A-403318[/TD]
[TD="bgcolor: transparent"]2/6/2019[/TD]
[TD="bgcolor: transparent"]2/22/2019[/TD]
[TD="bgcolor: transparent"]4/22/2019[/TD]
[TD="bgcolor: transparent"]11[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT05030125[/TD]
[TD="bgcolor: transparent"]A-403318[/TD]
[TD="bgcolor: transparent"]5/3/2019[/TD]
[TD="bgcolor: transparent"]O[/TD]
[TD="bgcolor: transparent"]DW[/TD]
[TD="bgcolor: transparent"]#N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RS09050117[/TD]
[TD="bgcolor: transparent"]A-362926[/TD]
[TD="bgcolor: transparent"]9/5/2018[/TD]
[TD="bgcolor: transparent"]2/21/2019[/TD]
[TD="bgcolor: transparent"]3/4/2019[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT03050203[/TD]
[TD="bgcolor: transparent"]A-362926[/TD]
[TD="bgcolor: transparent"]3/5/2019[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]DW[/TD]
[TD="bgcolor: transparent"]#N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT03010112[/TD]
[TD="bgcolor: transparent"]A-365385[/TD]
[TD="bgcolor: transparent"]3/1/2019[/TD]
[TD="bgcolor: transparent"]4/1/2019[/TD]
[TD="bgcolor: transparent"]4/9/2019[/TD]
[TD="bgcolor: transparent"]7[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT04160177[/TD]
[TD="bgcolor: transparent"]A-365385[/TD]
[TD="bgcolor: transparent"]4/16/2019[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]DW[/TD]
[TD="bgcolor: transparent"]#N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RS07090131[/TD]
[TD="bgcolor: transparent"]A-365722[/TD]
[TD="bgcolor: transparent"]7/9/2018[/TD]
[TD="bgcolor: transparent"]3/14/2019[/TD]
[TD="bgcolor: transparent"]4/12/2019[/TD]
[TD="bgcolor: transparent"]29[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT04120118[/TD]
[TD="bgcolor: transparent"]A-365722[/TD]
[TD="bgcolor: transparent"]4/12/2019[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]DW[/TD]
[TD="bgcolor: transparent"]#N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT02070058[/TD]
[TD="bgcolor: transparent"]A-366077[/TD]
[TD="bgcolor: transparent"]2/7/2019[/TD]
[TD="bgcolor: transparent"]2/25/2019[/TD]
[TD="bgcolor: transparent"]3/4/2019[/TD]
[TD="bgcolor: transparent"]28[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT04010115[/TD]
[TD="bgcolor: transparent"]A-366077[/TD]
[TD="bgcolor: transparent"]4/1/2019[/TD]
[TD="bgcolor: transparent"]4/23/2019[/TD]
[TD="bgcolor: transparent"]4/29/2019[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RT05020115[/TD]
[TD="bgcolor: transparent"]A-366077[/TD]
[TD="bgcolor: transparent"]5/2/2019[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]DW[/TD]
[TD="bgcolor: transparent"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[xenou]

Looks like my query basically works. I just changed the field names (which you really should have done yourself already).

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]

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]

Okay. But please note that there is only one table in this query. I am joining the table to itself (a self-join).

 

[welshgasman]

Xenou,

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

 

[xenou]

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.

 

[xenou]

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]

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