Solved Can Anyone correct the syntax

Local time
Tomorrow, 03:42
Joined
Aug 19, 2021
Messages
212
When I am trying to save the following SQL Query an error is appearing please check the screenshot.

SELECT bDebitVoucher.VoucherID,
bDebitVoucher.PaymentDate,
bDebitVoucher.AmountReceived,
bDebitVoucher.AmountPaid,
(Select Sum([AmountReceived]-[AmountPaid])From DebitVoucher WHERE
DebitVoucher.<=bDebitVoucher.Date) As Balance FROM
DebitVoucher As bDebitVoucher ORDER BY bDebitVoucher.Date;

1632169580074.png


Please help me to correct the statement.

Thank you
 
as G said, DebitVoucher.
what is the dot for, there's nothing else there
 
Addtionally, no space before your first FROM.

Then after you fix the syntax--doe you really have both a PaymentDate and Date field in DebitVoucher? And is that the right field to use in the WHERE?

Lastly, you shouldn't store Paid and Received in seperate fields. They should just go into a TransactionAmount field since you want them to be added together.
 
Addtionally, no space before your first FROM.

Then after you fix the syntax--doe you really have both a PaymentDate and Date field in DebitVoucher? And is that the right field to use in the WHERE?

Lastly, you shouldn't store Paid and Received in seperate fields. They should just go into a TransactionAmount field since you want them to be added together.
Hi plog thank you so much for your response.
I've deleted space before FROM.
No don't have Date field but I have PaymentDate field in DebitVoucher Table. Yes I want to use PaymentDate in WHERE.

Now my SQL statement is:
SELECT bDebitVoucher.VoucherID,
bDebitVoucher.PaymentDate,
bDebitVoucher.AmountReceived,
bDebitVoucher.AmountPaid,
(Select Sum([AmountReceived]-[AmountPaid])From DebitVoucher WHERE
DebitVoucher.PaymentDate<=bDebitVoucher.PaymentDate) As BalanceFROM
DebitVoucher As bDebitVoucher ORDER BY bDebitVoucher.PaymentDate;

ERROR WHEN I AM SAVING IT IS:
1632200926207.png


Please guide how to use AmountPaid and AmountReceived in statement to run correctly.
 
on the Inner SELECT, you add "space" between Sum(...) From.
 
Addtionally, no space before your first FROM.

Then after you fix the syntax--doe you really have both a PaymentDate and Date field in DebitVoucher? And is that the right field to use in the WHERE?

Lastly, you shouldn't store Paid and Received in seperate fields. They should just go into a TransactionAmount field since you want them to be added together.
Now Its:

Code:
SELECT bDebitVoucher.VoucherID, bDebitVoucher.PaymentDate, bDebitVoucher.AmountReceived, bDebitVoucher.AmountPaid, (Select Sum([AmountReceived]-[AmountPaid]) From DebitVoucher WHERE
DebitVoucher.PaymentDate<=bDebitVoucher.PaymentDate) AS Balance
FROM DebitVoucher AS bDebitVoucher
ORDER BY bDebitVoucher.PaymentDate;
It has been saved and running. But its not giving correct output for running balance.

1632229145841.png
 
Oh no, its giving you exactly what you asked for--the balance on the date of each record. On Jan 1 2021 the balance is -5500, because on that date you have 3 transactions that add up to -5500 so for each record with a date of Jan 1 2021 it produces the balance for that date (-5500).

My guess is you want the balance after each record. For that you need a better way to order your data than just date. So, how is one (or a computer) to know which of those 3 records on Jan 1, 2021 comes first? Which second and which third?

You need to make the SELECT statement that calculates better more specific when it comes to applying order to your records.
 
Oh no, its giving you exactly what you asked for--the balance on the date of each record. On Jan 1 2021 the balance is -5500, because on that date you have 3 transactions that add up to -5500 so for each record with a date of Jan 1 2021 it produces the balance for that date (-5500).

My guess is you want the balance after each record. For that you need a better way to order your data than just date. So, how is one (or a computer) to know which of those 3 records on Jan 1, 2021 comes first? Which second and which third?

You need to make the SELECT statement that calculates better more specific when it comes to applying order to your records.
Thank you very much for your guidance.
Can you tell me what should my statement?

I Want Something Like this:
1632233356632.png

But I am confused how to do it.
 
You want a running balance per record. To do that, you need a way to order the records in a way that makes sense TO THE COMPUTER. Are the voucher ID numbers always unique? Because you could order by PaymentDate AND by VoucherID. But note that your first entry (VNO-01) does not have a payment date so will sort differently than the other records.

Try changing the ORDER BY clause to ORDER BY bDebitVoucher.PaymentDate, bDebitVoucher.VoucherID ;

However, if you had used an autonumber (in increment mode rather than random mode) for the original records, it would also give you a viable ordering method. Not saying to add an autonumber, but if you had one it would have been a better choice. Probably NOT worth going back to add an autonumber now, though.

One final comment... if you ever have to make a voucher with ID VNO-100 your ordering will go out the window because that mixed alpha/numeric ID sorts as text, not as numbers. In that case, VNO-100 would appear after VNO-10 but before VNO-11, which might make things a bit unclear.
 
This is your current subquery that gets the balance:

Code:
(Select Sum([AmountReceived]-[AmountPaid])From DebitVoucher WHERE
DebitVoucher.PaymentDate<=bDebitVoucher.PaymentDate) As BalanceFROM

If VoucherID can be relied on to be the order you want your records to be in then you can replace the PaymentDate criteria with it:

Code:
(Select Sum([AmountReceived]-[AmountPaid])From DebitVoucher WHERE
DebitVoucher.VoucherID<=bDebitVoucher.VoucherID) As BalanceFROM

Now, if it can't be relied on to be in order then your technically screwed (but maybe not practically). Could a VoucherID be assigned out of Date/Time order? For example, could someone have missed an entry for last week, fix it by going in today and adding it? That way the PaymentDate was a week ago, but the VoucherID was assigned today?
 
This is your current subquery that gets the balance:

Code:
(Select Sum([AmountReceived]-[AmountPaid])From DebitVoucher WHERE
DebitVoucher.PaymentDate<=bDebitVoucher.PaymentDate) As BalanceFROM

If VoucherID can be relied on to be the order you want your records to be in then you can replace the PaymentDate criteria with it:

Code:
(Select Sum([AmountReceived]-[AmountPaid])From DebitVoucher WHERE
DebitVoucher.VoucherID<=bDebitVoucher.VoucherID) As BalanceFROM

Now, if it can't be relied on to be in order then your technically screwed (but maybe not practically). Could a VoucherID be assigned out of Date/Time order? For example, could someone have missed an entry for last week, fix it by going in today and adding it? That way the PaymentDate was a week ago, but the VoucherID was assigned today?
Usually we do the entry at the time of paying and receiving the amount. In some cases we do the entry before few days and some cases after few days. But VoucherID will always unique for each voucher we issue.
 
Unique isn't good enough, you want a way to uniquely order your data. PaymentDate is ordered by not unique, VoucherId is unique but not properly ordered.

A solution is to use both in the WHERE criteria of your subquery:

Date<=Date AND ID<=ID
 

Users who are viewing this thread

Back
Top Bottom