Complex formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ashlynn Grace - 06 Jul 2006 20:20 GMT

Hi. I am working on a query that is pulling from 2 tables. Its WHERE
Statement includes all items where cash in or cash out falls in the following
cases:
1. one large transaction for a single day that is between 3,000 and 9,999
2. multiple smaller transactions in a single day that is between 3,000 and
9,999

Here is what I have so far:

SELECT Day1.ACCOUNT_NUM, [Account Info].NAME, Day1.[CASH-IN],
Day1.[CASH-OUT], Day1.DATE, [Account Info].[Branch Number]
FROM [Account Info] INNER JOIN Day1 ON [Account Info].Account_Num =
Day1.ACCOUNT_NUM
WHERE (((Day1.[CASH-IN]) IS NOT NULL) OR ((Day1.[CASH-OUT]) IS NOT NULL));

Can anyone help me with the rest of the WHERE statement?
--------------------------------------------------------------------
Thanks!

Reply to this Message

John Spencer - 06 Jul 2006 20:56 GMT

Perhaps, the following will work for you. Your requirements were not
entirely clear.

For instance, ??multiple smaller transactions?? Each transaction Less than
3000? Less than 300?

SELECT Day1.ACCOUNT_NUM
, [Account Info].NAME, Day1.[CASH-IN]
, Day1.[CASH-OUT]
, Day1.DATE
, [Account Info].[Branch Number]
FROM [Account Info] INNER JOIN Day1
ON [Account Info].Account_Num = Day1.ACCOUNT_NUM
WHERE Day1.AccountNum IN
(SELECT D.AccountNum
FROM Day1 As D
WHERE [Cash-in] Between 3000 and 9999 Or [Cash-Out] Between 3000
and 9999)
OR Day1.AccountNum IN
(SELECT D.AccountNum
FROM Day1 As D
WHERE [Cash-in] <3000 and [Cash-Out] <3000
GROUP BY D.AccountNum
HAVING Sum(Cash-in]) Between 3000 and 9999 Or Sum([Cash-out])
between 3000 and 9999)

> Hi. I am working on a query that is pulling from 2 tables. Its WHERE
> Statement includes all items where cash in or cash out falls in the
[quoted text clipped - 15 lines]
> --------------------------------------------------------------------
> Thanks!

Reply to this Message