query for daily as well as cumulaive for month (1 Viewer)

[Ramnik]

hello everyone,
i have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ).
now i want a daily production query as

date : xx-xx-xxxx
productname | qty | monthlysum(for thsi product) | daily average |

i want this on a single query so that i can make a report out of this .
this seems quite simple but i m stuck . any help would be much appreciated. thankssssssss

 

[Ramnik]

hello everyone,
i m sending this attachment to further elaborate my question ...
i need monthly sum from daily production table for the product in that row. thankssss

 

[JHB]

Hmmm - how do you want the monthlysum calculated? date : xx-xx-xxxx minus one month or?

 

[Ramnik]

No i want for the current month from the date we entered ( Month(Productiondate) )

 

[Brianwarnock]

I see that you are grouping on production date, so drag it into the grid a second time for the Where condition which will be
Between dateserial(year(productiondate),month(productiondate),0) And Dateserial(year(productiondate),month(productiondate)+1,0)

Brian

 

[Ramnik]

thanks for ur suggestion nd this is the modification i have done but still the passquantity shows exact values from that day only . i think there are more things i should do . dont know ......

 

[Brianwarnock]

It is impossible to do daily and monthly in one query, that is what reports are for.
To get it by month you would have to group on Month(productiondate)

What is the criteria under the productiondate in your original

Brian

 

[Ramnik]

nothing that is just a date picker box to specify the date .

 

[Ramnik]

moreover if i have separate queries as daily and monthly ... how i will create a report out of this as :
date : xx:xx:xxxx
productname | passqty | monthlysum
xxxxxxxxxx | xxxx | xxxx

thanksssss...

 

[Brianwarnock]

nothing that is just a date picker box to specify the date .

But it is not nothing, it is criteria that will affect the data selected.

Brian

 

[Brianwarnock]

moreover if i have separate queries as daily and monthly ... how i will create a report out of this as :
date : xx:xx:xxxx
productname | passqty | monthlysum
xxxxxxxxxx | xxxx | xxxx

thanksssss...

That looks like a monthly report with no daily details, unless that monthly sum is a daily running total for the month.

Brian

 

[Ramnik]

yes its a running total sum for the month from the date specified.

 

[Brianwarnock]

That is done in the Report, search the report forum for running sum

Brian