Report Conditional Sum

Hi

I am currently transferring a system used for discounts from Excel to Access and would like some advice. I believe that what I want to achieve will be possible in Access Reports but would appreciate any pointers.

What is very easy in Excel is a little more complicated in Access, but overall should be a much better system.

The system calls for an aggregation of sales over an extended period with current sales added at 100%, 1 quarter ago 75%, 2 quarters ago 50%, 3 quarters ago 40%, 4 quarters ago 30%, all others 25%.

I am using a field, 'sales period' which increases by 1 every quarter and would like to use this as a basis for calculating applicable sales. For example if current period is 15, total * 100%, current period - 1 * 75% etc.

I would appreciate it if you could advise if I am on the right track or if I need to rethink my approach.

Many thanks

Jeff


Post your answer or comment

comments powered by Disqus
I have been trying to figure out the following scenario. Hopefully someone can help.Access 2002

LEADS table has many fields. The ones in question are
ContractAmount Currency
RegionalManager Text
ApptDate Date
DFTDate Date


Report runs for range of [apptdate] Between [begindate] and [enddate]
Grouped weekly on RegionalManager
Displays various totals on other fields in the RegionalManager footer

Now I need a conditional sum of the constructionamount where dftdate is null
This is also in the regionalmanager footer

I'm currently trying to design a report that sums all the power flows in, say, a power plant. I'm having difficulty deciding how to set it out.

Firstly I'll try to describe what I hope to have in the report:

In a power plant layout, the different levels a connected in a way that's similar to a tree diagram. At the very ends of the tree diagram are the loads. These demand a certain amount of power. I would like to add up the powers at each node of the tree diagram, and be able to show these in the report. I was wanting to make it so that they are displayed on the report in a similar way to the actual layout, (that is, show the top level, then second, third etc).

For example, if I have a plant layout as shown in layout1.bmp attached, I would like the report to display something like the following:

TOP FEED LOWER FEEDS POWER
------------------------------------------------------------------

Switchboad 1: Load 1 5kW
Switchboard 2 8kW
Substation 13kW

TOTAL POWER: 26kW

------------------------------------------------------------------

Switchboad 2: Load 2 2kW
Load 3 4kW
Load 4 2kW

TOTAL POWER: 8kW

------------------------------------------------------------------

Substation: Switchboard 2 5kW
Load 5 3kW
Load 6 5kW

TOTAL POWER: 13kW

I wish it to be ordered from top feed to lowest as shown.

I have a table that has the following info:

Number, Name, Above Feed Name, Above Feed Number, Power

My logic is that as long as each load has an "Above Feed Name" then it is possible to figure out the layout.

Does anyone know how I can use this method to make my report do what I intend? Thanks to anyone who helps.

Hi,

I placed 2 reports in a main report and generating data. All of them are based on simple queries with date criteria. It works fine.

Example: Let us assume the main reports sum is US$ 149.42. !st sub report's sum is US$ 29.12 and 2nd sub report's sum is US$ 3.12.

At the main report's report footer (in a text box) appears 181.66

The only problem I am facing is when I need to display NZ sum of 1st sub report on Main report as well as the second sub report's sum when there is NO DATA.

I tried using nz function but it always produces 'Error'. If works well when all the 3 sub total text box contains data. But produces Error when there is no data. I need it to produce 0 upon no data.

Any idea?

With kind regards,
Ashfaque

Hi All!
I did a report running sum from my checking account. Works fine on Ascendant, but I want it to work on descendant. The table is date/notes/Amount. On the report I have date/notes/amount/balance. What can I do to reverse it to make the new date to be on top of the page and make the balance as well, if that is possible?
thanks
Jr.

Hi all

I want to display income and expense seperately on the form till the time of last transaction--in cash book,where i will enter transactions.

Issue:

How do i display total of income and expense seperately on form using conditional sum on date,income/expense , or any other way to do it

