If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Select Sum SQL help
I am trying to create a report with a text box as a calculated control using
SQL Select statement. In the Code Header Section there is a text box control with the a code number generated by the user when the form opens. With that Code number, I want to sum the allocated amount based on that number and I am trying to do that with a Select clause. What I have is not working and I have made several other attempts without success. Maybe someone can help me. SELECT [PO Master].Code, Sum([PO Master].Allocation) AS SumOfAllocation FROM [PO Master] GROUP BY [PO Master].Code Where ((([PO Master].Code)="[code]")); |
#2
|
|||
|
|||
Select Sum SQL help
Billy B wrote:
I am trying to create a report with a text box as a calculated control using SQL Select statement. In the Code Header Section there is a text box control with the a code number generated by the user when the form opens. With that Code number, I want to sum the allocated amount based on that number and I am trying to do that with a Select clause. What I have is not working and I have made several other attempts without success. Maybe someone can help me. SELECT [PO Master].Code, Sum([PO Master].Allocation) AS SumOfAllocation FROM [PO Master] GROUP BY [PO Master].Code Where ((([PO Master].Code)="[code]")); Use DSUM instead -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201004/1 |
#3
|
|||
|
|||
Select Sum SQL help
Billy B wrote:
I am trying to create a report with a text box as a calculated control using SQL Select statement. In the Code Header Section there is a text box control with the a code number generated by the user when the form opens. With that Code number, I want to sum the allocated amount based on that number and I am trying to do that with a Select clause. What I have is not working and I have made several other attempts without success. Maybe someone can help me. SELECT [PO Master].Code, Sum([PO Master].Allocation) AS SumOfAllocation FROM [PO Master] GROUP BY [PO Master].Code Where ((([PO Master].Code)="[code]")); Control expressions can not use a query. OTOH, you can use the DSum function that will run a simple query for you. I think your query translats to: =DSum("Allocation", "PO Master". "Code=""" & Code & """") But, that will sum all the records in the table with its code field matching the current value of the code field in the report. This is rarely what is desired. Most often, the desired calculation is over the records in the report or each group; =Sum(Allocation) -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Select Sum SQL help
You can't use a SQL statement as a Control Source. If you want to sum a
numeric value from a report's section, use a text box with a control source like: =Sum([Allocation]) If for some unusual reason, you want to sum records and/or fields that aren't part of your report's record source then you can use DSum(). I hate DSum() and find it is often used when people don't understand how to use Sum(). -- Duane Hookom Microsoft Access MVP "Billy B" wrote: I am trying to create a report with a text box as a calculated control using SQL Select statement. In the Code Header Section there is a text box control with the a code number generated by the user when the form opens. With that Code number, I want to sum the allocated amount based on that number and I am trying to do that with a Select clause. What I have is not working and I have made several other attempts without success. Maybe someone can help me. SELECT [PO Master].Code, Sum([PO Master].Allocation) AS SumOfAllocation FROM [PO Master] GROUP BY [PO Master].Code Where ((([PO Master].Code)="[code]")); |
Thread Tools | |
Display Modes | |
|
|