Solved Reduce outcomes base on sub query (1 Viewer)

artefact

New member
Local time
Today, 09:14
Joined
Feb 9, 2016
Messages
12
I'm struggling to get an ms access query work in order to have the average of value based on 3 months if they do exist in the rows and no necessary consecutives.
I'm not able to calculate the way to test if for a specific month (included), I have 3 month in the rows, if so, limit the calculation only those three month, not less not more.

  • Here the query, I think I'm very close to the outcome I would like:

SELECT t1.DateDue, t1.[SupplierCode], iif(count(*)>3,avg(t2.ratioontime),0) AS avgratio
FROM otifdatatoexport AS t1
LEFT JOIN otifdatatoexport AS t2 ON (t1.DateDue>= t2.DateDue) AND (t1.[SupplierCode] = t2.[SupplierCode])
GROUP BY t1.DateDue, t1.[SupplierCode]
ORDER BY t1.[SupplierCode], t1.DateDue;


  • based on the following table and the logic with outcome I would like :

Logic description.JPG



Any help will be greatly appreciated!

Arte
 

Attachments

  • OTIF - Copy.zip
    89.1 KB · Views: 357
Last edited:

vba_php

Forum Troll
Local time
Today, 11:14
Joined
Oct 6, 2019
Messages
2,884
since noone has responded, got a sample file you can upload? maybe someone else has an answer, but personally, based on your words, I would have to see a sample to help.
 

artefact

New member
Local time
Today, 09:14
Joined
Feb 9, 2016
Messages
12
since noone has responded, got a sample file you can upload? maybe someone else has an answer, but personally, based on your words, I would have to see a sample to help.
since noone has responded, got a sample file you can upload? maybe someone else has an answer, but personally, based on your words, I would have to see a sample to help.
As requested, I attached a sample. Thanks for your help!!!
 

vba_php

Forum Troll
Local time
Today, 11:14
Joined
Oct 6, 2019
Messages
2,884
i did look at your sample, but based on your description, I'm not following what you want to do. actually, what I was expecting from you was an actual ACCESS file with the query you have already written.

furthermore, your query has a table in it that is NOT in your excel file:
Code:
from
otifdatatoexport t1 left join otifdatatoexport t2
based on your words, it is very difficult to try and find out what you want. are you looking for the AVERAGE RATIO based on a RANDOM 3 month period? or just the first 3 month period for each supplier code?
 

plog

Banishment Pending
Local time
Today, 11:14
Joined
May 11, 2011
Messages
11,611
I cannot reconcile your words with your data either. First, this site has lots of UK and USA members and we use the same date format to mean different things. "01/02/2017" means January 2nd 2017 in the US and February 1st in the UK. Which do you mean?

Second, you need to use diverse ratioOnTime values to demonstrate exactly what you want. Every value but one record has 1 as its value, when you do that we can't work backwards to determine which 3 records go into an Average.

3rd, you have some poor names. "Date" is a reserved word and should never be used for an object or field in Access. Nor should you ever use spaces in field names.

So, I am going to put forward starting data and we need you to tell us what data you expect in the results:


YourTableName
SupplierCode, DateDue, ratioOnTime
317, 11/1/2016, .7
317, 12/1/2016, .5
317, 1/1/2017, .8
317, 2/1/2017, .9
317, 3/1/2017, .6
317, 5/1/2017, .2
444, 4/1/2017, .6
444, 3/1/2017, .5


Using the above data, please show us what data you expect in the results.
 

artefact

New member
Local time
Today, 09:14
Joined
Feb 9, 2016
Messages
12
since noone has responded, got a sample file you can upload? maybe someone else has an answer, but personally, based on your words, I would have to see a sample to help.
i did look at your sample, but based on your description, I'm not following what you want to do. actually, what I was expecting from you was an actual ACCESS file with the query you have already written.

furthermore, your query has a table in it that is NOT in your excel file:
Code:
from
otifdatatoexport t1 left join otifdatatoexport t2
based on your words, it is very difficult to try and find out what you want. are you looking for the AVERAGE RATIO based on a RANDOM 3 month period? or just the first 3 month period for each supplier code?

