My name is Muhammad irfan ansari, from Pakistan. I am working on an Acc

By irfanansari85 ·
My name is Muhammad irfan ansari, from Pakistan.
I am working on an Account maintain software.
This had some tables and store proc and views for crystal report.
I am making this software for a shop where some costumer comes and buy some things, and then they paid some money and some money not given by them, they said I will pay after some days.
Original table or records are Bellow
Namee Buy _date Bought _some _cloth paid _money remaining _balance
Jon 10/08/2010 30000 10000 20000
Ali 23/08/2010 10000 2000 8000
Ali 14/08/2010 20000 12000 8000
Jon 8/08/2010 50000 20000 30000
Salvia 30/08/2010 100000 50000 50000
Salvia 19/08/2010 20000 20000 0
Ali 10/08/2010 10000 10000 0

And I want to record like this. Mean when I select one particular record so it should be like this as bellow.

Namee Buy_ date Bought _some_ cloth paid _money Remaining_ balance
Jon 10/08/2010 30000 10000 20000
Jon 11/08/2010 50000 20000 30000

Total reaming balance

Please tell me how it possible?
I tried it as in SQL server query.
Select name,buy_date,sum(Remaining _balance) from table1
where name=’jon’
Group by nmaee,buy_date
When I tried this so result as bellow, see

Namee Buy_ date Bought _some_ cloth paid _money Remaining_ balance
Jon 10/08/2010 30000 10000 20000
Jon 11/08/2010 50000 20000 30000

Not give total as this is
Namee Buy_ date Bought _some_ cloth paid _money Remaining_ balance
Jon 10/08/2010 30000 10000 20000
Jon 11/08/2010 50000 20000 30000

Total reaming balance

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Buy yourself an accounting system

by drowningnotwaving In reply to My name is Muhammad irfan ...

Then you bill the money, collect the money, pay your tax, put it in the bank, reconcile your bank account, produce your monthly account statements, hide some cash from you wife, spend the money, get drunk and have a hangover on Saturday morning.

And you never have to worry about Reaming Your Balance ever again.

You will probably find by searching on the internet you can download a simple, free accounting package that works in Pakistan.

Or use a spreadsheet like 200,000,000 small businesses around the world. Much easier than SQL.

Collapse -

I see

by santeewelding In reply to Buy yourself an accountin ...

You grow tired of this.

How tired?

Collapse -

No Santee

by drowningnotwaving In reply to I see

Just up to my armpits in biz, at present. Available time is less. Like you, but obviously from different directions, I am more interested in cause rather than symptom. Not so much to comment upon lately.

One thing I can be quite specific upon is that anyone who chooses SQL to develop their own basic business transactional solution, have rockee in headee.

It's a laff! :)

Collapse -

I agree with that bit

by Tony Hopkinson In reply to No Santee

unless it's extraordinarily simple and never ever ever going to change.

There again a document / object based solution isn't for the faint hearted either.

Unfortunately far too many confuse a database table with a rectangular collection of cells....

And besides I'd want more than a thumb for the book length answers to this sort of question...

Money would be good...

Collapse -

There also may be some extenuating circumstance

by drowningnotwaving In reply to I agree with that bit

Perhaps this is an assignment for a SQL class. Perhaps Muhammad is an 8 year old prodigy, working on his write-vs-buy thesis.

One suspects that at whatever end of the pendulum, hard currency may be difficult to come by!

I promise to split mine with you, Tony. Roolly troolly.


Collapse -

Or he's guaranteeing potential future income

by Tony Hopkinson In reply to There also may be some ex ...

from a maintenance contract....

He's doing well on that front.

I'd be adding zeros to the quote after a quick glance at the sql...

Collapse -

You touch upon

by santeewelding In reply to No Santee

One of the prime, original reasons I began investigating this place and its people (like yourself): how can I turn all this to business advantage.

I discovered early on...zilch. So, I persist in my analog, antiquated ways making money. Speaking of which, I am experiencing spells of business lately, too. Cuts into my time here as it does yours. I hope that "up to [the] armpits" is remunerative?

It has been for me. Almost like the old days. The bonus being, still time to yammer here.

Collapse -

Activity is up - very much so, indeed

by drowningnotwaving In reply to You touch upon

... and I hope this is the harbinger of filthy lucre.

Decisions are taking longer, with little impetus for the client to meet "deadlines".

Funny world - "Old" systems aren't really that old. They work. They integrate. They're used. They work on modern platforms with strong database backends. Why change?

Clients are getting wiser on the cost impact of the New. Realising that measuring a benefit for something that their existing system can perform, isn't really a benefit at all. Realising that the smaller, incremental benefits of modern solutions are actually very expensive and require acute validation.

There is a huge cost benefit of ignorance: I know your system can do it, but you don't know. Is it immoral to present an alternative solution when someone wants to change and wants to spend??

But for all of that, the list of people and companies that wish to spend money on these elemental increments continues to grow. We may or may not be doing a good job, but the message is still getting out.

It's my goal in life one day to be a Client. Well, okay, one of the lesser business hours only goals when you put it like that ....

Was laffing today with gents over lunch - 26 years ago #1 requirement was "better business reporting". Hasn't really cnanged. Don't think it will for next 20 years 'til I don't give a fliying fark. Who knows ...

Collapse -

It's a data base not a spread sheet

by Tony Hopkinson In reply to My name is Muhammad irfan ...

A query either returns a single variable or a recordset.
That means you can have two separate quesries, one for transactions and one for total, or one result set where the total is repeated for each record.

You've got the transactions.

Total would be
Select Sum(Remaining_Balance) as [Total emaining balance] from WhateverTableName Where Namee_Buy = 'Jon'

If you wanted one record set then
something like

Select t.*,tt.RemainingAmount From Transactions t
left join (Select [Namee Buy], SUM(Remaining) as RemainingAmount From Transactions Group By [Namee Buy]) tt
On t.[Namee Buy] = tt.[Namee Buy]
Where t.[Namee Buy] = 'Jon'

would give

Namee Buy _date Bought Paid Remaining RemainingAmount
--------- ----------------------- ---------------------- ---------------------- ---------------------- ----------------------
Jon 2010-08-10 00:00:00.000 30000 10000 20000 50000
Jon 2010-08-08 00:00:00.000 50000 20000 30000 50000

PS Tell me you are not using [Namee Buy] as your key, what happens if you have more than one Ali?

Notice the date format I've used, It won't get confused on a system set up as mm-dd-yyyy.

Why don't you pick legal names for the columns, spaces are a pain in the arse as are underscores, but iof you are going to use one of them, do that one not a mixture.

Big things are built from little things, get the little things wrong and .....

Collapse -

And by the way...

...names are not important here in TR, but marks are. Please remember to mark Tony's reply as the answer, if it works for you. Then we can bring back the Tech Q&A as it was originally meant to be.

Related Discussions

Related Forums