How can point me out the best way on a query

[Johnny]

Hi everyone

Hi have a DB were I control the transactions made by a group f people.
The 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 "Clients" 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 join the party they use to add money to personal account.
There is also a common money found, to be used when needed.

Now the problem:
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
transactions
of $100 each and the balance is now $300 negative.
I have a query that will give me all people than has negative balance,
with all the transactions made
since the beginning of the account.

What I need:
In order to advise advise all people that has negative balance,
besides the negative amount, I need the transaction who made the
balance became negative and all other after this one. I Don't need a
list with all transactions.
The idea is just to put in 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"

The query that gives all negative balances and all transactions made:
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.

Hope someone could help
Tks

 

[Ken Sheridan]

The following should return the latest sequence of negative balances per
regID, which I think is what you are asking for:

SELECT regID, movDate, movDescr, Credits, Debits,
(SELECT SUM(Credits)
FROM Transactions AS T2
WHERE T2.regID = T1.regID
AND T2.movDate <= T1.movDate
AND ( T2.movReg<= T1.TransactionID
OR T2.movDate <> T1.movDate)) +
(SELECT SUM(Debits)
FROM Transactions AS T2
WHERE T2.regID = T1.regID
AND T2.movDate <= T1.movDate
AND ( T2.movReg<= T1.TransactionID
OR T2.movDate <> T1.movDate)) AS Balance
FROM Transactions AS T1
WHERE
(SELECT SUM(Credits)
FROM Transactions AS T2
WHERE T2.regID = T1.regID
AND T2.movDate <= T1.movDate
AND ( T2.movReg<= T1.TransactionID
OR T2.movDate <> T1.movDate)) +
(SELECT SUM(Debits)
FROM Transactions AS T2
WHERE T2.regID = T1.regID
AND T2.movDate <= T1.movDate
AND ( T2.movReg<= T1.TransactionID
OR T2.movDate <> T1.movDate)) < 0
AND movDate >
(SELECT MAX(movDate)
FROM Transactions AS T3
WHERE
T3.regID = T1.regID
AND
(SELECT SUM(Credits)
FROM Transactions AS T2
WHERE T2.regID = T3.regID
AND T2.movDate <= T3.movDate
AND ( T2.movReg<= T3.TransactionID
OR T2.movDate <> T3.movDate)) +
(SELECT SUM(Debits)
FROM Transactions AS T2
WHERE T2.regID = T3.regID
AND T2.movDate <= T3.movDate
AND ( T2.movReg<= T3.TransactionID
OR T2.movDate <> T3.movDate)) >= 0)
ORDER BY regID, movDate DESC, movReg DESC;

Note that if the movDate values are entered as dates without a time of day
and a client executes two transactions on the same day the order of those
transaction in the query's result set will be determined by the movReg
values. As this is an autonumber it cannot be guaranteed that the rows will
be in the correct order of the actual transactions, an autonumber only
guaranteeing uniqueness not necessarily sequence. If on the other hand the
movDate values are unique by virtue of including the time of day, then there
should be no problem.

As you'll probably have guessed the above query is adapted from one of my
own using a similar table, the only material difference apart from the column
names being that both the credit and debit values are positive numbers.
Usually when positive and negative values are used a single TransactionAmount
column would be used rather than having separate credit and debit columns.
Testing it with my table does return the correct rows, so it should work as
amended with your table. You can of course join the clients table to the
Transactions table in the outer query to include the names etc of clients.

Ken Sheridan
Stafford, England

 

[Johnny]

Hello Ken

Thank you for your answer.
I need to confess my mistake. I initally said that the table has 6
fields, but it was on first version, I changed to 5 fields and credits
and debits are saved on same field, credit as positive value and debit
as negative, I think that will make a little difference on query
structure.
SO the transactions table fields are:
- regID related with the ID of "Clients" table (not automatic counter
on transactions table)
- movNum to identify the transaction number(automatic)
- movValue (Debits as negative and Credits as positive)
- movDate as date of transaction
- movDescr as description of transaction (food, money deposit,
gas....)

Regards
Johnny

 

[Ken Sheridan]

All you should need to do is, instead of adding the two results of the pairs
of subqueries use a single subquery in each case. You'll now sum the
movValue column rather than the debits or credits of course.

Ken Sheridan
Stafford, England

 

[Johnny]

Hi Ken

I tried the query you sent, but for some reason no records are found
and there are at least 3 people with negative balance, also the empty
query takes long time to show!

regards
Johnny

 

[Ken Sheridan]

I think see the problem; I hadn't taken account of the account going back
into credit after one or more consecutive debit balances. To make matters
simpler its probably best to do it in two stages, first creating a simple
balances query, qryBals:

SELECT movNum, regID, movDate, movValue, movDescr,
(SELECT SUM(movValue)
FROM Transactions AS T2
WHERE T2.regID = T1.regID
AND T2.movDate <= T1.movDate
AND ( T2.movNum <= T1.movNum
OR T2.movDate <> T1.movDate)) AS Balance
FROM Transactions AS T1;

If there can be only one transaction per customer per day you can simplify
it to:

SELECT movNum, regID, movDate, movValue, movDescr,
(SELECT SUM(movValue)
FROM Transactions AS T2
WHERE T2.regID = T1.regID
AND T2.movDate <= T1.movDate) AS Balance
FROM Transactions AS T1;

and then querying this:

SELECT *
FROM qryBals AS Q1
WHERE Balance < 0
AND (movDate =
(SELECT MAX(movDate)
FROM qryBals AS Q2
WHERE Q2.regID = Q1.regID
AND Q2.Balance < 0)
OR Transactiondate >
(SELECT MAX(movDate)
FROM qryBals AS Q3
WHERE Q3.regID = Q1.regID
AND Q3.Balance >= 0
AND Q3.movDate <
(SELECT MAX(movDate)
FROM qryBals AS Q4
WHERE Q4.regID = Q1.regID
AND Q4.Balance < 0)))
ORDER BY regID, movDate DESC, movNum DESC;

This seems to work OK with my dummy data. Its fast, but is using a table
with a restricted number of rows of course. If you are using it as the
RecordSource of a report remove the ORDER BY clause and use the report's own
internal sorting and grouping mechanism. Indexing makes a big difference to
performance of course.

Just to be clear on what it does; it returns the latest subset of
consecutive debit balance rows per customer, which I think is what you want.

Ken Sheridan
Stafford, England

 

[Johnny]

Hello Ken

Everything worked like you said I get the latest debits, but the
query is very, very, very slow, and computer freezes most of the
times.
Transactions table has around 1000 records, If you know a way to get
arround this, let me know please.

Thank you
Johnny

 

[Ken Sheridan]

Access is not good at handling correlated subqueries and these are fairly
complex ones. First thing is to make sure the relevant columns are indexed
of course, but you might be asking too much of it here.

Ken Sheridan
Stafford, England