Query for YTD Total

[Pam]

I have a query that pulls information from a Contributor table and a linked
query (queries for ID, date and amount of contribution). This query asks for
user input of year and date. I would also like to be able to pull YTD
information in a seperate field. All of this gets written to a receipt to
each person on the contributor list (table). I've tried doing another query
for YTD based on the year and joining that with the 1st table and query.
However, when I run it, it asks the user for YYYY and then MM and then YYYY
again. If I put this information in, the query runs, but information is
missing. There are no contibution amounts for some contributors that should
be there and ALL of the monthly amounts are missing

My questions are ... is there any way to "capture" that year information
when the user inputs it the first time? Instead of making another query and
joining it to the others, can this be done directly on the resulting page
(report) itself?

Thanks!

 

[KARL DEWEY]

Post the query SQL.
Open the query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.

 

[Pam]

SELECT DISTINCTROW Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm
yyyy') AS [ContributionDate By Month], tblContributor.ContributorID,
tblContributor.Account, tblContributor.[OneTimeContributor?],
tblContributor.[Inactive?], tblContributor.Addressee,
tblContributor.Address1, tblContributor.Address2, tblContributor.City,
tblContributor.State, tblContributor.Zip, Sum(qryMonthlyLettersSub.Amount) AS
[Sum Of Amount]

FROM tblContributor LEFT JOIN qryMonthlyLettersSub ON
tblContributor.ContributorID = qryMonthlyLettersSub.ContributorID

GROUP BY Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm yyyy'),
tblContributor.ContributorID, tblContributor.Account,
tblContributor.[OneTimeContributor?], tblContributor.[Inactive?],
tblContributor.Addressee, tblContributor.Address1, tblContributor.Address2,
tblContributor.City, tblContributor.State, tblContributor.Zip,
qryMonthlyLettersSub.ContributorID;

 

[KARL DEWEY]

Post the SQL for qryMonthlyLettersSub also.
--
KARL DEWEY
Build a little - Test a little

SELECT DISTINCTROW Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm
yyyy') AS [ContributionDate By Month], tblContributor.ContributorID,
tblContributor.Account, tblContributor.[OneTimeContributor?],
tblContributor.[Inactive?], tblContributor.Addressee,
tblContributor.Address1, tblContributor.Address2, tblContributor.City,
tblContributor.State, tblContributor.Zip, Sum(qryMonthlyLettersSub.Amount) AS
[Sum Of Amount]

FROM tblContributor LEFT JOIN qryMonthlyLettersSub ON
tblContributor.ContributorID = qryMonthlyLettersSub.ContributorID

GROUP BY Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm yyyy'),
tblContributor.ContributorID, tblContributor.Account,
tblContributor.[OneTimeContributor?], tblContributor.[Inactive?],
tblContributor.Addressee, tblContributor.Address1, tblContributor.Address2,
tblContributor.City, tblContributor.State, tblContributor.Zip,
qryMonthlyLettersSub.ContributorID;

Post the query SQL.
Open the query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.

 

[Pam]

qryMonthlyLettersSub:

SELECT tblReceipts.ContributorID, tblReceipts.ContributionDate,
tblReceipts.Amount
FROM tblReceipts
WHERE
(((tblReceipts.ContributionDate)>=DateSerial([EnterTheYearYYYY],[EnterTheMonth1to12],1)
And
(tblReceipts.ContributionDate)<DateSerial([EnterTheYearYYYY],[EnterTheMonth1to12]+1,1)));

Post the SQL for qryMonthlyLettersSub also.
--
KARL DEWEY
Build a little - Test a little

SELECT DISTINCTROW Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm
yyyy') AS [ContributionDate By Month], tblContributor.ContributorID,
tblContributor.Account, tblContributor.[OneTimeContributor?],
tblContributor.[Inactive?], tblContributor.Addressee,
tblContributor.Address1, tblContributor.Address2, tblContributor.City,
tblContributor.State, tblContributor.Zip, Sum(qryMonthlyLettersSub.Amount) AS
[Sum Of Amount]

FROM tblContributor LEFT JOIN qryMonthlyLettersSub ON
tblContributor.ContributorID = qryMonthlyLettersSub.ContributorID

GROUP BY Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm yyyy'),
tblContributor.ContributorID, tblContributor.Account,
tblContributor.[OneTimeContributor?], tblContributor.[Inactive?],
tblContributor.Addressee, tblContributor.Address1, tblContributor.Address2,
tblContributor.City, tblContributor.State, tblContributor.Zip,
qryMonthlyLettersSub.ContributorID;

Post the query SQL.
Open the query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls information from a Contributor table and a linked
query (queries for ID, date and amount of contribution). This query asks for
user input of year and date. I would also like to be able to pull YTD
information in a seperate field. All of this gets written to a receipt to
each person on the contributor list (table). I've tried doing another query
for YTD based on the year and joining that with the 1st table and query.
However, when I run it, it asks the user for YYYY and then MM and then YYYY
again. If I put this information in, the query runs, but information is
missing. There are no contibution amounts for some contributors that should
be there and ALL of the monthly amounts are missing

My questions are ... is there any way to "capture" that year information
when the user inputs it the first time? Instead of making another query and
joining it to the others, can this be done directly on the resulting page
(report) itself?

Thanks!

 

[KARL DEWEY]

Try this ---
WHERE [tblReceipts].[ContributionDate] Between DateSerial(Year(Date()), 1,1)
AND Date();
--
KARL DEWEY
Build a little - Test a little

