Reports :: Hourly Rate - Calculated Totals Not Adding Up

Sep 5, 2013

We recently have had a change to our hourly rate that we pay and now the Totals calculation is not adding up the numbers correctly.

In the query that the report is based from:

ExtendedTotal: CCur([Hours]*[Rate])

In the footer of the report: (Provides a Total for each Day)

Sub-Total: "Control Source" is: =Sum(CCur([ExtendedTotal]))

Report Footer: (Provides a Grand Total for the Month)
Total: "Control Source" is: =Sum([ExtendedTotal])

Problem comes in with the odd dollar rate time partial hours. For example:

$9.73 x 4.5 hours = $43.785 (Rounds to $43.79) which is fine...

but when you have a whole column of these rounded numbers, the totals are coming out off by pennies which add up to a good bit at the end of the month. I cannot match up the totals with the invoices that are coming in either.

View Replies


ADVERTISEMENT

Hourly Rate Of Pay For Access Development

Nov 8, 2005

I am self-taught on Access and have designed a quite basic database for a friend who owns his own company. This is a client contact database designed to hold information about his clients and their companies, but also to send reminders to Outlook when they haven't been contacted for a while - ie. to make my friend more proactive in his business.

This was a project which I originally agreed to work on for a fixed price. Needless to say, I undersold myself and probably because I am self-taught, with the number of hours I have put in, I probably got paid 2p an hour!

The project is drawing to a close, but he wants to pay me per hour for any further development to the system, and for any similar work he wants doing, problem is I haven't a clue what to charge him per hour?

Any ideas, without divulging your well-earned salaries of course!

View 8 Replies View Related

Concerning Prices: Hourly Rate / One-time Amount

Mar 26, 2008

I'm asked to do a specific task, a database concerning of:Tables: 25Forms: 25Reports: 40-45Build from ground up, multi-user, accounts, account-restrictions, has to be linked to an already existing database, will need a fair amount of VBA coding.I would like to know specifically two things:--> what is your common hourly rate --> what would your common total-price-estimate for a project like this.

View 1 Replies View Related

Adding And Subtracting Totals From 3 Separate Reports

Sep 4, 2015

I have 3 reports

Report 1. is attendance from January to June
Report 2. is from June to December
Report 3. is from January to December

The are all run from the same query with different Between Dates.

The scenario is that Paul attended the support group from Jan to June so in the Jan to June report he is counted once. The query removes duplicate values

Paul attended the same support group from June to December so in the June to December report he is counted once. The query removes duplicate values.

This shows the Paul attended he support group from Jan to December so in the June to December report he is counted once. The query removes duplicate values, if you removed the duplicates from this query he would be counted twice.

Not all people attend the same group for 12 months but if they do the are a duplicate and Paul is a duplicate. That is ok because we need to know now many duplicates there are for the year.

If I print all 3 reports with the duplicates remove query and you looked at the totals it would show (Example) 30 attendees in the 1st report and 30 attendees in the 2nd report but in the 3rd report it would show 59 not 60 which is the total of reports 1 & 2. we need to report these figures as 1st 6 months with 30 and the second 6 months with 30 but the whole year would be with 59 and 1 duplicate.

The way it is now I have to run all 3 report and do the math by hand this way.

Is there a way on the to do the math with code calculations on the full year report with the figures from the 2, 6 month reports to automatically show total attendance for the year 59 with 1 duplicate?

View 1 Replies View Related

Queries :: Sum Calculated Query Without GroupBy Totals?

Jul 11, 2013

Im trying to run the following calculated query:

Total Value on order: Sum([Purchase Price])

I want to sum the total of all the purchase prices together so I know the total value on order. I keep getting an error: "You tried to execute a query that does not include the specified expression Purchase Order' as part of an aggregate function

View 2 Replies View Related

Totals Of Calculated Fields In Report Footer

Dec 10, 2014

I have a Report that has calculated fields in the details section. I want to total those fields in the Report Footer. When I run the report I am prompted to enter the value for the calculated fields and the Totals do not appear. Access must be inspecting the report before it actually runs. And so, since the calculated fields do not exist until the report is run then Access doesnt find them during the inspection and hence prompts for input.