Can someone please remind how to conditionally sum one value in a record based on the value of another.

E.G. I have a subform with a Paid? Yes/No checkbox and a field holding the order line value.

What I want to do is have an unbound box showing how much is outstanding on the account.

I know the control source should be somthing like:

=Sum(if([Paid]=false,[OrderLineValue],0))

All I keep getting back is #Name error.
I can do the same task using a sum query but I know I dont need to.

Any takers...

Ian

Dear Access Friend.

I need some advices and would be happy for any tip you may have to help me out.

Problem Report Conditional Formating , I looking for help on "advanced" not the "basic" formating options

Example - Description of the situation

Imagine a table with 2 columns

Column A = CAR BRAND
Column B = COUNTRY

The result report is grouped per Column A "CAR BRAND"


CAR BRAND
bmwCOUNTRY
Germany
Germany
Italy
Germany
France
GermanyCAR BRAND
ferrariCOUNTRY
Italy
Italy
Italy
HongKong
Italy
Italy
So, what I am looking for is that the "COUNTRY" falling out of the "normal" or "most used country names", is highlighted in Red and this on Group level, not on the complete report level.

Is their a possiblily to do such thing?


Thanks in advance for any answer.

Jerry

I'm trying to write a conditional expression to sum specific data in my report.

I've placed a text box in the report footer.

This is what I'm typing in the control source box:

=IIf([MW_Status]="6",Sum([MW_SQ_FT]))

The end result I'm looking for: Sum the Sq. Ft. of all records where Status = 6.

Any help would be appreciated.

P.S. Thanks also for all the other valuable threads that I use on a weekly basis.

Little problem here.... Trying to do some summing in a report, but can't find a method that works. This is a report of Sales Grouped by Salesman. The type of sale is one of two or three descriptions "Monthly", "One Time", so on...
So I have an unbound text box in my header "Monthly Sales" Now what I am trying to do is sum ONLY the "ContractPrice" fields that have description of "Monthly" And I would like to do this on the report if possible.... I have tried a IIF statement ... =IIf([TypeDescription]="Monthly",Sum([ContractPrice]),0)...
But the problem here is if ANY of the descriptions are "Monthly" it sums ALL "ContractPrice" fields.

Any thoughts????

Thanks

I am using a simple query for a report
The query produces a value for each line by multiplying a quantity by a cost. Total Value: [cost] * [quantity]

I want to also obtain the sum of these Total Value(s) between set figures to use in the report. For example:
Sum of Total Values between 0 and 10.00
Sum of Total values greater than 10.00 and less than 100.00
Sum of Total Values greater than 100.0 and less than 1000.00
etc.

I have tied a few expressions in the query but I don't think I am using the correct syntax.
Any suggestions?

Cheers
Peter

In one report I summed up multiple DETAILS to come up with a total cost of the product in the footer. I needed the total to show up on other reports so I made a copy of the report that calculates the total and shrunk everything except the total cost. Next I drug a copy of that report and used it in the details section of the other reports that required it. Works good, but I don't think that I can find the sum of a bunch of reports in a report. Is there a better way to use a sum in one report as a detail in another. This is driving me nuts!!!!!!!!!!

okay I have a running sum to get a total each month at the end of the report it displays the correct total so thats fine , the rpoblem is I'd like the last running sum total on top of the form in the header

what I've done so far is created another set of text boxes in the header and tried to use teh DMAX or MAX functions to get the last of the running sum totals but its not working right is there another way I can approach this?

thanks for the time,
Bob

I know you can't do this via conditional formatting and you have to use some code to do it in the format event....

BUT

I'm having problems with one at the moment (it's pretty complex)

Main report shows days that sales were made by someone
sub report linked via employee id shows days that were worked.

what I want to do is make the controls bold if the day if the same in the main report and subreport

I've tried creating a recordset and seeking through it to see if that day and that employee id are there, but it means that the report doesn't open anymore so I've removed it.

