Qurey Number of days to complete operation.

[Barryg]

Hey all,



I am fairly new to query's, the introduction that I had was not in-depth enough to figure this out.

I have two fields in my table= (tblNPI) FIELD 1 = BRFrcv'd, FIELD 2 = BRF/BOF_Complete. both of these fields are date fields.

Multiple records in the table spanning days to possibly one or two months.
we process an NPI and I enter the date that the BRF document was received into the BRFrcv'd field. Once that process is complete I then enter a date into the BRF/BOF_Complete field.

At the end of the month I want to run a query that tells me the average number of days it took to complete any BRFs for that month.
Each records PK is an auto gen number, each record also has a BRF or BOF number associated with each record that is unique as well.

I was thinking that a nested query might be the correct way to do this but for the life of me cannot see it.

Can anyone point me in the right direction. I have looked at the DateDiff function and that is confusing me somewhat. I do not have any good examples that explain it.

any help would be greatly appreciated.

- Barryg

[June7]

Is the Complete date entered on the same record as Rcv'd date? What do you mean by 'multiple records'? Show sample raw data and sample desired output.


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Barryg]

Is the Complete date entered on the same record as Rcv'd date? What do you mean by 'multiple records'? Show sample raw data and sample desired output.


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

OK, I have some questions for you then:

First Question - Is Access smart enough to update any queries that may be in place when I remove the special character from the field name?

Second question - how would you like to see the sample data of rows. copy and past or some thing else?

[June7]

Access will recognize change of table, query, field names in many instances, but not everywhere, such as reference within expression or in VBA.

Data sample can be provided by attaching images, database file, or building tables in the Advanced Post editor.

[Barryg]

Access will recognize change of table, query, field names in many instances, but not everywhere, such as reference within expression or in VBA.

Data sample can be provided by attaching images, database file, or building tables in the Advanced Post editor.

I copied and pasted this out of my db.

All fields for the [DateBRFRcvd] and the last column [BRF/BOF_Complete] have dates in them. So what I want to do is pull a query that will give return the average number of days to process the number of BRFs completed in any given month. So for example in February there were 13 BRF/BOF's competed. if each one of the 13 took a different number of days to complete, what would be the average number of days for the month of Feb?

I am just not seeing how to achieve this answer. It would be nice if the query would say what month? and I would answer with either 1, 2, 11 or Jan. Feb. or November or something to that effect. I'm easy, but just don't get how to formulate this. A friend of mine said this would take possibly two different queries, one to count and one to average. I thought that if that were the case, then I should be able to use a nested query to achieve that result but then I am having a difficult time understanding this.

