SQL Syntax 1

[jmandiaz]

Hi guys,
i have a table named transactions the table has the following fields businessdate, calendardate, acctnbr, cashin, cashout.
i'm trying to query all acctnbrs that have the sum of cashin and cashout >= 3000.

What would the syntax be to return the data i'm looking for. Please advice and thanks in advance


-Jaime

 

[dhookom]

Your question is a bit vague. I think the following SQL should meet your requirements.

SELECT acctnbr
FROM [Transactions]
GROUP BY acctnbr
HAVING Sum(cashin + cashout) >= 3000;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

dhookom
i'm sorry for being vague. I would like for the query to return

acctnbr|cashin|cashout
1234567 3000 50000
7890101 7000 4000

How can i accomplish this? Thanks again.

-Jaime

 

[dhookom]

We still don't know for sure if you want both totals to be >=3000 or just one. Maybe this will work:

SELECT acctnbr, Sum(CashIn) as SumIn, Sum(CashOut) As SumOut
FROM [Transactions]
GROUP BY acctnbr
HAVING Sum(cashin) >= 3000 AND Sum(cashout) >= 3000;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

dhookom,
okay cashin is one column, cashout, is another column,
let say i have several businesses each business is has it's own acctnbr.
through out the day i make deposits and withdrawels from each of the accts.

Let say i come in make i deposit for 1k at 10 a.m. i come in at 12 p.m and make a deposit for 2k. I then come in at 3 p.m and withdrawel 10k

what i'm after is a cumulative of deposit and withdrawels that are equal or greater than 3k. does that make sense. Please advice

-Jaime

 

[dhookom]

I'm not sure why you can't just type or paste in some records and the desired display? This would seem to make answering so much easier. If you want a total of both columns, try:

SELECT acctnbr, Sum(CashIn) as SumIn, Sum(CashOut) As SumOut
FROM [Transactions]
GROUP BY acctnbr
HAVING Sum(cashin + cashout) >= 3000;

If this doesn't work, please tell us why you think it doesn't work.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

dhookom,
i want the outpput to be displayed like
SumOfCashIn SumOfCashOut
$40,000.00
$15,443.02 $0.00
$10,215.62 $0.00
$3,661.78 $0.00
$8,290.00 $0.00
$9,814.51 $0.00
$6,779.00
$106,317.60 $0.00
$5,605.00 $0.00
$5,007.00 $0.00
$6,403.00 $0.00
$3,260.00 $0.00
$8,350.00 $0.00
$4,300.00 $0.00
$4,239.26 $0.00
$7,710.24 $0.00
$14,772.00 $0.00
$6,539.00 $0.00

however the query that i'm using seems not be pulling all the records accross.

here's the query i'm using

