about summery field

[qwertyu1]

hi all,

iam using crystal xi and oracle 9i.

i have database tables like fr_recon and fr_stand_prefix.

i have databsae fields like : fr_recon.acres_amt(number5)
fr_recon.stand_prefix(varchar4)
and fr_stand_prefix.stand_prefix(varchar1)

iam trying to get the total amout of acres for each stand_prefix.
but the problem is ,i got two fields like fr_recon.stand_prefix and fr_stand_prefix.stand_prefix from both tables. i joined these tables using stand_prefix.

i have data in fr_STAND_PREFIX.standprefix as 'A','B' upto 'Z'.where as in other field i.e fr_recon.stand_prefix
as 'A','B','AZT','AXY','BX','OX' like that.

i need to get the total amount for each stand_prefix that contains instrings also.

i tried formula like instr(fr_recon.stand_prefix),'A'>0 and placed in selectexpert.i got total acres amout for stand_prefix 'A'

but i need total acres amount for all stand_prefixs.and some have no stand_prefix.

please help me.

 

[lbass]

I don't think you can successfully join the two tables, and it is unclear why you would need the table fr_stand_prefix.

I think you could handle this by using only one table and making a formula per each letter of the alphabet and placing them in the detail section:

//{@A}:
if instr({fr_recon.stand_prefix},"A") <> 0 then {fr_recon.acres_amt}

//{@B}:
if instr({fr_recon.stand_prefix},"B") <> 0 then {fr_recon.acres_amt}

//etc.

Then right click on each formula in the detail section and insert a sum at the grand total level.

-LB

 

[qwertyu1]

thanks for reply.iam sorry for that idin't specify the other field.

my question is"


i have to display like:

STAND_PREFIX_CODE STANDTYPE TOTAL_ACRES
A AESTHETIC ZONE 222000
B DEERYARD ZONE 456566
.
.
.
Z SEISMIC ZONE 5578787
'NOPREFIX' ZONE 2 58799

STANDTYPE IS COMING FROM DATABASE FIELD LIKE FR_STAND_PREFIX.STAND_PREFIX_TEST FROM TABLE FR_STAND_PREFIX TABLE.

thanks.

 

[qwertyu1]

its not clear

stand_prefix-code standname tot acres
A aesthetic zone 29870
.
.
.
z deeryard zone 49298
no prefix zone 1527

 

[lbass]

Can't follow this:

STANDTYPE IS COMING FROM DATABASE FIELD LIKE FR_STAND_PREFIX.STAND_PREFIX_TEST FROM TABLE FR_STAND_PREFIX TABLE.

Please use the convention {table.name} for all labels. Also please explain how you were able to link these tables if the two stand_prefix fields are different lengths.

-LB

 

[elsenorjose]

LB,

You can join fields of different lengths in most DBMSs as long as the data types are the same. Naturally, it would cause issues since one table could have a field with entries longer than the associated field in a related table but the DBMS would happily join all day as long as the types are the same. Ah, the wonders of SQL

 

[lbass]

elsenorjose,

I wasn't objecting to the join on the basis of the length per se. The issue here is that the fields look like this:

table1 table2
A A
B AB
C AD
D BC
E BEH

etc.

I would think that joining table1 to table1 would eliminate all but those records where there is a match, i.e., A = A, while the OP wants to check for records in table2 where the field contains an A and perhaps other records.

-LB

 

[elsenorjose]

Oh yeah, no doubt. I just read your post as asking how it was possible to link two tables on different length fields and that's entirely valid in most DBMSs.

 

[qwertyu1]

hello ,

stand name is a heading.

tablename: fr_stand_prefix

fieldname: fr_stand_prefix_test


thanks.

 

[qwertyu1]

hello,

please help me.

 

[lbass]

Glad to help, but you didn't answer my questions about the linking. Please also show your sample data with labels using {table.field}.

-LB

 

[qwertyu1]

iam linking tables like:

fr_recon (table 1) and fr_stand_prefix(table2)

linking fields:

table1 table2

stand_prefix(varchar4) stand_prefix(varchar1)



data in these fields like:


fr_recon.stand_prefix fr_stand_prefix.stand_prefix

(varchar4) (varchar1)

A A
B B
C C
D D
E E
AXU .
YAX .
UVW .
BXO Z
WER
ACD
.
.
.
.
etc


i need to see the report like:

