sum by date

  • Thread starter Thread starter ading
  • Start date Start date
A

ading

i have a date,debit and credit column on my database. wat id like to do is
to sum the debit and credit by month and then deduct the sum of each column
 
How about creating a totals query that Groups By the Yr and Mth and totals
the other fields:
SELECT Year([DateField]) as Yr, Month([DateField]) as Mth, Sum([Debit]) as
TotDebit, Sum([Credit]) as TotCredit, Sum([Credit] - [Debit]) as TotNet
FROM tblNoNameGiven
GROUP BY Year([DateField]), Month([DateField]);
 
You can do this fairly easily in a report.

Create a report that uses this table.
In report design view, open the Sorting And Grouping dialog (View menu.)
In the dialog, chose the transaction date field, and in the lower pane set
these properties:
Group Footer Yes
Group On Month

Access adds a new group footer to the report design view. In this group
footer, you can display the total credit for the month with a text box that
has this Control Source:
=Sum([Credit])
and debits:
=Sum([Debit])

You can also get a running balance on the report by using the Running Sum
property of the text box.

You do not store these totals in your database.
 
so how many queries that i have to make?

Duane said:
How about creating a totals query that Groups By the Yr and Mth and totals
the other fields:
SELECT Year([DateField]) as Yr, Month([DateField]) as Mth, Sum([Debit]) as
TotDebit, Sum([Credit]) as TotCredit, Sum([Credit] - [Debit]) as TotNet
FROM tblNoNameGiven
GROUP BY Year([DateField]), Month([DateField]);
i have a date,debit and credit column on my database. wat id like to do is
to sum the debit and credit by month and then deduct the sum of each
column
Click to expand...
Click to expand...
 
thanks for your help

where will i put the syntax in the SQL?

Duane said:
How about creating a totals query that Groups By the Yr and Mth and totals
the other fields:
SELECT Year([DateField]) as Yr, Month([DateField]) as Mth, Sum([Debit]) as
TotDebit, Sum([Credit]) as TotCredit, Sum([Credit] - [Debit]) as TotNet
FROM tblNoNameGiven
GROUP BY Year([DateField]), Month([DateField]);
i have a date,debit and credit column on my database. wat id like to do is
to sum the debit and credit by month and then deduct the sum of each
column
Click to expand...
Click to expand...
 
i tried putting it in SQL and every time i tried to open the sql the message
is this
"wrong number of arguments used in function query expression '([date])"

SELECT date([date]) AS mth, Sum([CIBD]) AS totdebit, Sum([CIBC]) AS totcredit,
Sum([Debit]-[Credit]) AS total
FROM psojournal
GROUP BY date([date]);


Duane said:
How about creating a totals query that Groups By the Yr and Mth and totals
the other fields:
SELECT Year([DateField]) as Yr, Month([DateField]) as Mth, Sum([Debit]) as
TotDebit, Sum([Credit]) as TotCredit, Sum([Credit] - [Debit]) as TotNet
FROM tblNoNameGiven
GROUP BY Year([DateField]), Month([DateField]);
i have a date,debit and credit column on my database. wat id like to do is
to sum the debit and credit by month and then deduct the sum of each
column
Click to expand...
Click to expand...
 
I use VB6 as my front end and MS Access as back end.

Allen said:
You can do this fairly easily in a report.

Create a report that uses this table.
In report design view, open the Sorting And Grouping dialog (View menu.)
In the dialog, chose the transaction date field, and in the lower pane set
these properties:
Group Footer Yes
Group On Month

Access adds a new group footer to the report design view. In this group
footer, you can display the total credit for the month with a text box that
has this Control Source:
=Sum([Credit])
and debits:
=Sum([Debit])

You can also get a running balance on the report by using the Running Sum
property of the text box.

You do not store these totals in your database.
i have a date,debit and credit column on my database. wat id like to do is
to sum the debit and credit by month and then deduct the sum of each
column
Click to expand...
Click to expand...
 
Your sql doesn't look like mine. Where did the Year() and Month() columns go
to?
--
Duane Hookom
MS Access MVP

ading via AccessMonster.com said:
i tried putting it in SQL and every time i tried to open the sql the
message
is this
"wrong number of arguments used in function query expression '([date])"

SELECT date([date]) AS mth, Sum([CIBD]) AS totdebit, Sum([CIBC]) AS
totcredit,
Sum([Debit]-[Credit]) AS total
FROM psojournal
GROUP BY date([date]);


Duane said:
How about creating a totals query that Groups By the Yr and Mth and totals
the other fields:
SELECT Year([DateField]) as Yr, Month([DateField]) as Mth, Sum([Debit]) as
TotDebit, Sum([Credit]) as TotCredit, Sum([Credit] - [Debit]) as TotNet
FROM tblNoNameGiven
GROUP BY Year([DateField]), Month([DateField]);
i have a date,debit and credit column on my database. wat id like to do
is
to sum the debit and credit by month and then deduct the sum of each
column
Click to expand...
Click to expand...
Click to expand...
 
Back
Top