SELECT dbo_Transactions.BusinessDate, dbo_Transactions.CalendarDate, dbo_Accounts.AccountNumber, dbo_Accounts.Title1, dbrganizations.OrganizationName, dbrganizations.TypeOfBusiness, dbrganizations.EmployerID, Sum(dbo_Transactions.CashIn) AS SumOfCashIn, dbo_Transactions.CashOut As SumOfCashOut
FROM ((dbrganizations RIGHT JOIN dbrganizationLinks ON dbrganizations.OrganizationTag = dbrganizationLinks.OrganizationTag) RIGHT JOIN dbo_Accounts ON dbrganizationLinks.AccountTag = dbo_Accounts.AccountTag) INNER JOIN dbo_Transactions ON dbo_Accounts.AccountNumber = dbo_Transactions.AccountNumber1
GROUP BY dbo_Transactions.BusinessDate, dbo_Transactions.CalendarDate, dbo_Accounts.AccountNumber, dbo_Accounts.Title1, dbrganizations.OrganizationName, dbrganizations.TypeOfBusiness, dbrganizations.EmployerID, dbo_Transactions.CashOut
HAVING (((dbo_Transactions.BusinessDate)=#7/24/2006#) AND ((Sum(dbo_Transactions.CashIn))>=3000))
ORDER BY dbo_Accounts.AccountNumber;


UNION

SELECT dbo_Transactions.BusinessDate, dbo_Transactions.CalendarDate, dbo_Accounts.AccountNumber, dbo_Accounts.Title1, dbrganizations.OrganizationName, dbrganizations.TypeOfBusiness, dbrganizations.EmployerID, dbo_Transactions.CashIn, Sum(dbo_Transactions.CashOut) AS SumOfCashOut
FROM ((dbrganizations INNER JOIN dbrganizationLinks ON dbrganizations.OrganizationTag = dbrganizationLinks.OrganizationTag) LEFT JOIN dbo_Accounts ON dbrganizationLinks.AccountTag = dbo_Accounts.AccountTag) LEFT JOIN dbo_Transactions ON dbo_Accounts.AccountNumber = dbo_Transactions.AccountNumber1
GROUP BY dbo_Transactions.BusinessDate, dbo_Transactions.CalendarDate, dbo_Accounts.AccountNumber, dbo_Accounts.Title1, dbrganizations.OrganizationName, dbrganizations.TypeOfBusiness, dbrganizations.EmployerID, dbo_Transactions.CashIn
HAVING (((dbo_Transactions.BusinessDate)=#7/24/2006#) AND ((Sum(dbo_Transactions.CashOut))>=3000))
ORDER BY dbo_Accounts.AccountNumber;

 

[jmandiaz]

Dhookom,
thanks for your queries all the examples giving returned data but it's not the output i was looking for. Do you any other suggestions. Thanks in advance

-Jaime

 

[dhookom]

You lost me with the union query. I can see the desired display but don't know why there seem to be nulls in there. I have no idea what your source records look like.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

Hi dhookom,
my records are coming from a trasaction table. The transaction is setup with the following colums/fields

Businessdate|Acctnumber|Cashin|Cashout|Checktotal|Reversed

let's say i walk in to jaime's bank. I make a deposit for my business account for 2k. The system takes my deposit as
cashin|cashout
$2000 $0
I then come in and withdraw 1k
the systems then logs the transaction as
cashin|cashout
$2000 $0
$0 $1000
I then come backin to the bank to make a deposit for 5k
cashin|cashout
$2000 $0
$0 $1000
$5000 $0
I come back later again and withdraw 2k
cashin|cashout
$2000 $0
$0 $1000
$5000 $0
$0 $2000

Here's my problem
when i select the following everythign that i'm looking for gets selected

SELECT
dbo_Transactions.BusinessDate,
dbo_Transactions.ApplicationCode1,
dbo_Transactions.AccountNumber1,
dbo_Accounts.Title1, Sum(dbo_Transactions.CashIn) AS SumOfCashIn, dbo_Transactions.Reversed

FROM (dbo_Transactions INNER JOIN dbo_Accounts ON dbo_Transactions.AccountNumber1 = dbo_Accounts.AccountNumber) INNER JOIN dbperators ON dbo_Transactions.OperatorTag = dbperators.OperatorTag
GROUP BY dbo_Transactions.BusinessDate, dbo_Transactions.ApplicationCode1, dbo_Transactions.AccountNumber1, dbo_Accounts.Title1, dbo_Transactions.Reversed
HAVING (((dbo_Transactions.BusinessDate)=#8/1/2006#) AND ((dbo_Transactions.ApplicationCode1)="DDA") AND ((Sum(dbo_Transactions.CashIn))>=3000) AND ((dbo_Transactions.Reversed)=0));

HOWEVER when i select the cashout column i my query returns nothing

SELECT
dbo_Transactions.BusinessDate,
dbo_Transactions.ApplicationCode1,
dbo_Transactions.AccountNumber1,
dbo_Accounts.Title1, Sum(dbo_Transactions.CashIn) AS SumOfCashIn,
Sum(dbo_Transactions.CashOut) AS SumOfCashOut,
dbo_Transactions.Reversed

FROM (dbo_Transactions INNER JOIN dbo_Accounts ON dbo_Transactions.AccountNumber1 = dbo_Accounts.AccountNumber) INNER JOIN dbperators ON dbo_Transactions.OperatorTag = dbperators.OperatorTag
GROUP BY dbo_Transactions.BusinessDate, dbo_Transactions.ApplicationCode1, dbo_Transactions.AccountNumber1, dbo_Accounts.Title1, dbo_Transactions.Reversed
HAVING (((dbo_Transactions.BusinessDate)=#8/1/2006#) AND ((dbo_Transactions.ApplicationCode1)="DDA") AND ((Sum(dbo_Transactions.CashIn))>=3000) AND ((Sum(dbo_Transactions.CashOut))>=3000) AND ((dbo_Transactions.Reversed)=0));

any suggestion on what the heck i'm doing wrong? Please advice and again thanks for all your help with this

 

[dhookom]

If you want accounts where sum of cashin >=3000 or sum of cashout >=3000 then you can use:

SELECT 
dbo_Transactions.BusinessDate, 
dbo_Transactions.ApplicationCode1, 
dbo_Transactions.AccountNumber1, 
dbo_Accounts.Title1, Sum(dbo_Transactions.CashIn) AS SumOfCashIn,
Sum(dbo_Transactions.CashOut) AS SumOfCashOut, 
dbo_Transactions.Reversed

FROM (dbo_Transactions INNER JOIN dbo_Accounts ON dbo_Transactions.AccountNumber1 = dbo_Accounts.AccountNumber) INNER JOIN dbo_Operators ON dbo_Transactions.OperatorTag = dbo_Operators.OperatorTag

WHERE dbo_Transactions.BusinessDate=#8/1/2006# AND dbo_Transactions.ApplicationCode1="DDA" AND dbo_Transactions.Reversed=0

GROUP BY dbo_Transactions.BusinessDate, dbo_Transactions.ApplicationCode1, dbo_Transactions.AccountNumber1, dbo_Accounts.Title1, dbo_Transactions.Reversed

HAVING Sum(dbo_Transactions.CashIn)>=3000 OR Sum(dbo_Transactions.CashOut)>=3000;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

dhookom you are the man! that's EXACTLY what i was looking for. Thanks again