Sum in textbox on report 3

[sk1hotpepr]

I have an .adp project that I'm trying to create a report for. I have a stored procedure that accepts a parameter. After the procedure runs, I open a report based on what records the stored procedure retrieves. My problem is that I have a report that I want to have a sum based on an id number in a textbox.

This all worked before I upsized to an access project, now I get all errors in the textboxes - this is an example:
=Nz(DSum("ApprovedCost","myBudget","ContractorID = 2"),0).

Is there any way to create sums in the report or is this something i need to do in a sql statement? any help would really be appreciated, i'm at a complete loss.

thanks.

 

[cmmrfrds]

The detail line textbox name can be referenced in a footer.

=Sum(textboxfield)

 

[sk1hotpepr]

Is there any way that I could get a sum with criteria in a textbox on the report by NOT using a footer? I have a layout created for a single page summary report with sum breakdowns by reason and contractor. I've seen a little about crosstab reports, but I don't know the first thing as to how to put them together. I currently have a simple stored procedure that I use as my report recordsource.

 

[cmmrfrds]

Show a layout of how the data looks on the report and whether it is summary or detail data.

 

[sk1hotpepr]

My layout is set up like this:

Reason1 Reason2 Reason3
General sum sum sum
Site ... ... ...
Mechanical ... ... ...
Electrical ... ... ...

Does that help?

 

[cmmrfrds]

That helps. Assuming you have a type field that can be reason 1, 2, or 3 i.e a predetermined number of columns.

Since this an ADP, you can put the query directly in the record source of the report. Something like.

Select contractname,
sum(case when type = 'reason1' then amount else 0
end) as reason1,
sum(case when type = 'reason2' then amount else 0
end) as reason2,
sum(case when type = 'reason3' then amount else 0
end) as reason3
From yourtable
where somecriteria
Group by contractname

 

[sk1hotpepr]

How can I place the sum(case...) statement within a stored procedure? This is some code that I had started and I want to change it to a case statement for ease, but I'm not sure how to do it, can you help? Here is part of it

ALTER PROCEDURE dbo.myBudget
(@ProjectNumber [char] (10))
AS SELECT     dbo.ChangeOrderTable.ProjectNumber, dbo.ProjectInformation.ProjectName, dbo.ProjectContractor.GCFN, dbo.ProjectContractor.SiteFN, 
                      dbo.ProjectContractor.MechFN, dbo.ProjectContractor.PlumbingFN, dbo.ProjectContractor.ElectFN, dbo.ProjectContractor.FireProtFN, 
                      dbo.ProjectContractor.AsbestosFN, dbo.ProjectBidInfo.GCActBid, dbo.ProjectBidInfo.SiteActBid, dbo.ProjectBidInfo.MechActBid, 
                      dbo.ProjectBidInfo.PlumbingActBid, dbo.ProjectBidInfo.FPActBid, dbo.ProjectBidInfo.ElecActBid, dbo.ProjectBidInfo.AsbActBid, 
                      dbo.ChangeOrderTable.ContractorID, dbo.ChangeOrderTable.ProposedCost, dbo.ChangeOrderTable.ReasonID, 
                      dbo.ChangeOrderTable.SchoolDistrictCost, dbo.ChangeOrderTable.GilbertArchitectsCost,
                          (SELECT     SUM(dbo.ChangeOrderTable.ApprovedCost)
                            FROM          dbo.ChangeOrderTable
                            WHERE      ContractorID = 1) AS cd1,
                          (SELECT     SUM(dbo.ChangeOrderTable.ApprovedCost)
                            FROM          dbo.ChangeOrderTable
                            WHERE      ContractorID = 2) AS cd2,
                          (SELECT     SUM(dbo.ChangeOrderTable.ApprovedCost)
                            FROM          dbo.ChangeOrderTable
                            WHERE      ContractorID = 3) AS cd3,
                          (SELECT     SUM(dbo.ChangeOrderTable.ApprovedCost)
                            FROM          dbo.ChangeOrderTable
                            WHERE      ContractorID = 4) AS cd4,
                          (SELECT     SUM(dbo.ChangeOrderTable.ApprovedCost)
                            FROM          dbo.ChangeOrderTable
                            WHERE      ContractorID = 5) AS cd5,
                          (SELECT     SUM(dbo.ChangeOrderTable.ApprovedCost)
                            FROM          dbo.ChangeOrderTable
                            WHERE      ContractorID = 6) AS cd6,
                          (SELECT     SUM(dbo.ChangeOrderTable.ApprovedCost)
                            FROM          dbo.ChangeOrderTable
                            WHERE      ContractorID = 7) AS cd7
