Query for calculated field?

[Roberto_Giussani]

I need to urgently resolve a problem.

I have a database with these 3 tables CUSTOMER - LESSONS RESERVATION BOOKING - PAYMENT.

I need the residual credit to be calculated in the AMOUNT OUTSTANDING field of the CUSTOMER table,

on the basis of the lessons booked or performed and on the basis of the payments made.

THANK YOU !

 

[June7]

Remaining balance should normally be calculated when needed, not saved to table. Review http://allenbrowne.com/AppInventory.html

 

[MajP]

Get rid of the amount outstanding in your table. Avoid calculated fields. In the query you show change it to a Left outer join so you get all custumers, all customer lessons, and all lesson payments.
Then do a "Group By" query. Group on CustomerID, and take sum of Lesson Price, and sum of payment. Save that query. Now join that to the customer table in a second query a and make a calculated column (AmountDue: [SumofLesson_Price] - [SumPayment_Amount])

If not familiar here is a "group by" example

 

[Roberto_Giussani]

Get rid of the amount outstanding in your table. Avoid calculated fields. In the query you show change it to a Left outer join so you get all custumers, all customer lessons, and all lesson payments.
Then do a "Group By" query. Group on CustomerID, and take sum of Lesson Price, and sum of payment. Save that query. Now join that to the customer table in a second query a and make a calculated column (AmountDue: [SumofLesson_Price] - [SumPayment_Amount])

If not familiar here is a "group by" example

thank you for your reply ... but may be I make a mistake ... Query2 don't work

 

[MajP]

It looks to me like like you did a Group By on the three fields instead of summing the other two fields
CustomerID: Group BY
LessonPrice: SUM
Payment_Amount: SUM

 

[Roberto_Giussani]

It looks to me like like you did a Group By on the three fields instead of summing the other two fields
CustomerID: Group BY
LessonPrice: SUM
Payment_Amount: SUM

View attachment 107228

ok the first query work correctly ... but second one no ...

Now join that to the customer table in a second query a and make a calculated column (AmountDue: [SumofLesson_Price] -[SumPayment_Amount])

Sorry but I am principiant ... thank you from Italy

 

[MajP]

In the first query you did your group by and you should have got something like this with a row for each customer

Customer_ID	SumOfLesson_Price	SumOfPayment_Amount
1	100	50
2	200	200
3	300

Use the above in a regular query linking customer_ID to table Customer. That is just a regular query not another aggregate query. You show a second aggregate.

 

[Roberto_Giussani]

Nella prima query hai fatto il tuo gruppo e avresti dovuto ottenere qualcosa del genere con una riga per ogni cliente

Identificativo del cliente	SumOfLesson_Price	SommaPagamento_Importo
1	100	50
2	200	200
3	300

Utilizzare quanto sopra in una normale query che collega customer_ID alla tabella Customer. Questa è solo una query normale, non un'altra query aggregata. Mostri un secondo aggregato.

Now work ... but how I can find or copy the "Amount outstanding" in the field of the customer table ?

 

[MajP]

In this query add a calculated field.
SumOutstanding: [SumOfLesson_Price] - [SommaPagamento_Importo]

 

[Roberto_Giussani]

ok work correctly but now I want put the "Amount outstanding" in the field of the customer table ... how I can do ? I think that this is my last request

 

[MajP]

You should not do that. If you put a calculated field in a table, then it is only correct at that point of time. Every time you edit the database records you would have to ensure to do an update query and push this calculated value into the table. Why do you need it in the table if you can show this in a report, form, or query dynamically?
If you had to do it you would now do an update query based on this final query.

 

[Roberto_Giussani]

Non dovresti farlo. Se inserisci un campo calcolato in una tabella, allora è corretto solo in quel momento. Ogni volta che modifichi i record del database, dovresti assicurarti di eseguire una query di aggiornamento e inserire questo valore calcolato nella tabella. Perché ne hai bisogno nella tabella se puoi mostrarlo dinamicamente in un report, modulo o query?
Se dovessi farlo, ora faresti una query di aggiornamento basata su questa query finale.

osservazione corretta...grazie...creerò un pulsante nella scheda cliente che mi darà il valore aggiornato...grazie mille

 

[MajP]

Saving information that can be quickly calculated is always a bad idea. It is very easy to get the stored data out of synch with the "real" current data. Sometimes it if very long and resource intensive do do calculations. In that case prior to reporting you may want to push values into a temp table, but those values are only current at the point you run the report.

 

[Roberto_Giussani]

one last clarification ... if I put a button in the customer form that executes the query, what should I enter to get the amount of the customer concerned as a result?

 

[June7]

Cannot perform an UPDATE action SQL when an aggregate query is involved.

A SELECT query is not 'executed' - you pull data from it same as pulling from a table. Bind form or report to query or use domain aggregate function (DLookup, DCount, DSum, etc) to pull specific data into textbox.

 

[Roberto_Giussani]

Cannot perform an UPDATE action SQL when an aggregate query is involved.

A SELECT query is not 'executed' - you pull data from it same as pulling from a table. Bind form or report to query or use domain aggregate function (DLookup, DCount, DSum, etc) to pull specific data into textbox.

Thank you for your reply. Sorry but I am principiant and don't understand what I have to do. Please con you send me a sample ? THANKS again

 

[June7]

What do you not understand about suggested options? How to bind form/report to query or how to use domain aggregate function?

If you want example specific to your data and situation, provide your db for analysis.

 

[MajP]

I kind of screwed up my previous suggestion. If you do it that way and you have multiple payments per lesson then you are not going to get the correct answer. See if this video is helpful. Also select the watch on youtube if not the first 10 or so seconds are blurry.

 

[MajP]

FYI, the final query is not updateable so if you want to edit your customer data and include this you cannot. You could make this a subform on your customer form and link by customer id. Then you could show this data linked to the first customer in the footer of the form.

qryBalance qryBalance

CustomerID	Last Name	First Name	SumOfLessonPrice	SumOfPayment_Amount	Balance
1​	Davolio	Nancy	$180.00​	$150.00​	$30.00​