We help IT Professionals succeed at work.

Access formula

Keef400
Keef400 asked
on
I am trying to sort a report by the sum of field.

Scenario is as follows:

I have a field "Product_Code" within the table "SYSADM_CUST_ORDER_LINE"

I need the sum of "TOTAL_AMT_ORDERED" that is also within the table "SYSADM_CUST_ORDER_LINE" for all the same "Product_Code"

I want it as a new column in the query so I can sort by the SUM on a report.

Comment
Watch Question

Top Expert 2016

Commented:
try this query

select Product_Code, Sum(TOTAL_AMT_ORDERED)
from SYSADM_CUST_ORDER_LINE
group by Product_Code

Author

Commented:
Is that a SQL query?
Top Expert 2016

Commented:
yes, that is an SQL statement ..

Author

Commented:
Product_Code: Sum([TOTAL_AMT_ORDERED])

When I put this in the Field: line it states that it there is an extra ) in the query expression

Author

Commented:
How do I enter this in as a sql statement? I need to do this in an access query
Top Expert 2016

Commented:
create a new query
(do not select any table)
switch to SQL view

copy this

select Product_Code, Sum(TOTAL_AMT_ORDERED)
from SYSADM_CUST_ORDER_LINE
group by Product_Code


and paste to the SQL view window of your new query

Author

Commented:
I see, yes that query works, but doesnt include everything else i've written. Which is why I need it in a access query not the SQL query.

I need it to filtered by a few other criterias

 example
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Use the query that Capricorn1 gave you as the source for the main report (get rid of the Order By clause because it isn't even considered in the report).

Then embedd your current report in it as a subreport, and link the two on the Product_Code table.

Then, you can sort the main report by the Sum

Author

Commented:
Cant I get it summed at the end of the screen shot above? I need to be able to call on a date range. I honestly dont understand your steps fyed. I am not an advanced acess user. It would seem to me that this can just be added in the query that I have setup already.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Yes, it might look like:

TotalProduct:DSUM("Total_Amt_Ordered", "SYSADM_CUST_ORDER_LINE", "[Product_Code] = " & chr$(34) & [Product_Code] & chr$(34))
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
But this will take a lot longer to run than the report/subreport method.

Author

Commented:
The last one you sent is an invalid syntax error.

I guess maybe can you give me a direction to look into figuring out how to do a report/subreport. I didnt see any options to include a subreport.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
1.  Post the entire SQL statement you are using (from the image you posted above).  I'll see what you have that is causing the syntax error.

2.  Report/subreport.

a.  Create a new report, use Capricorn1's query (below) as the RecordSource for the report.

SELECT Product_Code, Sum(TOTAL_AMT_ORDERED) as TotalProducts
FROM SYSADM_CUST_ORDER_LINE GROUP BY Product_Code

b.  On the Design tab in the ribbon, select the Group & Sort tool, then at the bottom of the page, select Add a Group and select the Product_Code as your group.

c.  In the Report design, insert two textbox controls (txt_Product_Code and txt_Total_Products) and assign their control sources to the appropriate fields from the query.

d.  Click on your current report (in the navigation pane) and drag it over into the Detail section of the new report.  Right click on the border of the subreport and display the properties dialog.  Go to the data tab and click on the elipse to the right of the Link Master Fields property.  This will open another dialog box where you select the fields to be used to relate the subreport to the report.  It will probably come up with [Product_Code] in each column, but if not, select that option and save those changes.

Now, when you view your report, the product code and total product will be listed in the Product group header, and the data in your original report that relates to that product will be displayed via the subreport.

Author

Commented:
SELECT SYSADM_CUSTOMER_ORDER.STATUS, SYSADM_CUST_ORDER_LINE.CUST_ORDER_ID, SYSADM_CUST_ORDER_LINE.PRODUCT_CODE, IIf(IsNull(SYSADM_CUST_ORDER_LINE!PART_ID),SYSADM_CUST_ORDER_LINE!SERVICE_CHARGE_ID,SYSADM_CUST_ORDER_LINE!PART_ID) AS [Part ID], SYSADM_CUSTOMER_ORDER.ORDER_DATE, SYSADM_CUST_ORDER_LINE.ORDER_QTY, SYSADM_CUST_ORDER_LINE.PROMISE_DATE, SYSADM_CUST_ORDER_LINE.TOTAL_AMT_ORDERED, DSum("Total_Amt_Ordered","SYSADM_CUST_ORDER_LINE","[Product_Code] = " & Chr$(34) & [Product_Code] & Chr$(34)) AS TotalProduct
FROM (SYSADM_CUST_ORDER_LINE INNER JOIN SYSADM_CUSTOMER_ORDER ON SYSADM_CUST_ORDER_LINE.CUST_ORDER_ID = SYSADM_CUSTOMER_ORDER.ID) INNER JOIN SYSADM_CUSTOMER ON SYSADM_CUSTOMER_ORDER.CUSTOMER_ID = SYSADM_CUSTOMER.ID
WHERE (((SYSADM_CUST_ORDER_LINE.CUST_ORDER_ID) Not Like "9***") AND ((SYSADM_CUST_ORDER_LINE.PRODUCT_CODE) In ("210","210-02","211","211-01","211-02","212","213","213-01","213-02","214","215","216","220","221","226","227","320","321","326","310","327")) AND ((SYSADM_CUSTOMER_ORDER.ORDER_DATE) Between [Enter start date:] And [Enter end date:]))
ORDER BY SYSADM_CUST_ORDER_LINE.CUST_ORDER_ID;
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
1.  Does the query run properly without the TotalProduct computed column?  It looks like there is a syntax error in the following line:

IIf(IsNull(SYSADM_CUST_ORDER_LINE!PART_ID),SYSADM_CUST_ORDER_LINE!SERVICE_CHARGE_ID,SYSADM_CUST_ORDER_LINE!PART_ID) AS [Part ID]

I believe you should replace all of the bangs (!) with dots (.) and it should read read:

IIf(IsNull(SYSADM_CUST_ORDER_LINE.PART_ID),SYSADM_CUST_ORDER_LINE.SERVICE_CHARGE_ID, SYSADM_CUST_ORDER_LINE.PART_ID) AS [Part ID]

2.  I would seriously consider defining the [Enter start date:] and [Enter end date:] parameters.  Otherwise these values may be confused as strings, not dates.  To do this, open the query in design view, then right click in the grey area and select the "Parameters" option from the popup.  Then enter [Enter start date:] in the left column and Date as the data type, and do the same for the end date

3.  The syntax for the DSUM line looks correct, but you might want to try changing it to:

DSum("Total_Amt_Ordered","SYSADM_CUST_ORDER_LINE","[Product_Code] = " & Chr$(34) & SYSADM_CUST_ORDER_LINE.[Product_Code] & Chr$(34)) AS TotalProduct

or

DSum("Total_Amt_Ordered","SYSADM_CUST_ORDER_LINE","[Product_Code] = """ & SYSADM_CUST_ORDER_LINE.[Product_Code] & """") AS TotalProduct

Author

Commented:
thanks