Select Sum SQL help

[Billy B]

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)="

"));

 

[PieterLinden via AccessMonster.com]

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)="

"));[/QUOTE]

Use DSUM instead

 

[Marshall Barton]

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)="

"));[/QUOTE]


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)

 

[Duane Hookom]

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().