MS Access 2000

958 Views 8 Replies 2 Participants Last post by Telecomguy, Nov 16, 2001

Telecomguy

Discussion Starter · Nov 14, 2001

I am trying to get totals in a report from a crosstab query. My query has 13 months of information. I can total up the thirteen months by company and by row, but I can't figure out how to total only the current month information. This is how it looks:

Company 00/10 00/11 ...01/10 totals
revenue 1 2 .... 3 6
expenses 1 2 .... 3 6
investment 1 2 .... 3 6
rev......3(can't get)
exp.....3(can't get)
inv......3(can't get)

I have tried to get this info using the expression builder, but have been unsuccessful. Any suggestions would be appreciated.

Thank you,
Telecomguy

Save

Status: Not open for further replies.

1 - 9 of 9 Posts

downwitchyobadself

· #2 · Nov 15, 2001

This can be pretty tough. I can't tell from your post how much you know about Access, so we'll start with the simple answer, and just ask if you need more info.

Crosstab queries create their field names on the fly. I would recommend that you not base your report directly on the crosstab, but build a second query on top of the crosstab. Then you'll see how Access is naming those month fields. At that point, the fields in the report footer should just be set using the Sum() expression.

Start by playing with a query based on the crosstab, and perhaps you'll see what I mean. If not, could you post back with the name of an example field from that query, as well as names for all related controls and their control sources from the report?

Save

Telecomguy

Discussion Starter · #3 · Nov 15, 2001

what about trying to come up with an 'if statement' in the expression builder? I haven't been successful with this approach, maybe I am writing the statement wrong. Here's the problem: the description field has three parts..revenue, expense and investment. BUT..all that shows in design view , because it is based on a crosstab query, is "Description" and breaks it out on the report itself. I have 30 companies, sorted by company, and I am trying to get the current month total for each broken down by r,e,i. when I attempt to sum the current month, I get the TOTAL of all three parts for the month not a break down of the elements. I can, however, sum row by row for the entire 13 months (which is good) useing the expression builder.

My question is: how do I pull out the individual parts of "description" to make totals? Would an 'if statment' be able to do this?

thanks again,
Telecomguy

Save

downwitchyobadself

· #4 · Nov 15, 2001

Okay, let's start over. Can you post the SQL of your crosstab here? (Design view > View menu > SQL view, copy and paste here). What I was trying to tell you is that you can't create those sums by basing that report on your crosstab--as you say, it splits out the fields at run-time, and you need to actually set up fixed fields in order to get a sum.

But posting the SQL will help me understand a little better how you've got it set up so far, because frankly I'm not following you.

Save

Telecomguy

Discussion Starter · #5 · Nov 15, 2001

Here is the SQL. maybe this will help. Sorry if I confused you before. there is probably a very simple solution that I am not seeing. thank you for your help.

TRANSFORM Sum(tblPrlmBC.[COLUMN E]) AS [SumOfCOLUMN E]
SELECT tblPrlmBC.COMPANY, tblPrlmBC.[LINE NUMBER], tblPrlmBC.DESCRIPTION
FROM tblPrlmBC
WHERE (((tblPrlmBC.DATE)>="2000/09") AND ((tblPrlmBC.[LINE NUMBER])="001")) OR (((tblPrlmBC.DATE)>="2000/09") AND ((tblPrlmBC.[LINE NUMBER])="002"))
GROUP BY tblPrlmBC.COMPANY, tblPrlmBC.[LINE NUMBER], tblPrlmBC.DESCRIPTION
PIVOT tblPrlmBC.DATE;

Telecomguy

Save

downwitchyobadself

· #6 · Nov 16, 2001

Okay. At least I see how you're setting the thing up. (No need to apologize, btw--it's all a work in progress

)

What I said before remains true. I'm going to pretend your xtab query is called qryXTabCompDesc. You just substitute whatever its real name is...

You need to build a new query, and when you do, select qryXTabCompDesc from the Queries tab of the Show Table window. You should then see that the "pivoted" tblPrlmBC.DATE field (that's a really bad field name, btw, because DATE, like TIME, NUMBER, FOR, NEXT, TYPE, etc., is a reserved word for Access--has a significance in programming, therefore not so good for naming non-programmed stuff like fields and controls) has been replaced by a series of dates, whose format will reflect the way the original date itself is stored. The similar test query I built (not having your tables, of course) gives me fields called 01/09/2000, 01/10/2000, etc., because I'm using short dates, and because I'm in Europa, where the day comes first.

But these are actually the field names your report would use. Now you start to see, perhaps, where the cageyness of reporting on xtab queries in Access begins. Because, if you're only doing 13-month reporting, then your field names are going to change each month... But that's another subject, requires some vba, or at least that's the only solution I've ever found.

Let's say you select all the fields, and save your new query as qryResultsCompDescXtab. Then you create a report based on that qryResultsCompDescXtab; much more flexible report, because it is based on a select query and thus has fixed field names. See the difference? The xtab creates its fields "on the fly", while the select uses fixed names.

So you can then add grouping levels, if you want, so that you can add/subtract (I'm not sure if your values require a simple sum, I'm assuming so, don't quite know what line numbers 001 and 002 mean, nor what's in description) by company, by description, etc. And for both any group-level total, and for the report-level total in the report footer, the control source looks like this

Code:

=Sum([01/09/2000])

if we use my first example field.

As I say, this is only a temporary solution, because your "static" field names will change, as the underlying query's field names change, as more months are added. But we'll x that bridge when we tab to it. Hee-hee.

Hope this gets you going. Keep us posted.

See less See more

Save

Telecomguy

Discussion Starter · #7 · Nov 16, 2001

Thanks for all of your help. I will try your suggestions and let you know what I come up with. You may not hear from me for a week...I am taking Thanksgiving week off!! This will be the LAST thing I want to think about while at home!!

Have a great Holiday.

Save