Sum all but *that* category

[Datahunter]

First off, I'm not sure I should be classed as a 'junior' member when it comes to Access. Is there something beyond that? Maybe 'just-fallen-off-the-turnip-truck' newbie without a clue. <g>

I have a table of donations with fields: amount, month (entered as the actual name rather than a number), year, tax receipt number.

I've managed to generate a query which produces only those records in which a receipt was issued (in other words, the receipt field isn't empty).

What I would like to do is, for example, sum up the year 2002 but *excluding* July 2002. Yeah, I can get this second query (based on the first one) to sum up each month but then I have to add it up manually.

I'd rather be able to hand my boss a nicely printed spreadsheet showing July as well as the rest of the year rather than one with my scribbled manual calculations. <g>

Any suggestions, simply described, would be appreciated. Thanks in advance.

Regards,
Datahunter

 

[Fornatian]

Add criteria to a query to exclude all those records for July 2002, then build a second query to pick only those records in 2002 using query1 as the source. Does that make sense to you?

 

[Deborah]

I hope I'm not misunderstanding, but it sounds to me that you're asking how to sum all records in a field. If I'm right, you can do this by creating a report, using the report wizard. (And it looks pretty too - your boss will think you're a genius!)

In order to activate the "summary options" in the 4th step of the wizard, (which is where you can choose which columns you'd like to sum), you must choose at least 1 grouping level in the 3rd step of the wizard. Try it out and let me know if this is what you're looking for. I apologise if this is way too basic for what you're looking for... I'm new to these boards.

 

[Fornatian]

Deborah you are right in your thinking however, because Mr DataHunter wants to exclude a month from the calculated sum we have to account for this, thus the criteria suggestion

 

[Datahunter]

Thanks Ian and Deborah for your suggestions!

I can't say that I've completely understood your suggestions right off the bat, but I'll puzzle over them for a bit. <ponders how to exclude July from a query>

I'm just thrilled that you didn't have me writing code! <g>

Regards,
Datahunter

 

[Fornatian]

Are your dates stored as text or as a date - knowing this I can then advise how to exclude July.

 

[Datahunter]

Hello Ian!

The field data is stored as 'July' and not a date format. I've tried typing in 'not July' in the criteria section but without much success. I can get a second query where it sums up the total for July as well as a third query which totals the whole year.

Perhaps I should be resigned to doing that July calculation manually.... <g>

Datahunter,
Access impaired

 

[Deborah]

If you're trying to make a query that doesn't include July, all you have to do is type Not "July" in the criteria cell under the month field in design view of your query. Then base all reports, forms, etc. on that query.

Hope this helps