1 Topic by firstpharaon (edited by firstpharaon 2021-07-27 12:08:55)

  • Registered: 2021-07-27
  • Posts: 5

Topic: Sqlquery calculated field summation error

i have invoice table with calculated field that calculate amount multiple price to give total value and that for each customer
with this query "(select productP from products where invoices.id_products = products.id) * invoices.quantity "

i mad new table called dept where i want to show how much total invoice had one customer bought , but i get error with query
"select total from invoices where dept.id_invoices = invoices.id"

also made column settings formula = sum in tablegrid

i upload the error message

Post's attachments

Attachment icon Capture.PNG 3.87 kb, 162 downloads since 2021-07-27 

  • Registered: 2014-06-25
  • Posts: 863

Re: Sqlquery calculated field summation error

According to the screenshot, there are too many wrong statements. If you want to write a sub-select, the second select statement should be in parenthesis and the "AS column_names" must be right behind the column name you select. Without your project or database description, it is a bit difficult to give you the right answer.

  • Registered: 2021-07-27
  • Posts: 5

Re: Sqlquery calculated field summation error

i had to upload my project to zippyshare cause of The selected file was too large to upload
and uploaded file with it's link cause no links are allowed
the issue is in table dept

Post's attachments

Attachment icon link.txt 43 b, 327 downloads since 2021-07-27 

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Sqlquery calculated field summation error

Hi. The error is that you are calling a calculated field from a calculated field. Rewrite the query or create a new one by calculating internally without calling the calculated field.

5 Reply by firstpharaon (edited by firstpharaon 2021-08-09 11:48:00)

  • Registered: 2021-07-27
  • Posts: 5

Re: Sqlquery calculated field summation error

How can that be done, since invoice total value includes many products values * it's prices
I don't have one field to be linked to

6 Reply by derek (edited by derek 2021-08-09 15:55:13)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,672

Re: Sqlquery calculated field summation error

It's difficult to offer any help without seeing your project (your link won't work for many of us based in Europe as zippyshare is blocked).
Delete the .exe file from your application and upload the remaining files as a .zip file.
Then explain as fully as possible exactly what you are trying to do.
Derek.

7 Reply by sparrow (edited by sparrow 2021-08-10 13:16:55)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Sqlquery calculated field summation error

Hi firstpharaon,
Look at this
Changed calculated field invoice.total and some changes in the settings of the table where there was an error.

Post's attachments

Attachment icon inv2-ed.zip 336.82 kb, 346 downloads since 2021-08-10 

  • Registered: 2021-07-27
  • Posts: 5

Re: Sqlquery calculated field summation error

derek wrote:

It's difficult to offer any help without seeing your project (your link won't work for many of us based in Europe as zippyshare is blocked).
Delete the .exe file from your application and upload the remaining files as a .zip file.
Then explain as fully as possible exactly what you are trying to do.
Derek.

i'm not sure exactly what i'm doing wrong
i'm making an invoice application, to track customers purchases, payments and debets
i made products table, customer table and invoice detail table
so what i want is
when i create new invoice to select products and it's prices multiplication in it's quantities, for one invoice contain many products and to be linked to customer name and date.

i'm still working on it but the issue is when i try to make new invoice the DGV (data grid view) shows all the previous records while i want to make new invoice without old records

Post's attachments

Attachment icon Invoice.rar 295.63 kb, 307 downloads since 2021-08-13 

9 Reply by sparrow (edited by sparrow 2021-08-15 04:25:39)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Sqlquery calculated field summation error

Hi,
Now your queries are referring to a table "invoices" that you do not have .
Let's try to do this.
You will make your program to the maximum.
Now you have everything separately in the program.
Invoice form in its infancy).
Complete the invoice form without the total quantity and amount, but with all the fields that are required. Which order, which client, which products, quantity, how they are displayed ... perhaps you will understand how to calculate the cost by quantity.
If not, then let's talk.

  • Registered: 2021-07-27
  • Posts: 5

Re: Sqlquery calculated field summation error

well i done this so far
what i have in mind is in one invoice i can add many products with it's price ad quantity showing it's total,
and for one customer to show how many invoices the customer bought with the total of each invoice.
but that's seem not working in the saving way for the tables or what am i doing wrong , how to make the grid to show only new items of products for new invoice and not all the records of the table

Post's attachments

Attachment icon Invoice.rar 295.78 kb, 331 downloads since 2021-08-21 

11 Reply by domebil (edited by domebil 2021-08-21 06:09:58)

  • Registered: 2014-11-13
  • Posts: 370

Re: Sqlquery calculated field summation error

see now

Post's attachments

Attachment icon Invoice ok.rar 296.63 kb, 388 downloads since 2021-08-21 

Domebil

domebil's Website