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

[Terri]

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.

[Bob Darlington]

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.

[Terri]

"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

[LoopyNZ]

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!

[Bob Darlington]

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

[Terri]

"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?

[Bob Darlington]

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?