# MTBF Analysis in Access query

#### Tcurtis

##### Board Regular
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.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### xenou

##### MrExcel MVP, Moderator
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
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_Num BarCode Date_Ent Date_Cls Date_Warranty MTBF RT01210082 A-402731 1/21/2019 2/7/2019 3/4/2019 1 RT03050181 A-402731 3/5/2019 4/1/2019 4/8/2019 2 RT04100127 A-402731 4/10/2019 O DW #N/A RS10110055 A-402823 10/11/2018 11/13/2018 3/21/2019 0 RT03210036 A-402823 3/21/2019 O DW #N/A RT02060116 A-403318 2/6/2019 2/22/2019 4/22/2019 11 RT05030125 A-403318 5/3/2019 O DW #N/A RS09050117 A-362926 9/5/2018 2/21/2019 3/4/2019 1 RT03050203 A-362926 3/5/2019 C DW #N/A RT03010112 A-365385 3/1/2019 4/1/2019 4/9/2019 7 RT04160177 A-365385 4/16/2019 C DW #N/A RS07090131 A-365722 7/9/2018 3/14/2019 4/12/2019 29 RT04120118 A-365722 4/12/2019 C DW #N/A RT02070058 A-366077 2/7/2019 2/25/2019 3/4/2019 28 RT04010115 A-366077 4/1/2019 4/23/2019 4/29/2019 3 RT05020115 A-366077 5/2/2019 C DW #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
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
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
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
Xenou,

How does Access know what table t3 is ?

#### xenou

##### MrExcel MVP, Moderator
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
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
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,

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