need help with ???

[aznabeel]

Hi,

can any body guide me...what is wrong with this query...i am trying to get average balance for specific date...But the query is not returning any result.
urgent reply will be appreciated.......

"SELECT [customer profit].[Customer Number], AVG ([customer profit].[VAL_BAL]) AS average, [customer profit].[Balance Date]
FROM [customer profit]


WHERE ( [customer profit].[Balance Date] BETWEEN 2/1/2014 AND 2/28/2014)
GROUP BY [customer profit].[customer Number], [customer profit].[VAL_BAL], [customer profit].[Balance Date];

"

[orange]

Can you post copy of your database? Or give us some test data and your table design?

[aznabeel]

tablle: customer prfot Customer Number	Balance Date	VAL_BAL	CCY
40001	2/1/2014	1724.68	PKR
40001	2/2/2014	1724.68	PKR
40001	2/3/2014	401724.68	PKR
40001	2/4/2014	446724.68	PKR
40001	2/5/2014	446724.68	PKR
40001	2/6/2014	21724.68	PKR
40001	2/7/2014	1724.68	PKR
40001	2/8/2014	1724.68	PKR
40001	2/9/2014	1724.68	PKR
40001	2/10/2014	1724.68	PKR
40001	2/11/2014	1724.68	PKR
40001	2/12/2014	1724.68	PKR
40001	2/13/2014	1724.68	PKR
40001	2/14/2014	1724.68	PKR
40001	2/15/2014	1724.68	PKR
40001	2/16/2014	1724.68	PKR
40001	2/17/2014	1724.68	PKR
40001	2/18/2014	201724.68	PKR
40001	2/19/2014	186724.68	PKR
40001	2/20/2014	186724.68	PKR
40001	2/21/2014	86424.68	PKR
40001	2/22/2014	86424.68	PKR
40001	2/23/2014	86424.68	PKR
40001	2/24/2014	86424.68	PKR
40001	2/25/2014	86424.68	PKR
40001	2/26/2014	86424.68	PKR
40001	2/27/2014	6424.68	PKR
40001	2/28/2014	6424.68	PKR

[orange]

I created table CustProf based on your data.
I reformatted your dates to be in my regional format.

You need to enclosed dates in #.

SELECT [Customer Number], AVG ([VAL_BAL]) AS average,[Balance Date]
FROM [custprof]
GROUP BY [customer Number], VAL_BAL,[Balance Date]
HAVING [Balance Date] BETWEEN #1/2/2014# AND #28/2/2014#

Result

      
Customer Number
average
Balance Date

40001
1724.68
02/01/2014

40001
1724.68
02/02/2014

40001
1724.68
13/02/2014

40001
1724.68
14/02/2014

40001
1724.68
15/02/2014

40001
1724.68
16/02/2014

40001
1724.68
17/02/2014

40001
6424.68
27/02/2014

40001
6424.68
28/02/2014

40001
86424.68
21/02/2014

40001
86424.68
22/02/2014

40001
86424.68
23/02/2014

40001
86424.68
24/02/2014

40001
86424.68
25/02/2014

40001
86424.68
26/02/2014

40001
186724.68
19/02/2014

40001
186724.68
20/02/2014

40001
201724.68
18/02/2014

[aznabeel]

thanks for the help..................

[aznabeel]

on the above data i am trying to get average monthly BALANCE, based on the following query ....but i only getting all recods with actual balace and not month wise average balance of the cusotmers......

can any body guide me...........................

-----------------------------------
SELECT [customer profit].[Customer Number], AVG ([customer profit].[VAL_BAL]) AS average, Month ([customer profit].[Balance Date])
FROM [customer profit]


GROUP BY [customer profit].[customer Number], [customer profit].[VAL_BAL], [customer profit].[Balance Date];
--------------------------------------
Data

Customer Number	Balance Date	VAL_BAL	CCY
40001	2/1/2014	1724.68	PKR
10001	2/2/2014	1724.68	PKR
40001	2/3/2014	401724.68	PKR
30001	2/4/2014	446724.68	PKR
40001	2/5/2014	446724.68	PKR
40001	2/6/2014	21724.68	PKR
10001	2/7/2014	1724.68	PKR
40001	2/8/2014	1724.68	PKR
20001	2/9/2014	1724.68	PKR
40001	2/10/2014	1724.68	PKR
20001	2/11/2014	1724.68	PKR
40001	2/12/2014	1724.68	PKR
30001	2/13/2014	1724.68	PKR
40001	2/14/2014	1724.68	PKR
40001	2/15/2014	1724.68	PKR
30001	2/16/2014	1724.68	PKR
40001	2/17/2014	1724.68	PKR
40001	2/18/2014	201724.68	PKR
10001	2/19/2014	186724.68	PKR
40001	2/20/2014	186724.68	PKR
30001	2/21/2014	86424.68	PKR
40001	2/22/2014	86424.68	PKR
20001	2/23/2014	86424.68	PKR
40001	2/24/2014	86424.68	PKR
40001	2/25/2014	86424.68	PKR
10001	2/26/2014	86424.68	PKR
40001	2/27/2014	6424.68	PKR
20001	2/28/2014	6424.68	PKR