Show sum of supplier purchases in a form

Creating and using forms
Post Reply
PMarcotte
Posts: 8
Joined: Wed Jan 25, 2017 3:53 am

Show sum of supplier purchases in a form

Post by PMarcotte »

Hi,
I want to have a textbox showing the sum of purchases for individual suppliers. I want to show this textbox in a "Manage Supplier" form. The amounts purchased are in a "T_Item" table, linked to a "T_Supplier" table with an "ID" field. Here is what I have:

SELECT SUM( "T_Item"."Cost" ) FROM "T_Item"
INNER JOIN "T_Supplier" ON "T_Item"."ID_Supplier" = "T_Supplier"."ID"
GROUP BY "T_Supplier"."ID"

This query shows the sum of all the purchases from all suppliers in the table, not just the "active" one on the form, i.e. every supplier shows the same total instead of each his own. What am I missing?

Thank you
OpenOffice 4.1.3 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Show sum of supplier purchases in a form

Post by Villeroy »

Select both fields, the sum and the supplier ID.
Add a subform, bind it to the selection and link it to the parent form by the common ID field.
Add a currency field representing the sum field to the subform.
[Tutorial] Forms in OpenOffice.org Base
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply