Running sum with 2 tables

[dwatt]

Have a problem with using DSum function when two tables are linked together.

The tables are linked by the ID field. Tables are Table1 and Table2 with respective fieldnames (ID, A, C, D) and (ID, B). Datatypes are (number, text, number, number) and (Autonumber, number)

The following function creates syntax errors.

Expr1: Val(DSum("D","Table1","A & B = '" & [Table1].[A] & [Table2]. & "' and C <= " & [Table1].[C]))

This is the data similar to what I want to generate (which I can if all the fields are in one table).

A……….B…………..C……..D………Expr1
1……….1………..2002….25………..25
1……….1………..2003….12………..37
1……….1………..2004….14………..51
1……….2………..2002….10………..10
1……….2………..2003….20………..30
1……….2………..2004….30………..60
1……….1………..2005….12………..63

What is wrong with the syntax, do I have any missing operators…can you use this function with linked tables

Thanks in advance

 

[Mile-O]

You can't reference values in more than one table with this method.

If you can, bring these fields together into a query.

Your syntax must also follow this style

A = criteria for A And B = criteria for B

NOT

A & B = criteria for A & criteria for B

 

[dwatt]

Thanks for the reply.

Afraid I cannot bring these fields together.

Is there any other statement I might use like SQL. Have little experience with SQL

What I want to do is add up the fields in D with respect to A and B remaining the same and C changing. Only when A or B changes a new record is created wrt D..see table

Regards