View 2 Replies View Related

Immediately Updating Totals Calculated From Subform/query

Aug 6, 2005

I have a problem with a total on my main form (calculated from a subform) not updating immediately. The updated total only displays correctly after I’ve navigated to another record and then back again. I would like the totals to update immediately after I’ve entered the new data. Can anyone help? Here are the specifics…

My main form has a subform which displays a dynaset of records from a query. I’ve set up this subform so that I can enter a new record, which is then written back to the record source for the query. This is accomplished via a combo box from which the user selects a product, and an AfterUpdate event returns the price of that product to the TotalPrice control on the subform. In the footer of this subform is a calculated control (called AdExpenseSubTotal), which calculates the sum of the TotalPrice field from the query. The footer of this form is hidden, and I’ve set up a control on the main (TotalSpent) form which displays the value of the subform control AdExpenseSubTotal. This total seems to update immediately whenever I tab off the relevant field in the subform – so far so good, no problems here.

Now here’s where it gets tricky. My main form has another subform which is hidden. This subform returns a dynaset of records from another query which has the same record source as the query on the first subform. Like the first subform, this subform also has a hidden calculated control with an aggregate function (Sum), and a calculated control (VendorSpent) on the main form to display its value.

As I said before, when a enter a new record in the first (displayed) subform (via the combo box) and tab off it, the TotalSpent control on the main form updates immediately, but the VendorSpent control doesn’t. Obviously the new record I’ve added doesn’t immediately show up in the query dynaset of the second (hidden) subform from which the VendorSpent control on the main form gets its value. The VendorSpent control only displays the updated total when I navigate to the next record, and then navigate back again.

Is there some Event Procedure I need to add to the AfterUpdate property of the combo box on the first subform which immediately forces the second (hidden) subform to requery? I probably haven’t explained myself very clearly, but I can provide a copy of the file (it’s only 184 Kb) if that helps.

View 2 Replies View Related

Reports :: Subtract Totals From 2 Sub-reports In Main Report Of Access?

Dec 28, 2014

I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.

On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.

On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.

I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.

Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.

=[rptP&LExpensesOverview]![AccessTotalsAmount]

=[rptP&LSalesOverview]![AccessTotalsTotal Sales]

To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.

As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).

View 2 Replies View Related

Adding Up Totals

Feb 20, 2007

I have a Query which im using for a program that controls my Bill Of Materials(BOM) for my products Now i need a second Query to add up my total for each product.

Query1
BOM | ItemNo.| Cost
ProdA 1 $3
ProdA 2 $2
ProdA 3 $2
ProdB 1 $4
ProdB 2 $4
ProdB 3 $1

What i Want the second Query to read is

BOM | Total
ProdA $7
ProdB $9


How do i get it to add up.

View 1 Replies View Related

General :: Adding Two Totals Together?

Jul 18, 2013

I am trying to add two columns with multiple records in them together using VBA.

I have one column named "Quantity" and that has 5 records going down

Column1(Row1) = 2
Column1(Row2) = 3
Column1(Row3) = 1
Column1(Row4) = 2
Column1(Row5) = 2

I have another column name "Quantity2" and that has another 5 records going down

Column2(Row1) = 1
Column2(Row2) = 4
Column2(Row3) = 2
Column2(Row4) = 2
Column2(Row5) = 2

I need a code to simply add them all together together using VBA and put the value back in a text box.

So the Value in the text box would be = 21

View 9 Replies View Related

Getting Totals By Adding Other Fields Within A Table

Mar 25, 2008

Hi there

I am completely new to access so if this question seems really simple then i apologise. If this has appeared elsewhere on this forum i apologise again!

Can you tell me if the following is possible

I have a table called AA1 containing the following fields:
(PKey)Refno
StudentNo
PC1
PC2
PC3
PC4
PC5
PC6
Range1a
Range1b

