Solved Can Anyone correct the syntax

[Khurramshaikh47]

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;

Please help me to correct the statement.

Thank you

 

[Gasman]

Missing field for debitvoucher?
Before the <

 

[Isaac]

as G said, DebitVoucher.
what is the dot for, there's nothing else there

 

[plog]

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.

 

[Khurramshaikh47]

Missing field for debitvoucher?
Before the <

Thank you

 

[Khurramshaikh47]

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:

Please guide how to use AmountPaid and AmountReceived in statement to run correctly.

 

[Khurramshaikh47]

as G said, DebitVoucher.
what is the dot for, there's nothing else there

Thank you I've correct it.

 

[arnelgp]

on the Inner SELECT, you add "space" between Sum(...) From.

 

[Gasman]

AS Balance FROM

Put spaces between ALL!! the words

 

[Khurramshaikh47]

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:

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.

 

[plog]

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.

 

[Khurramshaikh47]

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:

But I am confused how to do it.

 

[The_Doc_Man]

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.

 

[plog]

This is your current subquery that gets the balance:

(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:

(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?

 

[Khurramshaikh47]

This is your current subquery that gets the balance:

(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:

(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.

 

[plog]

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

 

[Khurramshaikh47]

Thank you very much to all of you. Its Done now! Special thanks to plog!