Forum Discussion
Probably Extremely Easy Question - Sum Column on one Table from Matching Rows on Other Table
I have a question today that I think most of you will find quite easy, but I'm really struggling with.
I have an Item table, and each row on the Item table of course represents one Item. Each item is identified by the No_ field.
| No_ | Description |
| A | Stock Item A |
| B | Sales Item B |
| C | Christmas Item C |
I have another table, purchase_lines, connected to it with a related field Item No_:
| Item No_ | Quantity |
| A | 2 |
| A | 3 |
| B | 6 |
| C | 7 |
| C | 3 |
What I need is a column on the first table that sums up matching rows on the second table.
Now your first instinct is going to be to tell me I can just drag the Quantity column into my report and it will sum up correctly there, and *you'd be correct*, but that's not what I need to do with this column. I'm doing some other complex stuff with other related tables and I really do need a calculated column on the Item table. A column that ends up looking like this:
| No_ | Description | QtySum |
| A | Stock Item A | 5 |
| B | Sales Item B | 6 |
| C | Christmas Item C | 10 |
I've tried Calculating and SumX-ing and I just can't figure out what to do. Please help.
4 Replies
Vvelarde I think that may have worked! I feel so stupid, as that's so simple and I've used those things countless times before. I was just summing it, or trying to do calculate(sum and then putting a filter in there. I was overcomplicating it. Thanks so much!
Anonymous yes and that works great in a 1:many relationship for referencing values on the 1: side of that relationship on the table with the :many side. But I'm doing the reverse, which is why I'm struggling.