What i would like to do is create another two fields called PC total and Range total whereby i create a formula that will add the totals of the PCs for each student )which will then be entered into the PC total column and the same for the ranges.


I am unable to do it using the query function as i have over 225 diffrerent fields overall so i thought if i could create a totals field i could use these for my queries therefore negating the need to include all the seperate fieids, pc1 pc2 pc3 etc...

the above fields are all in number format.

I have probably not explained myself very well and if you need any more information (im guessing yes) then please let me know.

thanks in advance

Jemma

View 2 Replies View Related

Adding Sum Totals To Output In Excel

Sep 8, 2006

Hello first of all I have learned a lot from this forum so thanks you all who post questions & solutions. Ok I have a table which I use to query info and export to excel. My question is there a way to add sum totals to a few of the columns once it is in excel. I was thinking it would be easier to do a sum statement then just add it as the bottom row while it was exporting. Or would it be easier to do it once it is in excel. Here is my code:

Private Sub Command63_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.CUSTOMER_TYPE, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.E_MAIL, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.REP_NUMBER, tblCONSOLIDATED.PROMOCODE, tblCONSOLIDATED.SALESCODE, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"


strWhere = "WHERE"

strOrder = "ORDER BY CURRENT_YTD DESC"

If Not IsNull(Me.txtCSONME) Then
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND"
End If

If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If

If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If

If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If

If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND"
End If

If Not IsNull(Me.txtCSOZIP) Then
strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND"
End If

If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.REP_NUMBER) Like '*" & Me.txtCSOSSM & "*' AND"
End If

If Not IsNull(Me.txtCSOM1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PROMOCODE) Like '*" & Me.txtCSOM1 & "*' AND"
End If

If Not IsNull(Me.txtSLCYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN " & Me.txtSLCYYD1 & " And " & Me.txtSLCYYD2 & " AND"
End If

If Not IsNull(Me.txtSLLYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) BETWEEN " & Me.txtSLLYYD1 & " And " & Me.txtSLLYYD2 & " AND"
End If

If Not IsNull(Me.txtSLPYR11) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_TOTAL) BETWEEN " & Me.txtSLPYR11 & " And " & Me.txtSLPYR12 & " AND"
End If

If Not IsNull(Me.txtSLPYR21) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR2_TOTAL) BETWEEN " & Me.txtSLPYR21 & " And " & Me.txtSLPYR22 & " AND"
End If

If Not IsNull(Me.txtSLPYR31) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR3_TOTAL) BETWEEN " & Me.txtSLPYR31 & " And " & Me.txtSLPYR32 & " AND"
End If

If Not IsNull(Me.txtSLPYR41) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR4_TOTAL) BETWEEN " & Me.txtSLPYR41 & " And " & Me.txtSLPYR42 & " AND"
End If

If (Me.PROSPECTBX) = True Then
strWhere = strWhere & " (tblCONSOLIDATED.CUSTOMER_TYPE) Like 'P' AND"
End If

If Not IsNull(Me.txtSLCLS) Then
strWhere = strWhere & " (tblCONSOLIDATED.SALESCODE) Like '*" & Me.txtSLCLS & "*' AND"
End If


If strWhere = "WHERE" Then
strWhere = ""
Else
strWhere = Trim(Left(strWhere, Len(strWhere) - Len("AND")))
End If

Set qryDef = dbNm.QueryDefs("qrySALESDATA")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder

DoCmd.OutputTo acQuery, "qrysalesdata", "MicrosoftExcel(*.xls)", "QUERY RESULTS.xls", True, ""


End Sub

Sorry for adding the whole thing but I'm not sure it should be done after "Query Results.xls" is written or during the select statement.

Thanks in advance for your help.

Bryan

View 1 Replies View Related

Reports :: Getting Totals From Five Reports To Create One Report

Feb 18, 2015

Is there a way to have say five different reports that give out information and at the bottom the totals. I would like to take each of those separate reports to create one report with just the totals.

View 3 Replies View Related

Queries :: Adding Totals For Fields With Same Values

Aug 13, 2015

