find average between two dates with query

A

abhay_547

Registered User.
Local time
Today, 05:06
Joined
Dec 4, 2010
Messages
32
Hi All,
I want to come up with a query in which will find the average between two dates. Below is the data sample which I have :
L1 L2 Retx Prd As of Date Amt
ABC XYZ AFX XXF 11/29/2010 112123
IFA SDF BBS STR 11/28/2010 112841
SFS FDS KBS SDF 11/27/2010 154422
SLS SDR KSS SLF 11/26/2010 164222
RES QSS REA ASE 11/25/2010 174212
Now I have the above data in one of my access database table. I want to come up with an average of Amt between two dates .i.e. As of Date 11/25/2010 to 11/29/2010 and I want the query in such a way that it will ask for the user input for entering the dates. .i.e. From Date to To Date.
i.e. a input box should reflect on the screen when the user will run the query.
Thanks a lot for your help in advance.:)
 
Hi,

I have checked both the links and I have tried to do whatever was mentioned in the same but still doesn't work. Can you please have look into my file. Attached is my file for your reference.

I selected avg in the dropdown as suggested by you and entered the below line in the criteria Between [startdate] And [enddate] but still it's not working. Query name is Query1 in the attached file.

Thanks a lot for your help in advance.:)
 

Attachments

Keep at it and you will get it. If I need to see your db I will tell you ;)

A parameter must not be a field name. It could be a question just like you see in the link. But enclosed in square brackets like you've done. Try it out again.
 
I think not, there is so much wrong.

Your current query

Code: 
SELECT mydata.L1, mydata.L2, mydata.Retx, mydata.Prd, mydata.[As of Date], Avg(mydata.Amt) AS AvgOfAmt
FROM mydata
GROUP BY mydata.L1, mydata.L2, mydata.Retx, mydata.Prd, mydata.[As of Date]
HAVING (((Avg(mydata.Amt)) Between [StartDate] And [EndDate]));

1 All of the rows in the colimns have different data so no Grouping will occur
thus the average will be the amount
2 The criteria is for the date but is in the amnt col
3 The Criteria Col should be a Where selection from the dropdown

I think that you need to try again

Brian
 
Ooo... lots of problems then. I didn't look at the db.
 
Hi Brian,

Thanks a lot for you reply, I have tried below but still it's not working.

Code: 
SELECT mydata.L1, mydata.L2, mydata.Retx, mydata.Prd, mydata.[As of Date], Avg(mydata.Amt) AS AvgOfAmt
FROM mydata
GROUP BY mydata.L1, mydata.L2, mydata.Retx, mydata.Prd, mydata.[As of Date]
HAVING (((mydata.[As of Date]) Between [StartDate] And [EndDate]));

One more thing I want to add two amts and then take the average of the same for e.g. I have 10 line items , 5 lines items of one L1 category and five line items of another L1 category. I want to add amts of this two line items on the basis of date. for e.g. two line its of different category but same date will be added then the average will be taken for those 5 line items. Can we do this .i.e. first adding the amounts and then taking the average of it.

Thanks a lot for your help in advance.:)
 
Last edited:
Try This

Code: 
SELECT mydata.L1, mydata.L2, mydata.Retx, mydata.Prd, Avg(mydata.Amt) AS AvgOfAmt
FROM mydata
WHERE (((mydata.[As of Date]) Between [StartDate] And [EndDate]))
GROUP BY mydata.L1, mydata.L2, mydata.Retx, mydata.Prd;
 
abhay_547 said:
Hi Brian,

Thanks a lot for you reply, I have tried below but still it's not working.

Code: 
SELECT mydata.L1, mydata.L2, mydata.Retx, mydata.Prd, mydata.[As of Date], Avg(mydata.Amt) AS AvgOfAmt
FROM mydata
GROUP BY mydata.L1, mydata.L2, mydata.Retx, mydata.Prd, mydata.[As of Date]
HAVING (((mydata.[As of Date]) Between [StartDate] And [EndDate]));