I Have attached the access database, with the query and the table as requested. What I would like to calculate for each couple supplier code + months, the average on 3 months regardless if they have been consecutive.
 

vba_php

Forum Troll
Local time
Today, 11:14
Joined
Oct 6, 2019
Messages
2,884
I don't think you can do it without writing code. but don't take my word for it. I'm a code writer, not a query writer. you are using a LEFT JOIN in your query, and I don't think that's necessary at all. what you want is a grouping on a 3-month period, and for EVERY 3 month period for EVERY supplier code, right?

the purpose of a GROUP BY clause is to group data by a field value, and you want to group by a GROUP of field values. thus, I would think that the only solution would be stacked queries or code. I could certainly write code to do this, but if I were you I would wait to see if PLOG or someone else has an easier solution for you. code is always a last resort. most of the time there is an easier way to do things. I will watch the thread to see if the other guys can come up with something easier for you....
 

artefact

New member
Local time
Today, 09:14
Joined
Feb 9, 2016
Messages
12
I cannot reconcile your words with your data either. First, this site has lots of UK and USA members and we use the same date format to mean different things. "01/02/2017" means January 2nd 2017 in the US and February 1st in the UK. Which do you mean?

Second, you need to use diverse ratioOnTime values to demonstrate exactly what you want. Every value but one record has 1 as its value, when you do that we can't work backwards to determine which 3 records go into an Average.

3rd, you have some poor names. "Date" is a reserved word and should never be used for an object or field in Access. Nor should you ever use spaces in field names.

So, I am going to put forward starting data and we need you to tell us what data you expect in the results:


YourTableName
SupplierCode, DateDue, ratioOnTime
317, 11/1/2016, .7
317, 12/1/2016, .5
317, 1/1/2017, .8
317, 2/1/2017, .9
317, 3/1/2017, .6
317, 5/1/2017, .2
444, 4/1/2017, .6
444, 3/1/2017, .5


Using the above data, please show us what data you expect in the results.
Hello Plog, as requested I've updated the table based on your request. Thanks!
 

plog

Banishment Pending
Local time
Today, 11:14
Joined
May 11, 2011
Messages
11,611
Hello Plog, as requested I've updated the table based on your request

Huh? I didn't request any table update. I requested you give me results based on the data I provided. Please do that.
 

artefact

New member
Local time
Today, 09:14
Joined
Feb 9, 2016
Messages
12
Huh? I didn't request any table update. I requested you give me results based on the data I provided. Please do that.
Based on the data your provided

SupplierCode, DateDue, ratioOnTime
317, 11/1/2016, .7
317, 12/1/2016, .5
317, 1/1/2017, .8
317, 2/1/2017, .9
317, 3/1/2017, .6
317, 5/1/2017, .2
444, 4/1/2017, .6
444, 3/1/2017, .5

here is the result:

SupplierCode, DateDue, avg(ratioOnTime )
317,1/1/2017/,.67
317, 2/1/2017,.73
317, 3/1/2017, .76
317, 5/1/2017, .57

Thank you!
 

plog

Banishment Pending
Local time
Today, 11:14
Joined
May 11, 2011
Messages
11,611
Ok, you can do this with just 1 sub query:

Code:
SELECT otifdatatoexport.SupplierCode, otifdatatoexport.DateDue, otifdatatoexport.ratioOnTime, Max(otifdatatoexport_1.DateDue) AS M1, Max(otifdatatoexport_2.DateDue) AS M2
FROM (otifdatatoexport INNER JOIN otifdatatoexport AS otifdatatoexport_1 ON otifdatatoexport.SupplierCode = otifdatatoexport_1.SupplierCode) INNER JOIN otifdatatoexport AS otifdatatoexport_2 ON otifdatatoexport_1.SupplierCode = otifdatatoexport_2.SupplierCode
WHERE (((otifdatatoexport_1.DateDue)<[otifdatatoexport].[DateDue]) AND ((otifdatatoexport_2.DateDue)<[otifdatatoexport_1].[DateDue]))
GROUP BY otifdatatoexport.SupplierCode, otifdatatoexport.DateDue, otifdatatoexport.ratioOnTime;

