sum by date

[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

 

[Duane Hookom]

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]);

 

[Allen Browne]

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.

 

[ading via AccessMonster.com]

so how many queries that i have to make?

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

 

[ading via AccessMonster.com]

thanks for your help

where will i put the syntax in the SQL?

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

 

[ading via AccessMonster.com]

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]);

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

 

[ading via AccessMonster.com]

I use VB6 as my front end and MS Access as back end.

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

 

[Duane Hookom]

Your sql doesn't look like mine. Where did the Year() and Month() columns go
to?
--
Duane Hookom
MS Access MVP

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]);

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