Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Rpt: crosstab(s) PLUS calculated fields

Posted on 2000-05-09
13
Medium Priority
?
398 Views
Last Modified: 2012-05-04
This probably requires coding a recordset? Do I have a choice?

Got a cross-tab worked up, based on a single table (probably should be on a query, and could I then use two tables?) that has 18 number fields.

I need to "insert" into the cross-tab at print time, the following:

1 field (for each of the 5 output columns) that totals the 4 fields above it.
1 field (for each of the 5 output columns) that shows the difference between a source field and the  calculated field.

2 more calculated fields, whose calculations are ratios. I think these also use some calculated data and some "source" data, according to different formulae, tho I'm not yet sure what those formulae are.

The only other way I can think of to do this is to break the one neat cross tab into two, and build an "intermediate" (because it physically has to go in the middle of the displayed "table" query that calculates the new numbers.  Will ONE additional query do it?

Thanks.

Marilyn Justman
0
Comment
Question by:mjustman
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2795791
Hello mjustman,

Could you please post here your current crosstable and details how do you like to calculate new fields. I think this might not require any coding if we can calculate everything in one crosstable query...

Regards,
paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2795792
meant crosstable query SQL and some sample data...
0
 

Author Comment

by:mjustman
ID: 2796625
As requested, here's the SQL:

TRANSFORM Max(CRHistFinData.Value) AS MaxOfValue
SELECT CRHistFinData.ItemTitle
FROM CRHistFinData
WHERE (((Format([Date],"yyyy"))>1994) AND ((CRHistFinData.AlphaID)="00960"))
GROUP BY CRHistFinData.ItemTitle
PIVOT Format([Date],"yyyy");

Now let's see if I can grab the query results and get them in here!

OK: here's query's output saved as prn file:
                               CRHistFinData_Crosstab        5/10/2000

ItemTitle    1995      1996      1997
Accts rec   1911517   1680649   2327882
Capital S    250000    250000    250000
Cash         463568    605975    453429
Cash val      76713     81713     81713
Current d   2418134   1920718   2219407
Debenture         0         0         0
Def liab      92603    125031     73115
Dividends         0         0         0
Merchandi   1898386   1665484   1685638
Mortgages    424405    408905    391687
Net worth         0         0         0
Notes rec         0         0         0
Profits      248964    203667    186637
Reserves          0         0         0
Ret earni   2708724   2912391   3099028
Sales      18182360  18408536  16719170
U S Gov s         0         0         0
Withdrawa         0         0         0

This is passed to a report that looks exactly like this and is included as a subreport in a larger, named one.  (I also haven't figured out yet how to grab the ID that is used to join this to the larger report -- xtab queries don't seem to tolerate "extra" fields.)

But the goal here is to total the first 4 (substantive) rows of this AS the 5th row ("Total.."), then subtract the next row (Current debt) from that total and report the results immediately after the Current row, then figure two ratio rows ... (which, incidentally, are reported in percentages (not $ amts as all other #s represent) and report those following.  So order of output should be:

Cash ... (data)
Accts rec (data)
Notes rec (data)
Merchandise (data)
Cash val  (data)
->TOTAL ASSETS (calculated)
Current debt (data)
-> WORK CAPITAL (TOTAL - Current d, calc'd
->CURRENT RATIO (calc'd)
->QUICK RATIO (calc'd)
Debentures (data)
.....
etc ... data to end

If you want the source table's structure, let me know.

Thanks.

Marilyn

p.s. there's no averaging or counting: I used max of each to grab the one and only piece of data for each label/year to make the table. So data you see IS what's in the "year" field.  Item No controls the order of Item title, which is the row heads.
mj
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:paasky
ID: 2796764
This is much complicated than I though at first glance. I see you're working with GL application?

In GL systems I've been working with there has been differend code fields in account table which have been used to help calculating. I think table definitions would help now.

You like to build all data (and calculations) into worktable and print it contains to report or would it be possible to build several subreports which calculate certain data and put them all into one main report?
0
 

Expert Comment

by:nealjd
ID: 2801555
I prefer a "custom" crosstab convention like this:

SELECT CRHistFinData.ItemTitle, Max(IIf(Format([Date],"yyyy")=1995,[value],0)) AS [1995 Value], Max(IIf(Format([Date],"yyyy")=1996,[value],0)) AS [1996 Value], Max(IIf(Format([Date],"yyyy")=1997,[value],0)) AS [1997 Value]
FROM CRHistFinData
WHERE (((Format([Date],"yyyy"))>1994) AND ((CRHistFinData.AlphaID)="00960"))
GROUP BY CRHistFinData.ItemTitle;

This does the same thing as crosstabs except it is far more flexible, filling in dead space with 0s and so forth. In the standard crosstab, if there was no 1996 data it would generate only 2 columns and as such your reports would choke because they needed 3.
0
 

Author Comment

by:mjustman
ID: 2803114
Thanks, guys.
The trickiest part of this problem has gone away.  The "other" programmer on this is planning to calculate the calculated fields at form time, so the query and report will get "hard" numbers. And yes, I probably MUST have a pre-query query.

I'm planning to break this one into two crosstabs, so the two lines with percentages in them (in the middle) can be simple queries and get formatted differently.

I'm going to try nealid's code, altho the new filip to this is that (it turns out) I need to report out the last 5 year's worth of data FOR THOSE YEARS THAT HAVE DATA, not a known set of years, and I still have to get that parameter (referring to the master-report's query) to replace "00960" for the AlphaID. (nealid: "date" is actually a field in the table/query.)

I think the only fair thing to do is to split the points between you for now, and re-asks the question if I need more specific help.  

Does either of you know HOW TO SPLIT POINTS????

Thanks.

Marilyn
0
 
LVL 10

Accepted Solution

by:
paasky earned 200 total points
ID: 2803223
You want already move this to PAQ and share points between us? You're too kind Marilyn! But it's okay too if you leave this question open and wait other experts to participate this too.

You can send a request to EE Community Support and they can help you to split points: http://www1.experts-exchange.com/Customer_Service/Experts_Exchange/

I'm willing to help you in this thread so feel free to send extra questions here so I can feel I've really deserved the points. ;o)

Regards,
paasky
0
 

Author Comment

by:mjustman
ID: 2803346
This is one of the few places I can afford to be a Big Spender!

Problem is changing, as I said, and I haven't yet tested any suggestions.  Sure, I'll appreciate any further help, and will post further questions, but to keep the thread "honest", I think I should officially end it before we take the discussion "elsewhere".

Off to visit the Customer Service people!
0
 
LVL 3

Expert Comment

by:darinw
ID: 2808307
Reviewing question.

darinw
Customer Service
0
 

Author Comment

by:mjustman
ID: 2808567
Guys:
I've decided you're right...I should save some points for future questions...so have asked CS to reduce this to 100 points, then split between you.
Hope that isn't TOO disappointing.
BTW: I've already posted a new question, regarding how to parameterize this xtab, which NOBODY's responded to yet.  That means it's a GOOD QUESTION.
I'm open to answers suggesting I attack the problem a whole different way...

Marilyn
0
 
LVL 3

Expert Comment

by:darinw
ID: 2817459
Community Support has reduced points from 200 to 50
0
 
LVL 3

Expert Comment

by:darinw
ID: 2817460
Hello everyone,

Reducing points to 50 allow for point decrease and split.

mjustman, you can now accept one of the comments in this thread as an answer. To award the other Expert, you can create a new question in this topic area with a title of 'For ExpertName -- 10341641' using that Experts username.

Remember, the Accept Comment as Answer button is in the header of the comment.

For your convenience, you can use this link to create the new question:
http://www.experts-exchange.com/bin/NewQForm?ta=91

darinw
Customer Service
0
 

Author Comment

by:mjustman
ID: 2835807
Nealjd - Go post  some comment at the question titled "nealid-Q10341641" (sorry for the misspelling) ... I owe you some points and I need a comment to "accept" in order to give them to you.

Nealjd and Paasky:
Believer came up with a nice, neat solution to my "xtab" problem by simply making my data into a multi-column report INSTEAD of an xtab. Don't know if you can discern enough details from the discussion there, but ask him or me if you have occasion to deal with a problem like this one!

Q was number: 103533590
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question