Sum by Won Project in by each month of the year

[011billyw]

Hello i have a report that with the fields:

Proposal Number Client Name Proposal Fee Status(won, lost, pending)

=Format$([Dated],"mmmm yyyy",0,0)

The report shows the Sum of the Fees

=Sum([Proposal_Fee])

here is where the problem begins
I want it to show the sum of the project won by each month, how ever i have no idea on how to make it work, since the coding i am using shows the sum of all the project in the form, of all years and month, here is the code

=DSum("[Proposal_Fee]","QryProposalInOrder","[Statu] Like 'Won*'")

is Statu because this fields shows the combination of the fields, the Status, and the Project Number of the Proposal


also at the end of the report i want the sum of the Yearly Proposals won(my guess is that having the monthly and yearly, the yearly would be the easiest to do

i need some help pls,



Thanks in advaned

[June7]

Create a month/year field in query with expression: Format$([Dated],"mm yyyy",0,0). Use this field to group and sort records in the report. Do Sum calc in textbox of group header/footer.

[011billyw]

i see,
but how will i sum the proposals won within that month(while showing all the proposals)

[June7]

Use report grouping and sorting. Set up a group on the month/year constructed field. Textbox in that group header/footer with expression in ControlSource: Sum([Proposal_Fee]).

Put fields for proposals info in the Detail section.

Does the table include records for proposals not won?

[011billyw]

ye sir, the table has all of them(Won Lost, Pending, and Submitted), as well as the report should have all of them.

i am using a query to set up the report, i thin that you are assumign that i only have won Proposals,however i need to have them all together, 1,- show the sum of all of them by month and year( Which is something i hve)
I Do not have the coding to sum only won Proposals by month and year.

this is the code that i am using in the TEXT BOx,

=DSum("[Proposal_Fee]","QryProposalInOrder","[Statu] Like 'Won*'")
The code above add ALL the porjcet in the report, not the won project by month

[June7]

I don't think you want or need to use DSum. DSum is a domain aggregate function (along with DAvg, DCount, etc) and is pulling data from the table, not the report's recordsource. Try this in textboxes in month/year group header or footer and form header or footer:

=Sum(IIf([Statu] Like "Won*", [Proposal_Fee],0))

[011billyw]

ey Thank you so much, and sorry for the late reply, i had was at school and doing hmwk all day )))

U saved my life!

[011billyw]

quick question thought:
i have a query field that combines the Status and The Project Number
Ex. Won 009-112

I want it to be like this
Won
009-112

I tried pressing CTR+ Enter (because that make a new page in Micrsft. Word)

I works fine until i close and open the access file, how can i make this to be permanently.is there an expression i can put in the field name

Statu: [Status] & "(here is when i press 'ctrl+Enter')" & [Project Number]

Thanks!!

[June7]

Try:
Statu: [Status] & Chr(13) & [Project Number]

[011billyw]

nop, it only makes a square between status and and Project Number

Thanks for the fast reply bro!

[June7]

Sorry, been a while since I used that.

Statu: [Status] & Chr(13) & Chr(10) & [Project Number]

[011billyw]

Thank you so much bro again
You saved me alot of time
thanks

And this Threat is SOLVED