Hi Eddie,
You wanted to group on [Name]
and FinancialYear (FY), then sum
AmountSpent over those groups.
For every PurchaseDate (if that field
is type Date/Time), the formula should
give you what financial year that record
occurs in.
Limited test in Immediate Window:
TDate=#1/1/2005#
?DatePart("yyyy",DateAdd("m",-6,TDate))
2004
TDate=#6/30/2005#
?DatePart("yyyy",DateAdd("m",-6,TDate))
2004
TDate=#7/1/2005#
?DatePart("yyyy",DateAdd("m",-6,TDate))
2005
TDate=#6/30/2006#
?DatePart("yyyy",DateAdd("m",-6,TDate))
2005
TDate=#12/31/2005#
?DatePart("yyyy",DateAdd("m",-6,TDate))
2005
Run the following query to verify w/your data:
SELECT DISTINCT
T.PurchaseDate,
DatePart("yyyy",DateAdd("m",-6,T.PurchaseDate)) As FY
FROM
Transaction As T
ORDER BY
T.PurchaseDate;
If PurchaseDate was type Date/Time
and the results (FY) of above query prove correct,
then more likely the confusion comes from
my haste in writing out the original query.
It should not have been a LEFT JOIN!
You probably don't know this, but I should --
you never get meaningful results when you
filter on the "inner table" of an "outer join."
Mea culpa.
You typically filter first in a separate query,
then outer join to that query!
Create the following query and save as "qryFilter"
SELECT
T.ID,
T.AmountSpent,
DatePart("yyyy",DateAdd("m",-6,T.PurchaseDate)) As FY
FROM
Transaction As T
WHERE
T.PurchaseDate >= #7/1/2004#
AND
T.PurchaseDate < #7/1/2006#;
now outer join your other table
to this query, group by [Name]
and FY, and sum AmountSpent.
SELECT
NA.[Name],
First(NA.Address),
Sum(Nz(Q.AmountSpent, 0)) As SumAmt,
Nz(Q.FY,9999)
FROM
NameAddress As NA
LEFT JOIN
qryFilter As Q
ON
NA.CustomerID = Q.ID
GROUP BY
NA.[Name],
Nz(Q.FY,9999);
or if you don't care about those
in NameAddress who had no
transactions during your filter
interval (maybe that was what
you wanted originally...sigh ...
my fault again...), then just use
an INNER JOIN:
SELECT
NA.[Name],
First(NA.Address),
Sum(Nz(T.AmountSpent, 0)) As SumAmt,
DatePart("yyyy",DateAdd("m",-6,T.PurchaseDate)) As FY
FROM
NameAddress As NA
INNER JOIN
Transaction As T
ON
NA.CustomerID = T.ID
WHERE
T.PurchaseDate >= #7/1/2004#
AND
T.PurchaseDate < #7/1/2006#
GROUP BY
NA.[Name],
DatePart("yyyy",DateAdd("m",-6,T.PurchaseDate))
sorry,
gary
Biff Barker 67 said:
I am a little confused by the DatePArt function
DatePart("yyyy",DateAdd("m",-6,T.PurchaseDate))
What is "
Cheers
Eddie
Gary Walter said:
Hi Biff,
try:
SELECT
NA.[Name],
First(NA.Address),
Sum(Nz(T.AmountSpent, 0)) As SumAmt,
DatePart("yyyy",DateAdd("m",-6,T.PurchaseDate)) As FY
FROM
NameAddress As NA
LEFT JOIN
Transaction As T
ON
NA.CustomerID = T.ID
WHERE
T.PurchaseDate >= #7/1/2004#
AND
T.PurchaseDate < #7/1/2006#
GROUP BY
NA.Name,
DatePart("yyyy",DateAdd("m",-6,T.PurchaseDate));
good luck,
gary
Biff Barker 67 said:
Please ignore my ignorance I am a noob at Access
I have a simple database with only two tables. NameAddress and
Transaction
The NameAddress table contains, you guessed it, my clients name and
address
details.
The Transaction table contains details regarding a customers purchases
with
me.
Fields in this table are:
ID - CustomerID which links to ID in NameAddress table
TransactionID - autonumber as key
PurchaseDate - Date client purchased goods
AmountSpent - How much that transaction
EntryDate - Date I Enter Transaction.
I need a query that will show the total a client has spent by financial
year
Between 1st July 2004 and 30 June 2005 = 05
Between 1st July 2005 and 30 June 2006 = 06
eg
Mary Smith's transactions are $100 in December 04, $200 in February 05
and
$600 in July 05
Name Address Amount
Financial Year
Mary Smith 123 Some Street $300
05
Mary Smith 123 Some Street $600
06
Any ideas
