Summary not working

[Slobey]

Hi everyone,

I put this in here because I thought it was a relational problem. I have a solution that has a job table (job) and a job line item table (JLI). The JLI table is made up of unit codes and quantities for the job. I want a field that will keep a running total for the units on a job. I need this to be in a JLI field. I created a self join relationship for the JLI table relating the job# to Job# and Unit code to unit code. this does not work. It gives me the summary of all units in the job, not just the unit for that JLI record. Meaning all records in the JLI with the same Job number have the same summary value. like the example below. Each line represents a record in the JLI

Job Location Unit Qty Sum

A 1 A1 1 9

A 2 A1 2 9

A 3 D4 1 9

A 2 A6 5 9

B A1 1 3

B B3 1 3

B D4 1 3

What I need it to say is the following:

Job Location Unit Qty Sum

A 1 A1 1 3

A 2 A1 2 3

A 3 D4 1 1

A 2 A6 5 5

B A1 1 3

B B3 1 3

B D4 1 3

I hope this makes sense and I assume it is something simple that my fried brain isn't seeing. Any help would be greatly appreciated.

Thanks

Mike