stand_prefix stand_name total_acres_amt
A aesthetic zone 347687
B deeryard zone 457880
C pale zone 6544
D northern zone 76612
.
.
.
.
Z southren zone 5558
NO PREFIX westrenzone 5675



the field stand_name coming from (fr_stand_prefix table) and acres amount coming from (fr_recon table).

 

[lbass]

I have a concern about your link. After linking, please place your two stand_prefix fields in the detail section and then report back on what it displays.

-LB

 

[qwertyu1]

it displays like:

fr_recon. fr_stand_prefix.
(stnad_prefix_code) (stand_prefix)

A A
A A
AX
PX
Z Z
P P
W W
SA
B B
AO
Q Q


i placed left outer join on table2.(i.e fr_stand_prefix)

 

[lbass]

So when {fr_stand_prefix.stand_prefix} is null, does that count as "No Prefix"? That would be fairly simple, but I thought you wanted to subtotal amounts for "A" whenever the {fr_recon.stand_prefix} contained an "A", e.g., if it equaled "AO", "SA", etc., ,which means that the same amount would get counted multiple times. Can you please clarify?

-LB

 

[qwertyu1]

when {fr_recon.stand_prefix} is null that counted
as "no prefix".

is that helps you.
thanks

 

[lbass]

No, that doesn't really answer the question, as fr_recon is your leftmost table--why would it contain nulls? Let's say that it CAN contain nulls, but then you haven't answered the other question:

I thought you wanted to subtotal amounts for "A" whenever the {fr_recon.stand_prefix} contained an "A", e.g., if it equaled "AO", "SA", etc., which means that the same amount would get counted multiple times. Is this true?

-LB

 

[qwertyu1]

that's true.

i need subtotal for"A", though it contains "OA","AHW","AX"

....etc.

 

[lbass]

I think you should use a subreport. In the main report, insert a group on {fr_stand_prefix.stand_prefix}, add the stand name field to the group header, and insert a subreport in the group header. In the subreport linking screen, move {fr_stand_prefix.stand_prefix} to the right, and in the bottom right, UNCHECK "select data based on the field". Then in the subreport record selection formula use:

{fr_recon.stand_prefix} like "*"+{?pm-fr_stand_prefix.stand_prefix}"*"

In the subreport, insert a sum on the acres field. Suppress all sections except the report footer within the subreport. I'm not whether this will handle nulls for {fr_recon.stand_prefix}--would there be corresponding nulls in the {fr_stand_prefix.stand_prefix}?

Maybe you could try this and then report back with the results.

-LB

 

[qwertyu1]

hello,

there is no corresponding nulls in {fr_stand_prefix}.{stand_prefix}.

i tried using subreport data displaying like:

CODE TEXT AMOUT

A AESTHETIC ZONE 192,271



B FUEL BREAKS 2,525



C EXTENDED ROTATION 14,483



D DEER YARD 201,341



E ERODABLE SOILS 1,818



F FILTER STRIP OR RIPARIAN STAND 3,604



H H - OLD PREFIX CODE, PLEASE RECODE 9,160



I OLD GROWTH-MANAGED



J OLD GROWTH-RESERVED



K WILDLIFE OPENING-TREATED



L LAKE AND RIVER ZONES 18,759



M POOR MARKETS



N STATE NATURAL AREA 19,078



O OLD GROWTH-POTENTIAL 5,430



P PLANTATIONS 163,526



Q ROAD AESTHETIC ZONES 11,064



R DESIGNATED WILD RIVER ZONE 8,760



S SPECIES AND COMMUNITIES OF SPECIAL CONCERN 2,754



T TROUT STREAM 1,474



U COUNTY FOREST SPECIAL USE CLASS LANDS 622



V SILVICULTURAL CONCERNS 2,099



W WILD AREA 37,500



X NON-PRODUCTIVE FOREST TYPE 30,538



Y DESIGNATED WILDERNESS AREA



Z OTHER STANDS OMITTED FROM MGT. ACTIVITIES 47,627


can you explain me what acres amt iam getting.

and iam using 2 more tables like:

1)fr_property_item
2)fr_property_group

links like:

{fr_recon}.{prop_code}={fr_property_item}.{prop_code}
and
{fr_property_item}.{property_id}={fr_property_group}.{property_id}

iam getting field like ropcode from {fr_property_item}
and {name} from table fr_property_group.


i placed this fields are in page header.
propcode and name fields are parameter fields.

thanks.