MTBF Analysis in Access query

T

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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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 |
---------------------------------
 
Upvote 0
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]
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Okay. But please note that there is only one table in this query. I am joining the table to itself (a self-join).
 
Upvote 0
Xenou,

For my benefit please.
How does Access know what table t3 is ?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
issue with Autogenerated custom serial number
Replies
3
Views
670
Micron
M
L
  • Solved
Trouble running an access query in excel vba
Replies
2
Views
217
Legal Tender
L
J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
286
jsand
J
W
  • Solved
Access Query - is it possible?
Replies
5
Views
2K
xenou
xenou
Sunline
  • Question Question
Find exact match, and the closest smallest and highest numbers.
Replies
3
Views
630
Sunline
Sunline

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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