Access formula

try this query

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

Is that a SQL query?

yes, that is an SQL statement ..

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

How do I enter this in as a sql statement? I need to do this in an access query

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

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

 

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

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.

Yes, it might look like:

TotalProduct:DSUM("Total_Amt_Ordered", "SYSADM_CUST_ORDER_LINE", "[Product_Code] = " & chr$(34) & [Product_Code] & chr$(34))

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

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.

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.

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;

thanks