By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Group and sum transactions for last year in which a transaction exists

P: n/a
I have a table called Transactions with 3 fields: ID, Date, and Amount. Each
ID can have multiple transactions in one particular year. An ID might not
have had any transactions in recent years.

For each ID I would like to sum all the transactions for the most recent
year in which one or more transactions have occurred.

Thanks for any help and for all previous help.
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Create a query showing the data you want ie all records where:
TransactionDate >= DateAdd("m", -12, Date)
Use that query to sum the Amount.

Incidently, I trust that you have not really used 'Date' for the field name
in your table!
--
Bob Darlington
Brisbane
"Terri" <Te***@spamaway.com> wrote in message
news:ci**********@reader2.nmix.net...
I have a table called Transactions with 3 fields: ID, Date, and Amount. Each ID can have multiple transactions in one particular year. An ID might not
have had any transactions in recent years.

For each ID I would like to sum all the transactions for the most recent
year in which one or more transactions have occurred.

Thanks for any help and for all previous help.

Nov 13 '05 #2

P: n/a

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...
Create a query showing the data you want ie all records where:
TransactionDate >= DateAdd("m", -12, Date)
Use that query to sum the Amount.

Incidently, I trust that you have not really used 'Date' for the field name in your table!


Thanks. Unfortunately the field is named date which I know is a reserved
word. I didn't design the database and the field name can't be changed.

I built a preliminary query (q1) and aliased the field as TransactionDate.
My second query (q2) is now:

SELECT q1.ID, q1.TransactionDate
FROM q1
WHERE (((q1.TransactionDate)>=DateAdd("m",-12,[TransactionDate])))
ORDER BY q1.ID;

The second query is returning the same number of records as the first so I'm
not sure if the DateAdd function is what I need. Given the following
TransactionDates in the first query:

4/12/2003
1/1/2003
12/23/2002
5/1/2001

I would only want to return:
4/12/2003
1/1/2003
which is all transactions in the most recent year for which there is a
transaction.

Thanks
Nov 13 '05 #3

P: n/a
Hi Terri,

I guess from your messages you're talking about calendar years (e.g. as
opposed to financial). If that's the case, you can do what you want by
using the Year() function and a series of three queries.

(1) Get sum ID Amount Sum for every year in the table:

SELECT Transactions.ID, Year([TransactionDate]) AS TransactionYear,
Sum(Transactions.Amount) AS YearAmount
FROM Transactions
GROUP BY Transactions.ID, Year([TransactionDate]);

(2) Get the latest year for each ID from the query above:

SELECT qry1_year_sums_all.ID, Max(qry1_year_sums_all.TransactionYear) AS
MaxTransactionYear
FROM qry1_year_sums_all
GROUP BY qry1_year_sums_all.ID;

(3) Join the above queries to get the yearly sums for the latest years
only:

SELECT qry1_year_sums_all.*
FROM qry2_max_year INNER JOIN qry1_year_sums_all ON
(qry1_year_sums_all.TransactionYear = qry2_max_year.MaxTransactionYear)
AND (qry2_max_year.ID = qry1_year_sums_all.ID);

Let me know if you have any trouble with these queries.

Cheers,
------------
LoopyNZ
------------

=====================
Original Message:

I have a table called Transactions with 3 fields: ID, Date, and Amount.
Each
ID can have multiple transactions in one particular year. An ID might
not
have had any transactions in recent years.

For each ID I would like to sum all the transactions for the most recent
year in which one or more transactions have occurred.

Thanks for any help and for all previous help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

P: n/a
Terri,
Try using the actual field name ([date]) instead of the alias in the
DateAdd. It should work OK.

--
Bob Darlington
Brisbane
"Terri" <Te***@spamaway.com> wrote in message
news:ci*********@reader2.nmix.net...

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...
Create a query showing the data you want ie all records where:
TransactionDate >= DateAdd("m", -12, Date)
Use that query to sum the Amount.

Incidently, I trust that you have not really used 'Date' for the field name
in your table!


Thanks. Unfortunately the field is named date which I know is a reserved
word. I didn't design the database and the field name can't be changed.

I built a preliminary query (q1) and aliased the field as TransactionDate.
My second query (q2) is now:

SELECT q1.ID, q1.TransactionDate
FROM q1
WHERE (((q1.TransactionDate)>=DateAdd("m",-12,[TransactionDate])))
ORDER BY q1.ID;

The second query is returning the same number of records as the first so

I'm not sure if the DateAdd function is what I need. Given the following
TransactionDates in the first query:

4/12/2003
1/1/2003
12/23/2002
5/1/2001

I would only want to return:
4/12/2003
1/1/2003
which is all transactions in the most recent year for which there is a
transaction.

Thanks

Nov 13 '05 #5

P: n/a
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41***********************@news.optusnet.com.a u...
Terri,
Try using the actual field name ([date]) instead of the alias in the
DateAdd. It should work OK.

--
Bob Darlington
Brisbane
"Terri" <Te***@spamaway.com> wrote in message
news:ci*********@reader2.nmix.net...

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...
Create a query showing the data you want ie all records where:
TransactionDate >= DateAdd("m", -12, Date)
Use that query to sum the Amount.
=DateAdd("m",-12,[Date]) works now that I put the brackets around the field

but the query returns the same amount of records with or without the
criteria.

SELECT transactions.Date
FROM transactions;

same amount of records as

SELECT transactions.Date
FROM transactions
WHERE (((transactions.Date)>=DateAdd("m",-12,[Date])));

Do I need to sort or group?
Nov 13 '05 #6

P: n/a
OK. Now I'll open my eyes and try again.
Try DateAdd("m",-12,Date())
All records will have been after DateAdd("m",-12,[Date]) .
You want 12 months before today (Date()), not 12 monhts before the first
transaction date.

--
Bob Darlington
Brisbane
"Terri" <Te***@spamaway.com> wrote in message
news:ci**********@reader2.nmix.net...
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41***********************@news.optusnet.com.a u...
Terri,
Try using the actual field name ([date]) instead of the alias in the
DateAdd. It should work OK.

--
Bob Darlington
Brisbane
"Terri" <Te***@spamaway.com> wrote in message
news:ci*********@reader2.nmix.net...

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...
> Create a query showing the data you want ie all records where:
> TransactionDate >= DateAdd("m", -12, Date)
> Use that query to sum the Amount.
=DateAdd("m",-12,[Date]) works now that I put the brackets around the

field but the query returns the same amount of records with or without the
criteria.

SELECT transactions.Date
FROM transactions;

same amount of records as

SELECT transactions.Date
FROM transactions
WHERE (((transactions.Date)>=DateAdd("m",-12,[Date])));

Do I need to sort or group?

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.