qryMonthlyLettersSub:

SELECT tblReceipts.ContributorID, tblReceipts.ContributionDate,
tblReceipts.Amount
FROM tblReceipts
WHERE
(((tblReceipts.ContributionDate)>=DateSerial([EnterTheYearYYYY],[EnterTheMonth1to12],1)
And
(tblReceipts.ContributionDate)<DateSerial([EnterTheYearYYYY],[EnterTheMonth1to12]+1,1)));

Post the SQL for qryMonthlyLettersSub also.
--
KARL DEWEY
Build a little - Test a little

SELECT DISTINCTROW Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm
yyyy') AS [ContributionDate By Month], tblContributor.ContributorID,
tblContributor.Account, tblContributor.[OneTimeContributor?],
tblContributor.[Inactive?], tblContributor.Addressee,
tblContributor.Address1, tblContributor.Address2, tblContributor.City,
tblContributor.State, tblContributor.Zip, Sum(qryMonthlyLettersSub.Amount) AS
[Sum Of Amount]

FROM tblContributor LEFT JOIN qryMonthlyLettersSub ON
tblContributor.ContributorID = qryMonthlyLettersSub.ContributorID

GROUP BY Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm yyyy'),
tblContributor.ContributorID, tblContributor.Account,
tblContributor.[OneTimeContributor?], tblContributor.[Inactive?],
tblContributor.Addressee, tblContributor.Address1, tblContributor.Address2,
tblContributor.City, tblContributor.State, tblContributor.Zip,
qryMonthlyLettersSub.ContributorID;


:

Post the query SQL.
Open the query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls information from a Contributor table and a linked
query (queries for ID, date and amount of contribution). This query asks for
user input of year and date. I would also like to be able to pull YTD
information in a seperate field. All of this gets written to a receipt to
each person on the contributor list (table). I've tried doing another query
for YTD based on the year and joining that with the 1st table and query.
However, when I run it, it asks the user for YYYY and then MM and then YYYY
again. If I put this information in, the query runs, but information is
missing. There are no contibution amounts for some contributors that should
be there and ALL of the monthly amounts are missing

My questions are ... is there any way to "capture" that year information
when the user inputs it the first time? Instead of making another query and
joining it to the others, can this be done directly on the resulting page
(report) itself?

Thanks!

 

[Pam]

Karl,

I modified the qryMonthlyLettersSub with that code. But I'm not sure what
it's supposed to do. When I run the query now, it gives me all the
contributions for the current year.

What I wanted to do was keep the original concept, where the user inputs the
month and year to get receipts for a specific month and year. But I also
wanted to ADD a field on the report that shows YTD contributions also ...
like a running total of what that person has given so far in the year that
I'm running the receipts for. For example, right now I may be running
December 2008 receipts and would like to show what the user gave for the
whole year 2008 along with the detail of what was given just in December
2008.

Thanks for all your help so far!

Try this ---
WHERE [tblReceipts].[ContributionDate] Between DateSerial(Year(Date()), 1,1)
AND Date();
--
KARL DEWEY
Build a little - Test a little

qryMonthlyLettersSub:

SELECT tblReceipts.ContributorID, tblReceipts.ContributionDate,
tblReceipts.Amount
FROM tblReceipts
WHERE
(((tblReceipts.ContributionDate)>=DateSerial([EnterTheYearYYYY],[EnterTheMonth1to12],1)
And
(tblReceipts.ContributionDate)<DateSerial([EnterTheYearYYYY],[EnterTheMonth1to12]+1,1)));

Post the SQL for qryMonthlyLettersSub also.
--
KARL DEWEY
Build a little - Test a little


:

SELECT DISTINCTROW Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm
yyyy') AS [ContributionDate By Month], tblContributor.ContributorID,
tblContributor.Account, tblContributor.[OneTimeContributor?],
tblContributor.[Inactive?], tblContributor.Addressee,
tblContributor.Address1, tblContributor.Address2, tblContributor.City,
tblContributor.State, tblContributor.Zip, Sum(qryMonthlyLettersSub.Amount) AS
[Sum Of Amount]

FROM tblContributor LEFT JOIN qryMonthlyLettersSub ON
tblContributor.ContributorID = qryMonthlyLettersSub.ContributorID

GROUP BY Format$([qryMonthlyLettersSub].[ContributionDate],'mmmm yyyy'),
tblContributor.ContributorID, tblContributor.Account,
tblContributor.[OneTimeContributor?], tblContributor.[Inactive?],
tblContributor.Addressee, tblContributor.Address1, tblContributor.Address2,
tblContributor.City, tblContributor.State, tblContributor.Zip,
qryMonthlyLettersSub.ContributorID;


:

Post the query SQL.
Open the query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

I have a query that pulls information from a Contributor table and a linked
query (queries for ID, date and amount of contribution). This query asks for
user input of year and date. I would also like to be able to pull YTD
information in a seperate field. All of this gets written to a receipt to
each person on the contributor list (table). I've tried doing another query
for YTD based on the year and joining that with the 1st table and query.
However, when I run it, it asks the user for YYYY and then MM and then YYYY
again. If I put this information in, the query runs, but information is
missing. There are no contibution amounts for some contributors that should
be there and ALL of the monthly amounts are missing

My questions are ... is there any way to "capture" that year information
when the user inputs it the first time? Instead of making another query and
joining it to the others, can this be done directly on the resulting page
(report) itself?

Thanks!