DSUM criteria prob (1 Viewer)

[mcw21j]

Hail and well met, friends!
I am having a problem with DSUM criteria. I am new to working with databases and thoroughly confused by the use of quotes, dblquotes, brackets, and parentheses which I believe is the issue I am grappling with in this situation. I think that the answer is simple but I have searched high and low and tried many, many iterations of the query below, without any luck. I am hopeful that you all can help me.

- I have a simple table (“TEST tblInvoices”) that I want to run DSUM on.

- My table has 21 records and several fields but I am concerned with only 2 of them… “CompName_FK” (number field) and “Charge” (which is currency field).

- Within the 21 records, I have 6 unique values for CompName_FK.

- I am looking to use DSUM to total up all of the Charge values, and then return a single record for each CompName, with the total Charge.

- The query I am running is below.

- It returns the same 21 records but in the “Total Costs” column, the value is the same for each of the 21 records. It has calculated the sum across the entire domain, returned the same value for each record AND it did not group my CompName_FK values as expected.

Total Costs: DSum("[TEST tblInvoices]![Charge]","TEST tblInvoices","[TEST tblInvoices]![CompName_FK] =" & "[TEST tblInvoices]![CompName_FK]")

Any advice is greatly appreciated.
Mark
Access 2010

 

[CJ_London]

first - best to avoid spaces in table and field names.

I presume what you have quoted is not the whole query which should look like this:

SELECT compname_FK, Sum(Charge) 
FROM [TEST tblInvoices] 
GROUP BY compname_FK

Square brackets are only required if you have spaces in your table or field names - although access will add them whether required or no anyway if you use the query builder

Round brackets are used in the same way as required for formulaes or functions (e.g. the sum function above) - typically used in criteria, Access will again add them whether required or not when using the query builder.

You uses dots '.' to separate table and field names e.g. [TEST tblInvoices].[Charge] - but bangs '!' to separate references to forms when referenced in a query e.g. [myform]![form]![mycontrol]

Quotations always have to be used with literal strings (e.g. 'smith') and are not used with numbers or dates.

Within a query you can use double or single quotes but if you are building the query in vba you have to use single quotes since double quotes are used to define the string - my recommendation is to get into the habit of using single quotes.

e.g. in VBA - mySqlStr="SELECT * FROM myTable where myName='Smith'"

 

[mcw21j]

ahhh, thanks so much. the code i posted was the entire code that i generated using the expression builder. but my question was also part of a larger query that i now have new knowledge to tackle, thanks to you. besides being a noob to Access, I am a noob to SQL and VB and basically, every other type of programming language but i was still able to figure out your code and incorporate it into my design.

thanks again!

 

[mcw21j]

also, many thanks for taking the time with the syntax descriptions. i very much appreciate it!