Help needed on query

[Johnny]

Hi, everyone

I have a samll DB of debits(expenses, food, etc) and
credits(deposits).
If the general balance becames negative I need a query that will give
me all the debits after the negative balance.
In other words, I need to have the last positive balance and all the
debits that make the balance becames negative.
If the account becames negative more than once, I only need the last
time it was negative.

Tks in advance

 

[Jeff Boyce]

Johnny

If, by "after", you mean at a date/time after, then your records need to
have some kind of date/time field.

If, by "small DB of debits and credits" you mean a single table, your
records need to have some kind of category field (i.e., debit, credit).

It all starts with the data, and I don't understand your data structure well
enough to offer more specific suggestions.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Johnny]

Hi Jeff

Thank you for the reply.

In fact my DB has two tables that are related in 1 to many.A table
with people information(name, adress, and so on) and a table with
money transactions.
The transactions table has 6 fields:
- regID related with the ID of the "Cliens" table (not automatic
counter on transactions table)
-movReg to identify the transaction number(automatic)
-Credits
-Debits
-movDate as date of transaction
-movDescr as description of transaction (food, money deposit, gas....)

When people get negative balance I need to present a paper with all
transactions after that situation(negativ balance), and also need to
present the las balance before the account went negative. The point
is, if the balance gets negative more than one time in a month, I only
need to check for negative balances on the date I'm presenting the
paper, If I'll present the paper today I only need to check for
negatibe balances today, and when I get the people whose balance is
negative, I need to grab all transactions done by people after the
negative situation.
Resuming
Last positive balance and transactions after negative balance

Ex:
Name Last Positive balance Final Balance
Johnny $300 (-) $400
Debit Credit Date
(-)$500 26-08-08
(-)$200 27-08-08


Regards
Johnny

 

[Jeff Boyce]

Johnny

If this were mine, I'd probably start with identifying all folks with a
negative balance. I'd use a totals query to get the sum of transaction
amounts. Are your credits and debits entered as positive and negative
numbers, or all positive numbers?

I'm not clear on why/how folks could have transactions AFTER a negative
balance (unless they were adding more money in their accounts). So the
negative balance is really (potentially) a temporary condition?

If you wanted to find out who was 'negative' as of a point in time, that's
fairly straight-forward (to include the date/time selection criterion in the
above query). If you wanted to find out "dynamically" who's just gone
negative, you'll need to re-run your process after EVERY transaction. I'm
thinking you'd need to create a procedure that checks the balance and run
that after each transaction.

Perhaps you could "flag" a field in the person record with the date/time
their account went negative. On the other hand, if you needed to keep track
of when, every time, a person's account went negative, you'd need another
table to write to ("on this date, in the conservatory, with the
candlestick") and have your procedure record the necessary info.

I still don't think I have quite enough of the "requirements" yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Johnny]

Hi Jeff

Maybe I didn't explain my self very well. Let's see.
At first time people use to add money to common safe for future use
when needed.
There is also a common money found, that's why people could have
transactions after negative balance.
When this happen people should be advised about the negative
transactions in order to add that value to regularize the balance.
I already have a query that give me the total of people that has
negative balance, and all the transactions made.
At a certain date for example, someone whose current balance was $100,
had the need of $200, so he got $100 of is own and $100 of common
found and became $100 negative, that person made two more transatrions
of $100 each and the balance is now $300 negative.
My problem is here, I have the query whre that person name is, among
others also with negative balance, with all the transactions made
since the beginning of the account, but to advise the person(s) I only
need the transaction who made the balance became negative and all
other after this.
The idea is just to a list of people who are negative, and all the
transactions after the one that let the balance became negative
(included).
In spoken words I'll say:"Mr John you had $100 on your account and you
pick $200 on date xx/xx/xx, $100 on date xx/xx/xx and more $100 on
date xx/xx/xx, so you are $300 negative"

My query:
SELECT MovimentoEuros.MovNum, MovimentoEuros.MovNReg,
MovimentoEurosTesteQuery.strName,
MovimentoEurosTesteQuery.SumOfValorMov
FROM MovimentoEurosTesteQuery INNER JOIN MovimentoEuros ON
MovimentoEurosTesteQuery.MovNReg = MovimentoEuros.MovNReg
WHERE (((MovimentoEurosTesteQuery.SumOfValorMov)<0))
GROUP BY MovimentoEuros.MovNum, MovimentoEuros.MovNReg,
MovimentoEurosTesteQuery.strName,
MovimentoEurosTesteQuery.SumOfValorMov;

On table Debits are saved as negative values and credits as positive.

I hope that I was more clear now and thank you for the help

Johnny

 

[Jeff Boyce]

Johnny

I'm don't work directly in SQL for most of my queries, so I'll have to step
back.

My previous post contained the steps (and thinking) I'd go through, but it
sounds like I don't have a very good handle on your specific situation.

Perhaps one of the other newsgroup readers can offer some insight.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Johnny]

Hi Jeff

Thank you anyway for your time

 

[rabina akhtar]

Johnny

I'm don't work directly in SQL for most of my queries, so I'll have to
step back.

My previous post contained the steps (and thinking) I'd go through, but it
sounds like I don't have a very good handle on your specific situation.

Perhaps one of the other newsgroup readers can offer some insight.

Regards


Microsoft Office/Access MVP