Can someone help ?

I have searched on Sum and DSum but cannot find anything that is relevant to my problem.
I have a report which is derived form a form/subform.
The main form has a unquie ID number amongst other fields, the subform has grade, volume and price fields among others.
A report is derived from a query that only shows those records where the ID matches in both main and subform, this part works fine.
My problem is that I want to sum the volumes but only if the grade is a certain value e.g. "P" in my report.
I have tried an unbound textbox in the report with the formula =Sum(IIf([Grade]="P",Forms!PropertyLogDocketMainForm!qryLogDocketS ubForm.Form!VOL,0))
but this gives me a figure that does not add up to anything in the subform volume fields.
My other problem is that I need to sum the prices of only the "P" grade items in another unbound textbox on the report, I have tried this formula - =Sum(IIf([Grade]="P",Forms!PropertyLogDocketMainForm!qryLogDocketS ubForm.Form![Pole Price],0))
but again I get some weird amount that doesn't resemble any of the data in the subform. I think that the problem with this field is that in some instances a row of data in the subform may not have a price and there are blanks in that column e.g.
Volume Grade Price
1.256 P 35.00
2.589 O
1.025 P 86.00
As you can see there are gaps in the column, does this affect things (I know it does in Excel, but unsure in Access)
Would DSum be better than Sum?
Any suggestions would be greatly appreciated.

Cheers
optidisk

I need to develop a report that sums commissions by office based on account number.

I've created a field that assigns a "DeptIDMarker" of either "A" or "B". Now what I can't figure out is how to Sum(Commission) where DeptIDMarker = A and then another field to Sum(Commission)where DeptIDMarker = B.

I hope someone can understand and help. I would really appreciate any suggestions.

Thank you.

Background:
Developing a DB to track purchase orders. Each order can contain one or more items.

Table Design/Relationship:
I have two tables 1) Order and 2) OrderDetails in a one to many relationship. The Order Table contains general order info and has a field "GrandTotal" which is the sum of all the item prices in the order. The OrderDetails Table has a record for each item in each order.

Objective/Problem:
I would like to have a report that joins the two tables and displays the current months orders and the contents of each order. The report is grouped by order number and displayed in the group footer is the GrandTotal field. The order contents (the items making up the order) is in the details section. In the report header i want to display the the total of all the orders. In a text box i placed =Sum([GrandTotal]) but the grandtotal of each item gets multiplied by the number of items in the order. For example, if there is one order with a GrandTotal of $2 and in that order there are two items, Sum([GrantTotal]) returns $4 instead of $2.

Does anyone know how I can display the sum of all the order's GrandTotals in the report header?

I am currently completing a rather convoluted report that is intended to visually illustrate the status for measurements of success. In the table (and associated form and query) that populates my report, there is a "Status" field from which users may select "Red", "Yellow", or "Green".

I have created 3 labels overlapping on the report screen ("R", "Y", and "G") with color corresponding to the status. My plan is to code the .Visible property based on the value in the "Status" field. However, I am failing tremendously to get any movement out of the .Visible property at all. For simplicity I have embedded a text box on the report that is not visible with the Status value. The code I have attempted to use is (using MS Access 2000 DB format):

Private Sub Report_Current()

If Reports![rptMeasure]![txtStatus] = "Green" Then
Reports![rptMeasure]![lblGreen].Visible = True
Else
Reports![rptMeasure]![lblGreen].Visible = False
End If

If Reports![rptMeasure]![txtStatus] = "Yellow" Then
Reports![rptMeasure]![lblYellow].Visible = True
Else
Reports![rptMeasure]![lblYellow].Visible = False
End If

If Reports![rptMeasure]![txtStatus] = "Red" Then
Reports![rptMeasure]![lblRed].Visible = True
Else
Reports![rptMeasure]![lblRed].Visible = False
End If

End Sub