I've got a table with data that's been imported from Excel. I need to run a query that pulls the same values of field one and adds the values of field 2. For instance:

Field 1; Field 2
Jones; 200
Smith;150
Jones; 300
Smith;100

In this example, I need the sum of field 2 for the records where Jones is the value in field 1.

So the result in this case is that records where Field 1 = Jones, Field 2 = 500.

View 2 Replies View Related

Reports :: Summing Calculated Fields On Reports And Tables To Include Cents?

Feb 18, 2014

In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)

View 2 Replies View Related

Totals In Reports

Mar 15, 2006

If I have a report that is returning numberous records from a table, how do i set it up to total the values in each field (AT THE END)

I have a series of query's that can calculate the totals I am looking for but cant find a way to place them at the end of the report. If I place them in the header or footer it puts them on every page....

Any thoughts how to just have them show up at the END of the report?

THanks in Advance

View 2 Replies View Related

Running Totals In Reports

Apr 30, 2005

I need some help with a report that has running totals of both positive and negative values. The report needs to have a subtotal of the positive values only, then the negative values only. The negative and positive values appear all in the same list. The running sum property over group/over all only adds all the values together which is of no use. Is there any easy way to do this?


Example Report snippet:
(Ignore all the dots - its the only way I could get everything to line up)

# Code...Cleaner...Current..+1Week
1..12....J. Smith........$12.20.....$4.50
2..15....R. Anderson...$10.00.....$2.30
3..18.....E. Jones.....-$14.00.....$1.50
4..19....A. Rob...........$3.50....-$2.50

TOTALS: (+)............$25.70.....$8.30
.............(-)...........($14.00)...($2.50)
-------------------------------------
Balance.................$11.70.....$5.80


View 1 Replies View Related

Reports :: Getting Totals At The Top Of Columns?

Mar 14, 2014

I have several reports on access 2010 that lists items and at the bottom in the report footer gives me a total for the columns. Is there any way that those totals can be put at the top of the columns such that the person reviewing the reports can see the totals first, then scroll down to see the details?

View 3 Replies View Related

Reports :: Zero Values And Calculated Fields In Reports?

Nov 13, 2013

I am trying to get calculated fields in my report to work but zero values in the data are throwing up #Div/0! and #Error! and #Num!

The main data fields I am trying to work with are:

VehicleReading
PreviousReading
VehicleLitres

My aim is to track mileage and consumption for a fleet of vehicles and show daily and weekly averages in mileage and consumption and then to flag 20% increase in consumption.

I have a calculated field in my report named Kilometers,

= [VehicleReading]-[PreviousReading]

and also a calculated field named Consumption,

=[Kilometers]/[VehicleLitres]

My sum and average calculation fields in the group footers are only working where there are values above zero in the Kilometer and VehicleLitres and Consumption fields.

In reality there are some days where there has been no travel, so some records will have a zero for the Kilometers field and there are also days when there has been no refueling so there is a zero for the VehicleLitres field.

I have tried using the expression builder to create an IIf function but to no avail.

I have tried copying the syntax suggested by the Expression builder:

«Expr» IIf («expr». «truepart». «falsepart»)

I know I need to get those zero's to fail in the calculation but as I said before all of the suggested expressions I have tried fail due to syntax!

View 11 Replies View Related

Reports :: Grouped Totals Report

May 8, 2013

I have a table with:

Invoice Number | Customer Name | Item | Item Cost | Invoice Date | Paid | Date Paid

Example of data:

AK001 | A Brown | Blue Car |1000 | 1/4/2013 | Yes | 20/4/2013
AK001 | A Brown | Red Car |2000 | 1/4/2013 | Yes | 20/4/2013
AK001 | A Brown | Yellow Car |500 | 1/4/2013 | Yes | 20/4/2013
AK002 | A Brown | Black Car |1000 | 7/4/2013 | Yes | 20/4/2013
AK003 | B Smith | Blue Car |1000 | 12/4/2013 | Yes | 25/4/2013

I want to create a report from this table that outputs as:

Invoice Number | Customer Name | Total Price | Invoice Date | Paid | Date Paid