One more thing I want to add two amts and then take the average of the same for e.g. I have 10 line items , 5 lines items of one L1 category and five line items of another L1 category. I want to add amts of this two line items on the basis of date. for e.g. two line its of different category but same date will be added then the average will be taken for those 5 line items. Can we do this .i.e. first adding the amounts and then taking the average of it.

Thanks a lot for your help in advance.:)
Click to expand...

I'm not surprised as you seem to have ignored everything I said, and now you have changed the problem, which definitely does not tie in with the data in the DB posted.

Brian
 
Hi DCrake,


Thanks a lot for your reply, I have incorporated the code it seems to working fine now but can you please help me with the below as well

One more thing I want to add two amts and then take the average of the same for e.g. I have 10 line items , 5 lines items of one L1 category and five line items of another L1 category. I want to add amts of this two line items on the basis of date. for e.g. two line its of different category but same date will be added then the average will be taken for those 5 line items. Can we do this .i.e. first adding the amounts and then taking the average of it.
Click to expand...

Thanks a lot for your help in advance.:)
 
How about this

He wants to sum amts/2 grouped by date where L1<>L1

ie insert table into the query twice, join on datefield,
(table_1.amnt+table_2.amnt)/2 where table_1.L1<>table_2.L1


Brian
 
It can probably be done in a report unless like you said, inserting into a temp table.
 
Hi All,

The query which was posted by DCrake is not working as per my requirement. I think there is misunderstood my issue. So I am attaching a excel file with the calculation which I need to do in the access query. I would request you guys to look into the attached excel file in order to understand my requirement. Please..


Thanks a lot for your help in advance.:)
 

Attachments

Last edited:
Take a look at the revised workbook. Don't believe everything you see.
 

Attachments

Hi DCrake,

Thanks a lot for your reply, It seems to be working fine now but can you please also look into my other issue where I am adding two categories data first and then calculating the average from the total of the same. Please have a look into the attached workbook. Cell H7.

Below is what I want to do in the query. If you look into the cell H7 you will understand my requirement.

I want to add two amts and then take the average of the same for e.g. I have 10 line items , 5 lines items of one L1 category and five line items of another L1 category. I want to add amts of this two line items of two different L1 categories on the basis of date. for e.g. two line are of different category but same date will be added then the average will be taken for those 5 line items instead of 10 line items. Can we do this .i.e. first adding the amounts of two lines on the basis of date and then taking the average of it.
Click to expand...


Thanks a lot for your help in advance.:)
 

Attachments

Last edited:
Is 2 the maximum number of categories you want to average? and how are you deciding which 2 to group?
 
Hi DCrake,

Thanks a lot for your reply I will be adding two categories. .i.e. Amt of L1 "ABC" and "IFA" on the basis of date. For e.g. L1 Column has different categories from which we have "ABC" and "IFA" reflecting multiple times and that two for the dates between 11/25/2010 to 11/29/2010 for both "ABC" and "IFA". So I want to add the number of Amt of ABC and IFA on the basis of date for e.g. Amt of 11/25/2010 for ABC and IFA will be added and the same way for other dates as well and then after that we will find the average of ABC and IFA total amt. Just look into the column G7:G11 in the sheet1 and Cell H7 for the calculation done by me in the excel.


Thanks a lot for your help in advance.:)
 

Attachments

Last edited:
Hi DCrake,

Did you get the chance to look into the above post. ?

Thanks a lot for your help in advance.:)
 
Then why not simply create a new query just the same as the current one and add a where condition to it

Where [LI] In("ABC","DEF")
 
You must log in or register to reply here.

Similar threads

L
I want to identify the data in an interval of time between two days
Replies
4
Views
1,256
sneuberg
S
X
Date difference between rows in same column
Replies
6
Views
1,302
xthai
X
K
How to calculate the number of weeks between two dates?
Replies
5
Views
7,967
KaylaSimmonds
K
W
Calculating "X Years, X Months and X Days" between two dates
Replies
3
Views
7,857
willknapp
W
I
Calculating Average Number of Days Between Two Dates
Replies
2
Views
2,400
InstructorGirl
I

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom