[Solved] Adding totals to the form

[Zazu99]

I have searched the forum and whilst there are many posts about calculated fields none seem to fit my particular requirement. I have a data entry form for orders. It has a subform where the order details are entered in a grid like:

SKU ITEM QTY PRICE EXTEND
1 Item 1 1 $5.00 $5.00
2 Item 2 3 $11.00 $33.00
3 Item 3 2 $10.00 $20.00

The data entry is based on a query. The value in the EXTEND field is calculated in the query "price"*"Qty"

I would now like to sum the extend column so I get $58.00. I have been trying to do this with a Formatted Field in another subform. (A sub form of the first subform). I have tried a variety of calculations but none work. Note I don't need to save the result - just see it. I have also tried to base the second subform itself on a query where I group the order id and sum the EXTEND column. The query returns the correct result but doesnt display on the form

[Arineckaig]

I have also tried to base the second subform itself on a query where I group the order id and sum the EXTEND column. The query returns the correct result but doesnt display on the form

The Base GUI will tend to update a sub-form automatically only if the record pointer in the main form moves to a new record. Thus, updating a sub-form sourced from an aggregate query often requires the addition of a button form control in the sub-form whose "Action" property is set to 'Refresh form'.

[Zazu99]

Thanks. I already have a button on the sub form that refreshes the form - but to no avail

[Arineckaig]

There would appear to be some simple error in the sub-form. There could be a number of causes: the quickest route to finding the error would be to post a copy of your Base file with any sensitive data removed as opposed to trying to guess what the problem might be.

Meantime the attached crude example may offer some pointers:

[Arineckaig]

The revised uploaded demo file may perhaps be more useful in that the added explanatory note includes particular reference to the updating a sub-form that has empty linking fields with its parent form.

[Zazu99]

For some reason this is not working for me. Attached is a pic of the form which is a derivative of the Invoice form elsewhere in the Forum.

The main Form contains all the customer info down to and including Applied to Date. The source is the Customers Table

Subform 1 (the drop downs and Next Item) is sourced from our Products table which includes a lot of other info including price

Subform 2 is the grid including the push button - Add item...- and the yellow nav bar. The data content based on sql - SELECT "SOLD".*, "Products"."ITEM_ID", "Products"."sku", "Products"."price", "Products"."post_title", "Qty" * "price" AS "Extend" FROM "SOLD", "Products" WHERE "SOLD"."ITEM_ID" = "Products"."ITEM_ID"

SOLD is a table containing Invoice#, Item_ID and Qty

Subforms 4 and 5 deal with Shipping - which is ok

Subform 3 is problematic. It has the Total field and pushbutton Get Total. The pushbutton "Get Total" refreshes the form. No matter what calculations I try in the Total field don't get any result

[Arineckaig]

From the original post:

I have also tried to base the second subform itself on a query where I group the order id and sum the EXTEND column. The query returns the correct result but doesnt display on the form

If Subform 3 is sourced from SQL that works as a simple query but fails in a subform, it is likely the fault lies in the properties of the subform. As a starting point it would be helpful to see:

1. the name of the form that is the parent form for Subform 3;
2. the SQL used as the Query on which Subform 3 is based;
3. the content of the Link Master and Link Slave fields set for Subform 3;
4. the name of the field to which the Total form control in Subform 3 is bound.

[Zazu99]

Thank you for your assistance. Answers:

Subform 2 is the parent of Subform 3

The basis of subform 3:
SELECT "ID", SUM( "Extended" ) FROM "ExtendedCalc" GROUP BY "ID"

This is the sql of "ExtendedCalc" query:
SELECT "Products"."ITEM_ID", "Products"."sku", "Products"."post_title", "Products"."price", "SOLD"."Qty", "price" * "Qty" AS "Extended", "INVOICE"."ID", "INVOICE"."CUSTOMER_ID" FROM "Products", "SOLD", "INVOICE" WHERE "Products"."ITEM_ID" = "SOLD"."ITEM_ID" AND "SOLD"."INVOICE_ID" = "INVOICE"."ID"

The Link fields are empty

The Data Field has this entry:
SUM(SYSTEM_SUBQUERY."Extended")

[Arineckaig]

I lack the skills or experience to have confidence in offering suggestions that cannot be tested without a copy of the database. At first sight, however, the following points might be worth looking at:

1. The query for Subform3 appears able to produce a multiple row-set with one row for each "ID" ("INVOICE"."ID"). If so, it is necessary to filter the result by including the respective "ID" fields in each of the Linking fields for Subform3. The linking fields should ensure that only a single but relevant row-set is supplied to Subform3.

2. Rather than using SUM(SYSTEM_SUBQUERY."Extended") as the bound Data Field for the formatted field in Subform3, it might be simpler to use an alias for that field. For example, the source query could be:

Code: Select all

SELECT "ID", SUM( "Extended" ) AS "Total" FROM  ExtendedCalc" GROUP BY "ID"

and the formatted field could then simply be bound to the "Total" field in the source query.

Please come back if these suggestions are of no help, and hopefully in the meantime somebody more skilled than I will have detected the source of the error.

[Zazu99]

Thank you for all your assistance. Taking on board your comments/suggestions I decided to take a fresh look at what I was doing. I have now achieved the desired outcome.

I created a new subform of the main form and based the data content on:

SELECT "SOLD"."INVOICE_ID", SUM( "Qty" * "price" ) AS "Total" FROM "Products", "SOLD" WHERE "Products"."ITEM_ID" = "SOLD"."ITEM_ID" GROUP BY "SOLD"."INVOICE_ID"

I used a Formatted Field, the data Field based on "Total"

Link Master is "ID"
Link slave is "INVOICE_ID"

Once again thank you