Selasa, 18 Oktober 2011

RE: [MS_AccessPros] Having troubel with Sum function in a report

 

David-

OK, I fixed it. I got rid of the extra two tables in the report Record Source and added the totals using DSum to make it easy to total in the report. The file in in 2_AssistanceNeeded.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of djsdaddy531
Sent: Tuesday, October 18, 2011 5:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Having troubel with Sum function in a report

John, I've tried every solution that you've offered, but still no success. I hope I am not being presumptuous by uploading a file for you to look at. This approach uses your suggestion of adding subreports to obtain the values for Parts Used and Labor Cost. The name of the report is rpt4CustomerRepairOrders. You will note that I have made rptParts-RepairOrder subreport visible so you can compare the figures in the parent report with the figures in the subreport. rptRepair-RepairOrder subreport currently has visible set to false.

Thanks for your assistance,

David

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> David-
>
> When you ask for fields from a 1-M join, you'll a number of rows equal to the number on the "many" side. You can ask the report to group on Customer, Job, Vehicle, etc, to reduce the number of rows you display for each customer, job, or vehicle, then total the parts cost. But it looks like you might have TWO 1-M joins. tblJobs -> tblPartsUsed and tblJobs -> tblScopeOfJob. If there are multiple rows for a job in both parts and scope, then you'll get parts * scope number of rows. Consider removing both parts and work requested from the query (along with the related tables) and putting those two in subreports if that's the problem.
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of David Tolson
> Sent: Monday, October 17, 2011 12:54 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Having troubel with Sum function in a report
>
> Thanks John, I must not be seeing something correctly with the suggested solution that you gave, but I guess I’m trying to fit a square peg into a round hole. The reason I am doing the summing on fields in subreports is because my query does not seem to be functioning the way I want it to, so I tried a work-around. According to all that I’ve studied, I should be able to sum on the field in the query/table with no problem, but because I need some information out of a many side of a relationship with two tables, my query is displaying the number of records for each job (although there should only be one) equal to the number of records in the many side of the two tables. The two tables connected on the many side of the JobID is tblPartsUsed and tblScope ofWork.
>
>
>
> Here is the SQL for the report, and perhaps you can make some suggestions here.
>
>
>
> SELECT tblJobs.JobID, tblJobs.VehicleID, tblMasterCustomer.CustomerID, [LastName] & ", " & [FirstName] AS FullName, tblMasterCustomer.FirstName, tblMasterCustomer.LastName, tblJobs.RepairDateOut, [ModYear] & " / " & [Make] & " / " & [Model] AS YearMakeModel, tblMakes.MakeID, tblMakes.Make, tblModels.ModelID, tblModels.Model, tblJobs.MiscellaneousCost, tblPartsUsed.PartQty, tblPartsUsed.PartCost, [PartCost]*[PartQty] AS TotPartCost, tblScopeOfJob.WorkRequestedCost
>
> FROM ((tblModels INNER JOIN (tblMakes INNER JOIN (tblVehicles INNER JOIN (tblMasterCustomer INNER JOIN tblJobs ON tblMasterCustomer.CustomerID = tblJobs.CustomerID) ON tblVehicles.VehicleID = tblJobs.VehicleID) ON tblMakes.MakeID = tblVehicles.MakeID) ON tblModels.ModelID = tblVehicles.ModelID) INNER JOIN tblPartsUsed ON tblJobs.JobID = tblPartsUsed.JobID) INNER JOIN tblScopeOfJob ON tblJobs.JobID = tblScopeOfJob.JobId
>
> ORDER BY tblJobs.RepairDateOut;
>
>
>
> I also tried “Select Distinct” at the beginning and instead of seeing the 98 records that I am seeing, I saw 85, but there are only 24 records in tblJobs where JobID is the one side of the relationship.
>
>
>
> Thanks again,
>
>
>
> David
>
>
>
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Sunday, October 16, 2011 9:57 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Having troubel with Sum function in a report
>
>
>
>
>
> David-
>
> If Sum of PartCost1 is in the outer report, you don't need the full Reports! stuff. You say txtPartsTotal is in the Record Source of the subreport, so to sum it on the outer report, do:
>
> Sum of PartCost1 Control Source =Sum([Report]![rptParts-RepairOrder subreport].Report![txtPartsTotal])
>
> Likewise if txtLaborTotal is in the Record Source of the subreport, then to calculate the total on the outer report use:
>
> Sum of WorkRequestedCost1 Control Source = Sum([Report]![rptParts-RepairOrder subreport].Report![txtLaborTotal]
>
> What other totals are you trying to get on the outer report? If you're wanting to sum fields found on the subreport, use the pattern above. Note that fields like txtPartsTotal and txtLaborTotal need to be in controls in the subreport (they can be hidden). The report engine builds its own query from your specified Record Source and will often not include fields that aren't also bound to some control. It's an "optimization" that drives me nuts sometimes!
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortal
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David Tolson
> Sent: Sunday, October 16, 2011 3:32 PM
> To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Having troubel with Sum function in a report
>
> Thanks John, The calculations for the detail line are as thus:
>
> txtPartsTotal is calculated in the record source of the subreport… =Nz(Sum([ExtPrice]))
>
> txtTaxTotal is calculated in the record source of the report… =[txtPartsTotal]*0.06
>
> txtLaborTotal is calculated in the record source of the subreport… =Nz(Sum([WorkRequestedCost]))
>
> txtMiscCost = Miscellaneous cost that is in the table
>
> The calculations for total fields in the MakeID Footer are as thus:
>
> Sum of PartCost1 â€" Record Source: =Sum([Reports]![rpt4CustomerRepairOrders]![rptParts-RepairOrder subreport].[Report]![txtPartsTotal])…This is calculated in the record source of the subreport
>
> [txtTaxTotalMake] â€" Record Source: =Sum([txtPartsTotal]*0.06)…this is calculated in the record source of the report
>
> [Sum of WorkRequestedCost1] â€" Record Source: =Sum([Reports]![rpt4CustomerRepairOrders]![rptParts-RepairOrder subreport].[Report]![txtLaborTotal])… This is calculated in the record source of the subreport
>
> [txtMiscCostMake] â€" Record Source: =Sum([txtMiscCost])… this is calculated in the record source of the report
>
> Also, the expression used in txtPartsTotal is =IIf([rptParts-RepairOrder subreport].[Report].[HasData],[rptParts-RepairOrder subreport].[Report]![txtPartsTotal],0). When I use that expression I get $0.00 when for the first vehicle, I should see $395.08. I also tried the expression without the IIf and got a blank.
>
> Thanks,
>
> David
>
> From: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John Viescas
> Sent: Sunday, October 16, 2011 8:36 AM
> To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Having troubel with Sum function in a report
>
> David-
>
> It would be helpful to know where you're performing each of these calculations. Are any of them in the Record Source of the report or subreport? Where have you place Sum of PartCost1, txtTaxTotalMake, Sum of WorkRequesteedCost1, and txtMiscCostMake? On the report or the subreport? Note that you get prompted for txtPartsTotal because that control is calculated. You have to repeat the expression used in the Control Source of txtPartsTotal in your Sum.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David Tolson
> Sent: Sunday, October 16, 2011 2:03 PM
> To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Having troubel with Sum function in a report
>
> Thank you Crystal, I repeated the equation and the fields still end up blank. Also Access is prompting me for a parameter value for all of the fields where I repeat the equation. Here are the four fields that I am attempting to sum and their corresponding control sources:
>
> [Sum of PartCost1] â€" Record Source: =Sum([Reports]![rpt4CustomerRepairOrders]![rptParts-RepairOrder subreport].[Report]![txtPartsTotal])
>
> [txtTaxTotalMake] â€" Record Source: =Sum([txtPartsTotal]*0.06)
>
> [Sum of WorkRequestedCost1] â€" Record Source: =Sum([Reports]![rpt4CustomerRepairOrders]![rptParts-RepairOrder subreport].[Report]![txtLaborTotal])
>
> [txtMiscCostMake] â€" Record Source: =Sum([txtMiscCost])
>
> Sum of PartCost1 is the sum of a field in a subreport that is not visible, but the proper amount is displayed in the detail field, and the same is true for Sum of WorkRequestedCost1.
>
> Access is prompting me for a value for txtPartsTotal, txtLaborTotal, and txtMiscCost, although these fields display the proper amounts when the report opens on screen.
>
> It may be helpful to know that the equation for txtPartsTotal has an IIF statement as does the equation for txtLaborTotal:
>
> =IIf([rptParts-RepairOrder subreport].[Report].[HasData],[rptParts-RepairOrder subreport].[Report]![txtPartsTotal],0)
>
> =IIf([rptRepair-RepairOrder subreport].[Report].[HasData],[rptRepair-RepairOrder subreport].[Report]![txtRepairItemTotal],0)
>
> FYI, I also tried repeating the equation in the footer total with the IIF, and the fields gave me $0.00 instead of blanks like they did in the above record sources.
>
> Thanks again for your help so far,
>
> David
>
> From: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Crystal
> Sent: Saturday, October 15, 2011 11:49 PM
> To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Having troubel with Sum function in a report
>
> hi David,
>
> you cannot sum a control that has an equation -- repeat the equation
>
> =sum( <equarion in txtPartsTotal>)
>
> ~Crystal
>
> ________________________________
> From: djsdaddy531
> Great Day, I have created a report that displays the revenue that a vehicle repair shop has made on servicing its customers' vehicles. The fields look something like this:
>
> InvoiceDate
> Cost of Parts
> Tax
> Service Cost
> Misc. Cost
>
> And then it sums these fields on each vehicle and each customer, since a customer can have more than one vehicle. Then it does a grand total for the report. For aesthetics I have placed the text boxes for the above referenced fields in the JobID Header (RepairOrder), and I am placing the total fields for the vehicle in the YearMakeModel footer and the total fields for the customer in the Customer Footer. In the JobId footer(repair order) I have the following control source for summing the Cost of parts field…
>
> =Sum([txtPartsTotal])
>
> and so forth for the other fields. txtPartsTotal is the field that grabs the sum total of parts used from a subreport. However, when the form opens (rptCustomerRevenue), a dialogue box is requesting the I enter a parameter value for txtPartsTotal, although txtPartsTotal displays the appropriate number.
>
> I would greatly appreciate if someone can tell me why I am getting these parameter requests.
>
> Thanks in advance.
>
> r/David
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

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

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar