So hard that I don't know what it is called...

[tommygrayson]

Hi guys,

I am such a noob when it comes to Accounting.

Guys, what do you call this kind of computation:

Loaned Amount Amount Left

100 900
200 700
300 400

How do you do that in a query? Is it possible to do it in SQL or I would have to revise my ****** program.

[salvelinus]

I call it subtraction.
You can do subtraction in SQL, but you're missing a starting amount - presumably 1000 in your example.

Balance Loaned
1000 0
900 100
700 200

[szlamany]

So we don't waste all our time - tell us what the name of your table is and what fields are in it.

There must be come kind of loan amount field.

And I'm guessing a field for the date of the loan.

[tommygrayson]

So we don't waste all our time - tell us what the name of your table is and what fields are in it.

There must be come kind of loan amount field.

And I'm guessing a field for the date of the loan.

Ok. You guys are correct. After going through some research, it is a subtraction but in a running total form.


Here is the thing.

Table - Loans

Fields:

Loan

There is a loan amount field but the balance is a result for the running total. There are other fields like dates, control numbers and stuff but I don't want to clutter it all here. I just want a running total.

Thanks for your replies.

[tommygrayson]

I call it subtraction.
You can do subtraction in SQL, but you're missing a starting amount - presumably 1000 in your example.

Balance Loaned
1000 0
900 100
700 200

Yes that is correct but the beginning balance is only a constant declared by the program. It is also not saved in the database.

Could you post the SQL query for that?

[szlamany]

But you have to clutter us here - as those dates would be exactly what would be used by me to build you a query - with a "running total sub-query" that ran your figures...

[salvelinus]

You've got one field in your table? You need a better table. This doesn't sound like a very viable project (sorry if I'm misunderstanding something).
Anyway, what data does the lonely field hold? The amount loaned? The running balance?
The general form of the SQL is:

SELECT (field1 - field2) AS SomeValue FROM Loans

[tommygrayson]

But you have to clutter us here - as those dates would be exactly what would be used by me to build you a query - with a "running total sub-query" that ran your figures...

Sorry for the late reply. I am from a very far and different time zone. Hope you understand.

Ok. point taken.

Here it is.


Table: - Loans

Fields:

TransNo - Primary Key
EmpNo
MedName
MedCode
GenName
GenCode
Dosage
PerscriptPeriod
RMQty
UnitCost
ORNo
ORDate
Loan

The date for the transaction is ORDate. I also use this for the report and the Loan field. I group the report by using MONTH(ORDate) = @ORMonth and YEAR(ORDate) = @ORYear. The problem is that there is no Balance in the table and I will have to revise the program if I can't generate a running total to the query.

The format of the report is:

     Medicine Loan for the Month of ___________                Loanable Amount 3,000.00

          OR Number              OR Date          Amount Loaned         Balance Left
          [ORNo]                   [ORDate]         [Loan]                     No field

[salvelinus]

You need to rethink your table design. Balance Left should probably not be a field in a table, but a calculated amount. So far, so good.
But Loanable Amount should be a field. And as szlamany says, if you want a running total you'll need some kind of date field, even if only a month.
All this data shouldn't be in just one table, but it's late & I'm too tired to break it out right now. Basically, loan data should be separate from medical data. Use foreign keys to join info for queries where needed.

[tommygrayson]

You need to rethink your table design. Balance Left should probably not be a field in a table, but a calculated amount. So far, so good.
But Loanable Amount should be a field. And as szlamany says, if you want a running total you'll need some kind of date field, even if only a month.
All this data shouldn't be in just one table, but it's late & I'm too tired to break it out right now. Basically, loan data should be separate from medical data. Use foreign keys to join info for queries where needed.

Don't worry man, got it covered. Already thought of a table design for medical data with foreign keys and relationships.

Loanable Amount should be a field. That is true and it is already been planned to be included in the Misc Table where all constants are declared there.

There is a date field. The OR Date column in the report is my date field.

Just needed the query for the running total.

Thanks in advance for all your inputs on this. Too bad, the table I showed is not normalized enough so there are a lot of concered issues you've posted.

You guys are great.

[szlamany]

This query should work:

Select L1.ORNo, L1.ORDate, L1.Loan
       ,3000-(Select Sum(L2.Loan) From Loans L2
                   Where L2.ORDate<=L1.ORDate
                       and L2.xyz=L1.xyz)
       From Loans L1

The "xyz" part is from my confusion over whether there is several loans going on at one time here - is it loans for an EmpNo?

At any rate - the basic concept is to have a sub-query that "totals" all the loans for that date going backwards - and subtract that from 3000.

Obviously the 3000 should not be a constant but a field from a misc table that's joined in somehow.

[tommygrayson]

This query should work:

Select L1.ORNo, L1.ORDate, L1.Loan
       ,3000-(Select Sum(L2.Loan) From Loans L2
                   Where L2.ORDate<=L1.ORDate
                       and L2.xyz=L1.xyz)
       From Loans L1

The "xyz" part is from my confusion over whether there is several loans going on at one time here - is it loans for an EmpNo?

At any rate - the basic concept is to have a sub-query that "totals" all the loans for that date going backwards - and subtract that from 3000.

Obviously the 3000 should not be a constant but a field from a misc table that's joined in somehow.

OMG!!!!!

That's it. That's so cool.

I thought you guys are going to post a friggin' Fetch Cursor yada yada yada that my brain could not understand.

I never thought that a subquery could do that.

That my friend is the most simple yet effective and a memory saver.

Anyway, if you have any questions about ASP, ASP.NET, IIS just PM me. I usually do those all the time.

[szlamany]

OMG!!!!!

That's it. That's so cool.

...I never thought that a subquery could do that.

That my friend is the most simple yet effective and a memory saver.

...

I spend 120% of my day writing queries - it's good and it's bad

I'm interested in how to make my app/db usable without the VB client - so maybe we can talk about ASP and such...

Where are you located?

[tommygrayson]

I spend 120% of my day writing queries - it's good and it's bad

I'm interested in how to make my app/db usable without the VB client - so maybe we can talk about ASP and such...

Where are you located?

Ah. The trend of the web apps.

My permanent residence is now in San Francisco.

But I travel a lot today doing errands for my uncle.