FROM         dbo.ProjectContractor FULL OUTER JOIN
                      dbo.ProjectBidInfo ON dbo.ProjectContractor.ProjectNumber = dbo.ProjectBidInfo.ProjectNumber FULL OUTER JOIN
                      dbo.ChangeOrderTable ON dbo.ProjectContractor.ProjectNumber = dbo.ChangeOrderTable.ProjectNumber FULL OUTER JOIN
                      dbo.ProjectInformation ON dbo.ProjectContractor.ProjectNumber = dbo.ProjectInformation.ProjectNumber AND 
                      dbo.ProjectBidInfo.ProjectNumber = dbo.ProjectInformation.ProjectNumber AND 
                      dbo.ChangeOrderTable.ProjectNumber = dbo.ProjectInformation.ProjectNumber
WHERE     (dbo.ChangeOrderTable.ProjectNumber = @ProjectNumber)
ORDER BY dbo.ChangeOrderTable.ProjectNumber

I have this stored procedure as my Record Source currently with each new column as a place on the record. I know this isn't the best way to do it, I'm just learning as I go. Thanks so much for your help!

 

[cmmrfrds]

(SELECT SUM(dbo.ChangeOrderTable.ApprovedCost)
FROM dbo.ChangeOrderTable
WHERE ContractorID = 1) AS cd1,

Sum(Case when ContractorID = 1 then ApprovedCost else 0 end) as cd1,

 

[sk1hotpepr]

Do I use the Select statement for that line? I wrote in my sum statements with a separate select subquery but get an ado error telling me that only one expression can be specified in the select list when the subquery is not introduced with EXISTS. When i don't use the select statement, it goes crazy with aggregate errors. I've been searching documentation, with not a whole lot of luck.

 (SELECT SUM (CASE WHEN ContractorID = 1 THEN ApprovedCost ELSE 0 END) as ap1,
			SUM (CASE WHEN ContractorID=2 THEN ApprovedCost ELSE 0 END) as ap2)

Again, I really appreciate your help, I'm starting to feel like I will eventually get to finish this project.

 

[cmmrfrds]

I didn't look close enough, I had assumed your query was an aggregate query. If you can add all the other fields to the group by clause then the case statement I showed will work. It would be sort of like using Select Distinct. You would need to add project id, name, etc.. to the group by.

 

[sk1hotpepr]

Cmmrfrds

Thank you so much for your help, alittle juggling with the code and it now works!

(SELECT SUM (CASE when ContractorID = 1 THEN ApprovedCost ELSE 0 END) FROM dbo.ChangeOrderTable as ga1),
			(SELECT SUM (CASE when ContractorID = 2 THEN ApprovedCost ELSE 0 END) FROM dbo.ChangeOrderTable as ga2),
			(SELECT SUM (CASE when ContractorID = 3 THEN ApprovedCost ELSE 0 END) FROM dbo.ChangeOrderTable as ga3),
			(SELECT SUM (CASE when ContractorID = 4 THEN ApprovedCost ELSE 0 END) FROM dbo.ChangeOrderTable as ga4),
			(SELECT SUM (CASE when ContractorID = 5 THEN ApprovedCost ELSE 0 END) FROM dbo.ChangeOrderTable as ga5),
			(SELECT SUM (CASE when ContractorID = 6 THEN ApprovedCost ELSE 0 END) FROM dbo.ChangeOrderTable as ga6),
			(SELECT SUM (CASE when ContractorID = 7 THEN ApprovedCost ELSE 0 END) FROM dbo.ChangeOrderTable as ga7)

 

[PaulBricker]

I was able to use the info from this post to solve a string comparison problem that would have been easy using VBA or the InStr() function, but neither of which were an option.
Great stuff

Paul