Effectively all the boxes are remaining visible based on the default properties field rather than looking to the code. I am a neophyte at this and I'm sure I'm missing something terribly obvious. Also, I know the simpler solution may be to simply have the txtStatus control visible and conditionally format that, but it doesn't give the same effect that I am looking for here.

I would appreciate any input on the code and any explanation describing the "why" of that code so I might learn from this. Thank you.

On the report (file attached), I try to make a sum of the length field, i created a text box to sum the length but it shows as "Error". I also want to make sums of each group. How can I do it? and what was the problem? I'm new on this, thanks in advance.

Hello everyone,

I have a problem with adding a formula on a report in order to calulate the Total of a client's "charges" (field name).

Can someone assist me in this area: what formula or condition can I input into the design view of the report to Sum or Total the "charges" field of the report.

Thanks in advance

A....

Hey again. I have done a lot of searching, and can't seem to find an answer. I am summing data from my main table, one calculation per field each in its own box, then totalling those. Whenever a field has no data the box on the report is blank and the total box stops working and goes blank as well. The only thing I can find that looks like the answer is the "IIf" function, but I can't seem to find good info on using it. A sample would be a very simple: =sum([fuel]) How do I work the IIf function into that so I get a value of zero if I have no data in that field in my main table? Thanks in advance.

I am using crystal reports 2008 to access both an SQLServer and MSaccess database. The MSAccess is a mobile version of SQLServer database. In my report I have a conditional suppress of a text field that works fine when running against the SQLServer but gives me "Invalid argument provided" when I run it against MS access. the formula is pretty simple:

{FREQ.Type} = 'H' or {FREQ.Type} = 'R'

I am at a loss as to why this doesn't work. I have tried numerous iterations, adding parentheses, putting it in an if statement etc. but I can't make Access happy.

Any help is greatly appreciated.

Hi guys

I am designing a search from to search for a customer. based upon following input:

I have in total 5 textboxes.

1. Textbox for "Polno".
2. Textbox for "Firstname"
3. Textbox for "Surname".
4. Textbox for "All or part of Address/City"
5. Textbox for "Postcode"

Now, when the user just enter Polno and press Enter then it displays a report as follows:


	Code:
	If txtPolno  Null Or txtPolno  "" Then
 mysCriteria = "Polno=" & txtPolno & ""
doCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog      End If

But now when somebody input data in rest of the four textboxes then I have to join the conditions.e.g if a user enters Firstname and some part of Address then to run a report condition will be different.

How to do that part?

Any help would be much appreciated.

Thanks

Hi there

I have a table that has statistics in it by date. I have simplified below. The yearNum, MonthNum and WeekNum are just forumla driven off the date.

The report is a variance report "as at" the date the user enters: Report for week ending: 17/05/2009

The reports are variance reports for the "month to the date" entered and the "year to date" against previous months and years. And yes, I know a database would be better, but my client is an Excel nut who will not accept calculations he cannot see.

I have used sumif calcs based on the month number to arrive at weekly variance calcs, however, I am not sure how to use Sumif to sum the column for all dates for the month where they are equal or less than the date entered for a year to date calculation. I have tried embedding sumifs, but this doesn't seem to work.

Any assistance you could provide would be great and much appreciated.

Thanks

Rebecca

In Access2010, I have a crosstab report (monthly sum of hours by program) that runs off a button (OnClick event macro) on a main menu form. The data source is a query with the following SQL:

TRANSFORM Sum(Hours.Hours) AS SumOfHours
SELECT Hours.Program, Sum(Hours.Hours) AS [Total Hours]
FROM Hours
WHERE (((Year([VolDate]))=[What year?]))
GROUP BY Hours.Program
PIVOT Format([VolDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

All runs well, but I want the year entered by the user to print in the report.
How can I pass the WHERE condition value entered by the user (a year) to be printed in the report?


Not finding an answer? Try a Google search.