access 365 SUM in table

[jnh999]

Source Table has several occurrences (rows) of CC CHASE JH, but QRY coding below only gives me SUM last row entry.

Example:
Source table row 1 has value of 750.00
Source table row 22 has value of 2,500.00
QRY result displays 2,500.00

Any help is greatly appreciated.

Chase: Sum((IIf(Left([Company],11)="CC Chase JH ",[AMOUNT])))

 

[samuria]

Chat gpt is great for this The code appears to be a Microsoft Access SQL statement, which uses the Sum and IIf functions to calculate a sum of values from a field named AMOUNT in a table or query. The IIf function is used to check if the left 11 characters of the Company field match the string "CC Chase JH ", and if so, the corresponding AMOUNT value is included in the sum.
However, there are a few potential issues with this code:

1. The condition in the IIf function checks for an exact match of the first 11 characters of the Company field. This means that if there are any leading or trailing spaces or other characters in the field, the condition will not be met even if the field actually contains "CC Chase JH ". To avoid this, you may want to use the Trim function to remove any leading or trailing spaces before checking the condition, like this: IIf(Left(Trim([Company]),11)="CC Chase JH ",[AMOUNT]).
   
2. The code only includes values from the AMOUNT field where the Company field matches the specified condition. If there are any null values in either field, they will be excluded from the sum. Depending on your data and your requirements, you may want to handle null values differently. For example, you could use the Nz function to replace null values with a default value, like this: Sum(IIf(Left(Trim([Company]),11)="CC Chase JH ",Nz([AMOUNT],0))).
   
3. The code assumes that the Company field contains the string "CC Chase JH " in a consistent format for all records. If there are any variations in the formatting or spelling of this string, the condition may not be met for some records that should be included in the sum. To handle this, you may want to consider using a more flexible pattern matching approach, such as the Like operator or regular expressions. For example: Sum(IIf([Company] Like "CC Chase JH *",[AMOUNT])) or Sum(IIf(RegexpLike([Company],"^CC Chase JH "),[AMOUNT])) (assuming your version of Access supports the RegexpLike function).