Replies inline
Randy, thanks much for your post. Actually what I need to do is create a
letter for each individual contributor with his or her past contributions
all
listed. Not a sum.
Like:
Joe Schmoe
123 Anywhere Street
Anywhere, IL 12345
Dear Joe:
Thanks for contributing this much:
$1000 in 2004
$1000 in 2003
$500 in 2000
We hope you will contribute in 2005.
OK. The following query is named "qryContributions", its code is as follows:
SELECT Contributors.ID,
Contributors.Name,
Contributors.Address,
Contributions.[Contribution Year],
Contributions.[Contribution Amount]
FROM Contributors INNER JOIN Contributions
ON Contributors.ID = Contributions.[Contributor ID];
You could then use a crosstab query (or use the Crosstab Query Wizard) to
have a query looking like this:
TRANSFORM First(qryContributions.[Contribution Amount])
AS ContributionValue
SELECT qryContributions.ID,
qryContributions.Name,
qryContributions.Address,
Sum(qryContributions.[Contribution Amount])
AS [Total Contribution Amount]
FROM qryContributions
GROUP BY qryContributions.ID,
qryContributions.Name,
qryContributions.Address
PIVOT Format([Contribution Year],"yyyy");
Save this query as "qryContributionsCrosstab".
The above query returns only one record per person, and generate one column
per each year. If the person did not contribute on that year, the field will
be not populated. The query also adds a column for the total contribution
amount across all the years. I think is pretty complete.
In MS Word, you will need to select the name of the crosstab-query
"qryContributionsCrosstab". Then insert all the columns of fields using the
"Insert Merge Field" button:
«ID»
«Name»
«Address»
«M_2000» in 2000
«M_2001» in 2001
«M_2002» in 2002
«M_2003» in 2002
«M_2004» in 2002
«Total_Contribution_Amount»
The part that reads "in 200#" should not be typed in directly, instead it
should be inserted as an IF THEN ELSE conditional field by using the "Insert
word Field" button in MS Word. Just specify the field name "M_2000" and the
condition "Is Not Blank", Then you can type the text " in 2000" into the
"Insert This Text" box.
That way Word will not print the lines where the year does not contain
contributions.
The reason I am trying to stay away from a report is because the group I
am
preparing this for is afraid to edit a report. They prefer to edit Word
documents.
I achieved this result in the past by concatenating rows (I used Duane
Hookom's Generic Function to Concatenate Child Records
(
http://www.rogersaccesslibrary.com/...Generic+Function+To+Concatenate+Child+Records')
however now I am having trouble with this because the 2 fields I am trying
to
concatenate are of different types (one is date and one is currency).
Correct Duane Hookom's Concatenate function works beautifully as I read and
tested. However it is based on one-field concatenation. Since it is a
demonstration, you should modify the code to your specific needs.
From:
strConcat = strConcat & _
.Fields(0) & pstrDelim
To:
strConcat = strConcat & _
.Fields(0) & pstrDelim & _
.Fields(1) & pstrDelim
Even better, you should consider to use the following instead:
strConcat = strConcat & _
Format(.Fields(1), "Currency") & " in " & _
Year(.Fields(0)) & vbCrLf
Why? Because it will create this textual output:
$50.00 in 2000
$10.00 in 2001
$30.00 in 2002
$40.00 in 2003
$15.00 in 2004
Does this help?
-Randy
Randy said:
TinleyParkILGal said:
Help me please. I need to merge data. The first table is contributors
(ID,
Name, Address). The next table is contributions (contributor ID,
contribution
year, contribution amount).
I need to produce a letter for each single contributor with his/her
contributions. I can achieve a letter for each contribution but I do
not
know
how to give each contributor a single letter (word merge) listing all
his/her
contributions.
Thanks in advance for saving me!
TinleyParkILGal,
To create a Mail Merge in Word, using data from Access, you will first
need
to create the data source for the mail merge. In this case, it could be a
query. Microsoft Word accepts queries as a record source. Alternatively
you
can use an Access Report, instead of MS Word.
So, first create a query like this:
SELECT Contributors.ID,
First(Contributors.Name) AS fName,
First(Contributors.Address) AS fAddress,
Sum(Contributions.[Contribution Amount]) AS TotalContributedInYear
FROM Contributors INNER JOIN Contributions
ON Contributors.ID = Contributions.[Contributor ID]
WHERE ((Contributions.[Contribution Year])=2004)
GROUP BY Contributors.ID
This query sums or totalize contributions for each person. 2004 is the
year
for which you want to limit the results by (at least I will think so).
You
can use brackets around it to have Access prompt you to type any year.
Question: are you sure your ContribuitonYear field is a number and not a
date?
Run the query in Access and see if the results satisfy you.
"Tax season again."
-Randy
