At the historic site where I work, I’m in charge of running Microsoft Access – even though I really don’t know much about it. (I know more than my co-workers, so that means I’m in charge.)
So, unsurprisingly, I often run into problems which – although they’re probably no problem at all for people who actually know Access or SQL – are enough to completely stump me. So I thought “why not ask the “Alas” readership? Nothing stumps them!”
So right now, what I need to produce is a report that contains the names of everyone who has given money to our Disabled Access Campaign, the sum of how much they’ve given since date X, and the total amount that has been given to the Disabled Access campaign so far since date X. And I can do all of that – what I can’t do is make Access put it all in a single report.
Here’s the SQL for my query “WhoGaveWhatByCatagory”:
FROM Members INNER JOIN Gifts ON Members.MemberID = Gifts.MemberID
GROUP BY Members.[First name], Members.[Last name], Members.MemberID, Gifts.[Type of Donation], Gifts.Date
HAVING1
ORDER BY Members.[Last name];
That query nets me a list of names, and how much they’ve given to the Disabled Access Campaign since January 2, 2004. And I can create a report printing all that out, of course.
I can also create a separate query which will tell me the sum total of all donations from the first query; that looks like this:
FROM WhoGaveWhatByCatagory;
But I don’t know how to incorporate that (or something like it) into the first query. And when I try telling the Report-making wizard that I want to use both queries in my report, it tells me it has no way of linking the two queries.
What I’m trying to do is to combine the two in a single report; that is, to have the report show first the list of names and donations, and then at the end of that list give a sum total of all the donations.
If any Access-user out there has any suggestions, I’d really appreciate it.
- (Gifts.[Type of Donation])=”Disabled Access Campaign”) AND ((Gifts.Date)>#1/1/2004# [↩]
I never really got the hang of reports, unfortunately. But it seems like you might not need two separate queries — you just need Access to add up the number of donations.
This page might help: http://www.dbforums.com/archive/index.php/t-478087.html or try clicking my name.
You might be able to do something like this: Place a text box in the footer
with a control source of: =Sum([FldNameYouWantTotalOf])
I’m not really into Access and it’s bastardization of SQL, so I can’t say what’s possible without looking a bit more into it.
Having said that, I also a little confused about what you are trying to get – do you want to get the total sum of all donations, or the total sum of all donations per person?
If it is the first, then it shouldn’t really be possible to incorporate, as it results in one row/field while the rest of the query results in (potentially) many rows (unless of course this is the only result you want, in which case there are simpler queries) – if it is the later, then is should be possible to get the wanted result.
If no-one with more Access experience offers a good solution, e-mail me, and I’ll make an effort (shouldn’t be too hard, but it’s easier if someone who knows the syntax does it).
For the record I work in Oracle, MS SQL Server and MySQL.
The trick you’re missing is a subreport.
Create a simple report with the second set of information. Go back to the main report, and make sure there’s space for the subreport.
Make sure the wizard button is activated on your toolbar, click the subreport button, drag out the area where you want it–then the wizard should guide you through the rest.
I’m not fond of subreports, as the formatting can be very tricky–but it sounds like the easiest way to get at the data you want.
Er, where “toolbar” = “toolbox”. That’s an important distinction. I never proofread on Sunday.
Thanks, folks!
I’ll try Cat’s suggestion first, just because it sounds most likely to fit within my (limited) abilities).
(And Kristen, I’m trying to get both; a sum of how much each indivudal has donated next to each name, and then at the end of the list of names a sum total of all donations from everybody.)
You can sum the figures at the report level. Webmacher was right.
Oh yah, I forgot. If you just add a sum field at the report level, it will always sync to the content of the report. Sub reports have a habit of developing a life of their own.
Sorry – just catching up on my “Alas” reading and came across this. You’ve probably long since solved this, but if not… what you’re missing is a simple sum field on the report itself. Use the original “Who Gave What by Category” qry as your report source, then on the report either turn on the page or report footer, or create a group footer based on your “Type of Donation” field. That will create a new subsection on your report where you will create a new text box that has as it’s control source “=Sum([FieldName])”
Without the quotes and replace [FieldName] with the name of the field you want summed.
This is exactly what I do for a living – feel free to email me if you ever need help/ advice in the future. I’d be happy to take a look at your database (either by email or in person) anytime. I’m also available for courtesy housecalls and tutoring :)
please send me the query you need help with —
Joe
hi
a have 2 table in access
in both has Name,sex field
i want display Fmale Name Of Bothe table
i one Field in Query