Paste that into a new query and name it 'sub1'. It finds the prior records for each record that you will compute the average for. Then to get the results you want use this query:

Code:
SELECT sub1.SupplierCode, sub1.DateDue, ([sub1].[ratioOnTime]+[otifdatatoexport].[ratioOnTime]+[otifdatatoexport_1].[ratioOnTime])/3 AS [AVG]
FROM (sub1 INNER JOIN otifdatatoexport ON (sub1.SupplierCode = otifdatatoexport.SupplierCode) AND (sub1.M1 = otifdatatoexport.DateDue)) INNER JOIN otifdatatoexport AS otifdatatoexport_1 ON (sub1.M2 = otifdatatoexport_1.DateDue) AND (sub1.SupplierCode = otifdatatoexport_1.SupplierCode);

If it doesn't work for all cases, please provide an example of where it doesn't work.
 

artefact

New member
Local time
Today, 09:14
Joined
Feb 9, 2016
Messages
12
Ok, you can do this with just 1 sub query:

Code:
SELECT otifdatatoexport.SupplierCode, otifdatatoexport.DateDue, otifdatatoexport.ratioOnTime, Max(otifdatatoexport_1.DateDue) AS M1, Max(otifdatatoexport_2.DateDue) AS M2
FROM (otifdatatoexport INNER JOIN otifdatatoexport AS otifdatatoexport_1 ON otifdatatoexport.SupplierCode = otifdatatoexport_1.SupplierCode) INNER JOIN otifdatatoexport AS otifdatatoexport_2 ON otifdatatoexport_1.SupplierCode = otifdatatoexport_2.SupplierCode
WHERE (((otifdatatoexport_1.DateDue)<[otifdatatoexport].[DateDue]) AND ((otifdatatoexport_2.DateDue)<[otifdatatoexport_1].[DateDue]))
GROUP BY otifdatatoexport.SupplierCode, otifdatatoexport.DateDue, otifdatatoexport.ratioOnTime;

Paste that into a new query and name it 'sub1'. It finds the prior records for each record that you will compute the average for. Then to get the results you want use this query:

Code:
SELECT sub1.SupplierCode, sub1.DateDue, ([sub1].[ratioOnTime]+[otifdatatoexport].[ratioOnTime]+[otifdatatoexport_1].[ratioOnTime])/3 AS [AVG]
FROM (sub1 INNER JOIN otifdatatoexport ON (sub1.SupplierCode = otifdatatoexport.SupplierCode) AND (sub1.M1 = otifdatatoexport.DateDue)) INNER JOIN otifdatatoexport AS otifdatatoexport_1 ON (sub1.M2 = otifdatatoexport_1.DateDue) AND (sub1.SupplierCode = otifdatatoexport_1.SupplierCode);

If it doesn't work for all cases, please provide an example of where it doesn't work.

Hello Plog, I'd really to thank you. I was searching for that for 5 days. You just did it very fast!!!! I was told it was not possible in access. I was advisedto use sql server...
 

plog

Banishment Pending
Local time
Today, 11:14
Joined
May 11, 2011
Messages
11,611
Glad to help and glad it worked the first time.

I was advisedto use sql server...

Ha ha, yeah and if you talked to an Oracle guy he would have said you need Oracle, a C# lady would tell you C#, a python guy...
 

vba_php

Forum Troll
Local time
Today, 11:14
Joined
Oct 6, 2019
Messages
2,884
Ha ha, yeah and if you talked to an Oracle guy he would have said you need Oracle, a C# lady would tell you C#, a python guy...
you can do this with ANYTHING, plog. come on, ur smart enuf to realize that.
 

Users who are viewing this thread

Top Bottom