Example of report from Example Data:

AK001 | A Brown |3500 | 1/4/2013 | Yes | 20/4/2013
AK002 | A Brown |1000 | 7/4/2013 | Yes | 20/4/2013
AK003 | B Smith |1000 | 12/4/2013 | Yes | 25/4/2013

Is there an easy way to do this.. or will I need to make a new linked table with the invoice number as a lookup?

View 2 Replies View Related

Combining Reports To 1 Report To Display Totals

Aug 29, 2005

I have monthly reports developed from various query's and what I want to do is have a single report that I can display my monthly totals from each monthly report without developing a whole new report. I want to use the existing totals from the monthlies and have them all on one report. Can anyone help me?
Jaxfire

View 8 Replies View Related

Reports :: Subreport Totals On Main Report

Jul 30, 2015

I am trying to get my totals from my subreports, and dividing the number by 2 or 4, and put the new number on the main report.

for Operator/Trainer Productivity, I used:
=([rptEmployEvaluationOperator subreport].[Report]![OPTotal]+[rptEmployEvaluationOperatorTrainer subreport].[Report]![OPTotal])/2

for Auditor/Trainer Productivity, I used:
=([rptEmployEvaluationAuditor1 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor2 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor3 subreport].[Report]![OPTotal]+[EmployEvaluationAuditorTrainer subreport].[Report]![OPTotal])/4

These both work if there are values in all totals subreports. When one of them might not have a total, I get an error message.

I tried to use :

=IIf([Orders].[Report].[HasData], [Orders].[Report].[txtOrderValue], 0)
=IIf([Orders].[Report].[HasData], Nz([Orders].[Report].[txtOrderValue], 0), 0)

=iif([rptEmployEvaluationOperator subreport].[Report].[HasData],([rptEmployEvaluationOperator subreport].[Report].[OPTotal]+=iif([rptEmployEvaluationOperatorTrainer subreport].[Report].[HasData],[rptEmployEvaluationOperatorTrainer subreport].[Report].[OPTotal])/2

and the same idea for the second one. I tried both methods and did not work. I am not sure what I am missing.

View 10 Replies View Related

Reports :: Totals From Multiple Combo Box Fields

Sep 4, 2014

I'm fairly new to Access but I've been tasked to re-jig an old databse running on Access 2003. So far the database is doing more or less what I want it to but I need to generate a report showing totals from multiple fields in combo boxes. As an example, I have a combo box for a job booking-in form which requires the type of task to be selected e.g Video, photography, editing. The report needs to show the total booking numbers for each tasks (combo field) at the end of the month. I've managed to create reports for totals of a single field in the combo box but I'm struggling in achieving the multiple totals.

View 7 Replies View Related

Reports :: Deriving Totals Based On Combo Box

Jul 2, 2013

I have a combo box named Session which comprises of am and pm. I have a number of fields that relate to this combo box.

E.G. Monday am Sales £1000
Monday pm Sales £ 2000

I want to create a total Sales of the am and pm to derive a total of £3000.i.e. Monday Sales is £3000 which is a combination of the am and pm sessions.

View 7 Replies View Related

Reports :: Generating Totals In Monthly Report

Jun 1, 2013

I've got a simple invoice database with 10 amount fields, that needs to be summed up intoa Total including Gst box on a monthly report.

Report only has Date, Invoice Number And Total Amount fields on it, so I need to pull the information from table and sum it into Total Amount.

ie. = sum ( t1 +t2 + t3 + t4 + t5 + t6 + t7 + t8 + t9 + t10)
and sum above divided by 10% then added together to form total.

I'm just not sure how to do it, everything I've tried so far ain't worked.

Every example I've looked at is only doing maths with 1 field.

View 4 Replies View Related

Reports :: Show Totals Extracted From Tables

Oct 9, 2014

I routinely produce a report for regular Committee Meetings which includes statistical date derived from the total rows for various fields in 1 or more Tables.It should be possible to extract such data automatically, probably using a query. So far, my endeavors in this direction have been unsuccessful.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved