Show sum of supplier purchases in a form

Creating and using forms

Show sum of supplier purchases in a form

Postby PMarcotte » Sat Sep 23, 2017 5:51 am

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
PMarcotte
 
Posts: 8
Joined: Wed Jan 25, 2017 3:53 am

Re: Show sum of supplier purchases in a form

Postby Villeroy » Sat Sep 23, 2017 7:17 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests