Suppose i have table which has field like memberno(num),teaexp(num),lunchexp(num),dinerexp(num).I like to have sum of these expenses for date between 1 jan 2004 to 31 march 2004 for specific member.How to write query for this.

Ganesh :)

: Suppose i have table which has field like memberno(num),teaexp(num),lunchexp(num),dinerexp(num).I like to have sum of these expenses for date between 1 jan 2004 to 31 march 2004 for specific member.How to write query for this.
:
: Ganesh :)
:

Clearly, you cannot get the sum of the expenses for a specific date range when you don't have a field containing the date for the expense. If you do have a such fild, let's say it's named dateofexpense and the table's name is foodexp, the query would be:

SELECT memberno, Sum(teaexp) As Sumteaexp, Sum(lunchexp) As Sumlunchexp, Sum(dinerexp) As Sumdinerexp
FROM foodexp
WHERE dateofexpense >= '04-01-01' And dateofexpense <= '04-03-31'
GROUP BY memberno;

Hope this helps.

//gizzu

This message was edited by ganesh4u at 2004-5-31 0:13:2

---

Thanks for reply.
Basicaly i am developing program which has vb6 as frontend ,Access as backend and crystal report as reporting tool.
I tried to utilise your query from vb6 crystal report control's groupselectionformula property but it doesn't work.
if your familier with concept of generating report in crystal report through vb6 then guide me to generate report from same query.
or
As i am new to site i don't know crystal report messageboard if you know that Please guide me.

Thanks once again.


Ganesh :)

: This message was edited by ganesh4u at 2004-5-31 0:13:2

---

: Thanks for reply.
: Basicaly i am developing program which has vb6 as frontend ,Access as backend and crystal report as reporting tool.
: I tried to utilise your query from vb6 crystal report control's groupselectionformula property but it doesn't work.
: if your familier with concept of generating report in crystal report through vb6 then guide me to generate report from same query.
: or
: As i am new to site i don't know crystal report messageboard if you know that Please guide me.
:
: Thanks once again.
:
:
: Ganesh :)
:
:
:

I am not familiar with crystal report at all, but I assume that there should be no difference in the query if crystal report is actually connecting to the access database. I must confess, however, that I didn't test the query before replying to your question. Try removing the line beginning with the WHERE word, and you'll have a query summing the total expense of all times instead of just three months. If it work then, there must be something wrong with that line. Maybe there should be '2004- instead of just '04- or something. If not, I'm afraid I cannot help you since I haven't ever heard of crystal before. I hope you can work this out anyway.

//gizzu

Hi gizzu,


Your suggession does not solve my problem.Any ways Thanks.
Ganesh :)