[mso] Re: Getting Access to do the math for me :VSMail mx4
- From: James LaBorde <jlaborde@xxxxxxxxx>
- To: "'mso@xxxxxxxxxxxxx'" <mso@xxxxxxxxxxxxx>
- Date: Mon, 7 Jun 2004 09:31:12 -0700
Katherine,
You are now getting into the best part of Access. Building your own queries
can be trying but is what separates Access from Excel. You will want to
know a few things that will help you a lot.
First, the aggregate queries. If you open your New Query window, you will
see a large symbol that looks like a funny looking E. That will give you
totals. If you watch when you click on it you will see an additional line
added to your query options below. It will automatically believe you want
to group by every field. What you will want is to group by the year field
and sum your donations field. This will give you total donations by year.
The above assumes your dates are entered strictly by the year of donation.
However, I assume they can make more than one donation per year so you may
have a date field instead. If this is the case you will want to use the
DatePart() function. It has 2 parameters, which part of the date you want
to extract and which field/date to extract it from. IN order to pull the
year from a field called DonationDate, you would enter the following:
DonationYear: Datepart("yyyy", [DonationDate])
Your results will be the four digit year of the donationdate. This will
also come in handy when you want to look at this year and last year. Your
criteria for this year will be DatePart("yyyy", Date()). For last year
Datepart("yyyy", Date())-1. This will help in doing your queries I will
describe in the next step.
You will also need to learn about Outer Joins. You average query has
multiple tables in it and they are linked to include everything that matches
in both tables (This is an Inner Join). Sometimes that is not what you
need. When you want to find your LYBUNTS, you will need to do 3 queries.
First you will want to use the datepart function to limit your query to only
donors from last year. Save this query and create another, again using the
datepart function to determine this year's donors. Now open your third
query and bring your first two queries in as your source data. Make sure
you have a linking field that will be appropriate (such as donor). Once you
link the two fields, double-click the link and you will get a new dialog
box. It will show you three options. The one that is currently selected is
the option to show all records from each table that match. The other two
options are what you will want. You will want to select the option that
will display all records from your LastYear's donors query and only matching
records from your CurrentYear Donors query. Now add whatever fields you
want from the last year's donors query. Lastly add one field from the
Current Year Donors query that will have data as long as they made a
donation. Under the criteria for that field, enter Is Null. This will
select all of the records from the Last Year's query that don't have a
matching record in the Current Year query, your LYBUNTS.
Best of all since you are using the Datapart functions rather than
hardcoding a daterange for your criteria, you will be able to keep reusing
the queries without having to reenter your criteria.
I hope this helps some. If you need more help, just holler and I will do
what I can to assist you.
Thank You,
James La Borde
South Western Federal Credit Union
(562) 694-8296 ext. 2251
-----Original Message-----
From: Katherine Driskell Felts [mailto:subscriptions@xxxxxxxxxxxxxxxxx]
Sent: Monday, June 07, 2004 8:39 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Getting Access to do the math for me :VSMail mx4
Hello friends. I about have the data base merging I told you about
done. What I did was exported tables into Excel, cut and past info into
the appropriate columns and then imported it back into Access. It would
have been nice if I could have cut and pasted directly into Access, but
I never figured that out.
Here's the current problem: Now that I have the 02 and 03 donations in
with my 04 ones, I'd like to have a field on my main donor page (with
contact info and such) that tracks the total giving by year and total
giving history. Then I want to be able to ask the data base who my
LYBUNTS are (Last Year But Unfortunately Not This Year) donors are
(they're good prospects). I'll also, in the future want to be able to
write a letter to my donors and suggest donations that start at last
year's giving + 5% rounded up to something reasonable, and then with
giving options that go up from there. Can someone tell me where to find
the easy obvious way of doing that?
Thanks,
Kathie
P.S. a good book recommendation would still be appreciated.
Katherine Driskell Felts,
Executive Director
Blooming Prairie Center
Deep Roots, Bright Futures, Unlimited Horizons
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes)
in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
- » [mso] Re: Getting Access to do the math for me :VSMail mx4