sorry for my ignorance. (I do plan on changing the last field from BRF/BOF_Complete to BRF_BOF_Complete. (can I use a dash? BRF-BOF_Complete)

NPI_ID	BRF/BOF Number	Assembly_ID	JobNumber	AnticipatedBuildDate	PlanningStartDate	Customer_ID	Engineer_ID	FollowUpQty	ProgramMgr_ID	ActualNPIStartDate	DateBRFRcvd	NPIQty	Status_ID	PSWIReady	NPIShipDate	DateOfPostmortem	PostMortemClosed	ReleasingProduct	ArenaDocsUploaded	ProductionDocsLinked	PgmMgrSign	NPICoordSignoff	ECNSignoff	PlanningSignoff	ProductMgrSignoff	QualitySignoff	ProcessEngSignoff	TestEngSignoff	BRF/BOF_Complete
33	MER BOF-000040	28	FIB0020252-0200	3/3/2014	3/3/2014	11	5	0	3	3/3/2014	2/27/2014	90	10	2/28/2014	3/11/2014			Undetermined			2/26/2014	2/28/2014	2/26/2014	2/26/2014	2/26/2014	3/28/2014	2/27/2014	2/27/2014	3/3/2014
35	MER BOF-000038	30	FIB0020352-0100	2/28/2014	2/28/2014	12	5	0	5	2/27/2014	2/27/2014	50	12	2/27/2014	2/28/2014			Undetermined			2/26/2014	2/27/2014	2/26/2014	2/26/2014	2/26/2014	2/27/2014	2/27/2014	2/26/2014	2/27/2014
37	MER BOF-000039	31	FIB0020352-0200	2/28/2014	2/28/2014	12	5	0	5	2/28/2014	2/27/2014	50	12	2/27/2014	2/28/2014			Undetermined			2/26/2014	2/28/2014	2/26/2014	2/26/2014	2/26/2014	2/28/2014	2/27/2014	2/26/2014	2/28/2014
38	MER BRF-000700	32	FAB0020335-0100	3/10/2014	3/5/2014	7	2	0	5	3/5/2014	2/28/2014	8	11	3/4/2014	3/10/2014			Undetermined			2/28/2014	3/4/2014	2/28/2014	2/28/2014	2/28/2014	3/4/2014	3/3/2014	3/4/2014	3/4/2014
39	MER BRF-000701	33	FAB0020373-0007	3/6/2014	3/6/2014	5	2	5	3	3/6/2014	3/4/2014	1	18	3/5/2014	3/19/2014	3/20/2014	3/21/2014	Yes	3/21/2014		3/4/2014	3/5/2014	3/4/2014	3/4/2014	3/6/2014	3/5/2014	3/4/2014	3/5/2014	3/6/2014
42	MER BRF-000704	36	FAB0020390-0100	3/12/2014	3/13/2014	4	5	0	5	3/13/2014	3/10/2014	30	12	3/11/2014	3/18/2014			Undetermined			3/10/2014	3/12/2014	3/11/2014	3/10/2014	3/10/2014	3/12/2014	3/10/2014	3/13/2014	3/13/2014
43	MER BOF-000705	37	FIB0020373-0012	3/14/2014	3/19/2014	5	2	100	3	3/19/2014	3/13/2014	67	8	3/14/2014				Undetermined			3/13/2014	3/17/2014	3/13/2014	3/13/2014	3/13/2014	3/17/2014	3/13/2014	3/14/2014	3/18/2014
44	MER BOF-000041	38	FIB0020363-0100	3/14/2014	3/17/2014	9	2	0	5	3/17/2014	3/14/2014	140	12	3/17/2014	3/21/2014			Undetermined			3/13/2014	3/17/2014	3/13/2014	3/13/2014	3/13/2014	3/17/2014	3/13/2014	3/13/2014	3/17/2014
45	MER BOF-000042	39	FIB0020323-0100	3/17/2014	3/17/2014	7	2	0	5	3/17/2014	3/17/2014	114	12	3/17/2014	3/21/2014			Undetermined			3/17/2014	3/17/2014	3/17/2014	3/17/2014	3/17/2014	3/17/2014	3/17/2014	3/17/2014	3/17/2014
19	MER BRF-000692	16	FIB0020297-0100	2/7/2014	2/14/2014	5	2	1000	3	2/26/2014	1/28/2014	500	17	1/30/2014	3/12/2014	3/13/2014	3/14/2014	Yes	3/14/2014	3/17/2014	1/27/2014	2/10/2014	1/28/2014	1/28/2014	1/30/2014	2/4/2014	2/10/2014	2/3/2014	2/10/2014
20	MER BRF-000693	17	FAB0020301-0100	2/12/2014	2/12/2014	5	2	500	3	2/7/2014	1/29/2014	110	12	1/30/2014	2/19/2014	3/3/2014		Undetermined			1/29/2014	2/5/2014	1/29/2014	1/31/2014	1/31/2014	2/6/2014	2/3/2014	2/5/2014	2/6/2014
21	MER BRF-000694	18	FAB0020307-0100	2/3/2014	2/4/2014	4	5	0	5	2/4/2014	1/30/2014	80	17	2/3/2014	2/6/2014	2/13/2014	3/10/2014	No	3/10/2014	3/11/2014	1/30/2014	2/4/2014	1/31/2014	1/30/2014	1/30/2014	2/4/2014	2/3/2014	2/3/2014	2/4/2014
22	MER BOF-000033	19	FIB0020318-0100	3/1/2014	2/27/2014	12	5	0	5	3/3/2014	2/5/2014	1200	18	2/6/2014	3/4/2014	3/19/2014	3/19/2014	Yes	3/19/2014		2/5/2014	2/6/2014	2/5/2014	2/5/2014	2/5/2014	2/6/2014	2/5/2014	2/5/2014	2/6/2014
23	MER BOF-000031	20	FIB0020309-0100	2/5/2014	2/10/2014	8	5	0	2	2/7/2014	2/6/2014	75	17	2/6/2014	2/12/2014	2/14/2014	2/14/2014	Yes	2/14/2014	2/18/2014	1/31/2014	2/6/2014	2/6/2014	2/3/2014	2/6/2014	2/7/2014	2/6/2014	2/6/2014	2/7/2014
25	MER BOF-000032	21	FIB0020320-0100	2/17/2014	2/17/2014	8	5	0	2	2/13/2014	2/7/2014	30	17	2/11/2014	2/21/2014	3/27/2014	3/3/2014	No	3/5/2014	3/6/2014	2/5/2014	2/11/2014	2/7/2014	2/5/2014	2/7/2014	2/12/2014	2/7/2014	2/7/2014	2/12/2014
26	MER BRF-000696	22	FAB0020373-0006	2/24/2014	3/6/2014	5	2	200	3	3/6/2014	2/11/2014	180	18	2/12/2014	2/12/2014	3/20/2014	3/21/2014	Yes	3/21/2014		2/11/2014	3/4/2014	2/12/2014	3/4/2014	3/4/2014	3/5/2014	2/12/2014	2/13/2014	3/5/2014
27	MER BRF-000697	23	FAB0020373-0005	2/24/2014	3/6/2014	5	2	100	3	3/6/2014	2/11/2014	65	18	2/12/2014	3/19/2014	3/20/2014	3/21/2014	Yes	3/21/2014		2/11/2014	3/4/2014	2/12/2014	3/4/2014	3/4/2014	3/4/2014	2/12/2014	2/13/2014	3/5/2014
30	MER BOF-000035	25	FIB0020288-0100	3/5/2014	2/28/2014	14	2	0	5	3/10/2014	2/19/2014	1250	11	2/20/2014				Undetermined			2/18/2014	2/20/2014	2/19/2014	2/18/2014	2/19/2014	2/20/2014	2/18/2014	2/19/2014	2/20/2014
31	MER BRF-000699	26	FAB0020343-0100	3/12/2014	3/24/2014	10	5	0	5	3/19/2014	2/20/2014	125	8	3/14/2014				Undetermined			2/19/2014	3/20/2014	2/20/2014	2/20/2014	2/25/2014	3/3/2014	3/20/2014	2/20/2014	3/20/2014

[June7]

Not showing the Rec'd and Completed fields. I will assume they are both populated for each record. Which field should be use to determine the month the record should be summarized in?

Consider:

SELECT Month(PlanningStartDate) AS AvgMon, Avg([BRF/BOF_Complete] - [DateBRFRcvd] + 1) AS AvgDays FROM tablename GROUP BY Month(PlanningStartDate);

I show adding 1 to the elapsed days calculation because it will be short one day otherwise. I.e., if both dates are the same, the result will be 0, not 1.

Day is the default unit when calculating date values. If you prefer other units (years, hours, minutes) use DateDiff function.


Dash is a punctuation and special character.

[Barryg]

Thank you Jun7,

OK,
I do not think I'm explaining this in the right way.
Let’s assume that I want to calculate all BRF/BOF that were completed in say February of 2014. If I were to do this manually it would look like this:

There are 13 BRF_BOF_complete (for February 2014 See below)

Here the total number of days to complete (summed) is 184.

To get my answer, I would take 184/13 = 14.15384615 or the average number of days to complete BRF_BOF’s based on the NPIShipDate for the Month of February

How do I get a qurey to provide me with that Avg of 14.153846?

Number of Days from BRF Recieved to BRF Complete
DateBRFRcvd	BRF_BOF_Complete	NPIShipDate	#Days to complete
2/27/2014	2/27/2014	2/28/2014	0
2/27/2014	2/28/2014	2/28/2014	1
1/29/2014	2/6/2014	2/19/2014	8
1/30/2014	2/4/2014	2/6/2014	5
2/6/2014	2/7/2014	2/12/2014	1
2/7/2014	2/12/2014	2/21/2014	5
2/11/2014	3/5/2014	2/12/2014	22
1/10/2014	2/4/2014	2/14/2014	25
1/10/2014	1/29/2014	2/20/2014	19
1/6/2014	2/10/2014	2/27/2014	35
1/6/2014	2/13/2014	2/26/2014	38
1/7/2014	1/27/2014	2/7/2014	20
1/17/2014	1/22/2014	2/14/2014	5
	13 completed		184 (sum)

[June7]

Ooops, I should have used Avg() instead of Sum() - I corrected earlier post. If the NPIShipDate is the field that should be used to extract month for aggregating, consider:

SELECT Month(NPIShipDate) AS AvgMon, Avg([BRF/BOF_Complete] - [DateBRFRcvd]) AS AvgDays FROM tablename GROUP BY Month(NPIShipDate);

[Barryg]

Ooops, I should have used Avg() instead of Sum() - I corrected earlier post. If the NPIShipDate is the field that should be used to extract month for aggregating, consider:

SELECT Month(NPIShipDate) AS AvgMon, Avg([BRF/BOF_Complete] - [DateBRFRcvd]) AS AvgDays FROM tablename GROUP BY Month(NPIShipDate);

This is great,

Is there a way that I can round the AVGDays to two significant digits. (See below) Also the Month that is blank, is that due to Null values in the (NPIShipDate) field?

Month	AvgDays
	9.42857142857143
1	14.4285714285714
2	14.1538461538462
3	12.4166666666667

Month	AvgDays
	9.43
1	14.43
2	14.15
3	12.42

[June7]

Why is rounding in the query important? Could build a report with the query and round the values with textbox formatting.

Yes as to the Null issue.

[Barryg]

Why is rounding in the query important? Could build a report with the query and round the values with textbox formatting.

Yes as to the Null issue.

Not Important, just saw it an though I would ask. Thanks.


I am trying to use the same premise on another query and bascally just changing the fields and adding Not Null to the query. Its not working out for me.
I have tried adjusting several things but to no avail.


Query Name: Avg#Days from NPI Ready to NPI Build



SELECT Month(NPIShipDate) AS [Month], Avg([ActualNPIStartDate]-[BRF_BOF_Complete]) AS AvgDays



FROM tblNPI



GROUP BY Month(NPIShipDate)


WHERE (((tblNPI.BRF_BOF_Complete) Is Not Null)



HAVING (((Month(tblNPI.NPIShipDate)) Is Not Null));


When I run this it get this: Is this missing punctuation or something else?

[June7]

Try:

WHERE BRF_BOF_Complete Is Not Null AND NPIShipDate Is Not Null;


BTW, Month is a reserved word and should avoid reserved words as field names.

[Barryg]

Thanks June7 for all your help, I'm sure I will be back.

FYI, I changed the field names in the qurey to AS TheMonth. should be good right.

I tried the suggestion you made for WHERE BRF_BOF_Complete Is Not Null AND NPIShipDate Is Not Null;

No Joy, till get the Syntax Error just like above. I tried it just like you stated, then tried it with parenthesis and still got the syntax error both ways.

[June7]

These all worked for me:

SELECT Month(NPIShipDate) AS AvgMon, Avg([BRF_BOF_Complete]-[DateBRFRcvd]) AS AvgDays
FROM tblNPI
WHERE ((Not (tblNPI.BRF_BOF_Complete) Is Null) AND (Not (tblNPI.NPIShipDate) Is Null))
GROUP BY Month(NPIShipDate);

SELECT Month(NPIShipDate) AS AvgMon, Avg([BRF_BOF_Complete]-[DateBRFRcvd]) AS AvgDays
FROM tblNPI
WHERE (((tblNPI.BRF_BOF_Complete) Is Not Null) AND ((tblNPI.NPIShipDate) Is Not Null))
GROUP BY Month(NPIShipDate);

SELECT Month(NPIShipDate) AS AvgMon, Avg([BRF_BOF_Complete]-[DateBRFRcvd]) AS AvgDays
FROM tblNPI
WHERE ((Not (tblNPI.BRF_BOF_Complete) Is Null) AND ((tblNPI.NPIShipDate) Is Not Null))
GROUP BY Month(NPIShipDate);

[Barryg]

Man, You are awesome,

I for the life of me cannot figure why I could not get it to work. Your middle example looks like one of the versions that I tried and could not get to work.

In any case, I thank you for your patience and look forward to working with you again down the not too distant road I'm guessing.


I consider this thread done.

a million thanks to you June7