Layout with simple (?) summary

[AnjaNi]

First, being a newbie, I apologize in advance if this there is a self-evident solution to the following issue...:

I have a database containing information about archaeological artifacts, with the following fields (simplified) Site, Artifact_category, Artifact_material, Number (of artifacts). Artifact_category and Artifact_material are drop-down lists from predefined value lists.

From this I would like to make a layout where, when sorted by Site, each Artifact_category is summarized by Artifact_material (from which each value should be the header row). It should look something like this:

Any suggestions will be highly appreciated!

Anja

[Søren Dyhr]

This is a two step journey, first do you need to grasp the gist of this:

...then when you get the hang of it, should this approach give you what you're after:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

What isn't so obvious is that you would need to make a calc using the position in a value-list to perform the horizontal ushering:

ValueCount ( Left ( theList ; Position ( ¶ & theList & ¶; ¶ & value & ¶ ; 1 ; 1 ) ) )

...snipped boldly from this CF:

http://www.briandunning.com/cf/859

--sd

[AnjaNi]

Thank you for the reply!

So far, the 1.5 first steps are good. I am stuck at the repeating caculation. I am sorry, I just don't understand where this calc is supposed to go?

What isn't so obvious is that you would need to make a calc using the position in a value-list to perform the horizontal ushering:

ValueCount ( Left ( theList ; Position ( ¶ & theList & ¶; ¶ & value & ¶ ; 1 ; 1 ) ) )

...snipped boldly from this CF:

http://www.briandunning.com/cf/859

--sd

[Søren Dyhr]

The seating aspect of this:

http://en.wikipedia.org/wiki/Church_usher

Horizontally would you have a valuelist like this:

chert

flint

jaspis

quartzise

quartz

rhyyolite

rock crystral

bergkrystal

slate

...wouldn't you? Which doesn't have any scalar values inhibited like the dates Edoshins method uses, until you make the listing into the actual order. The calc then returns a integer for which repeating field it should be summarized in by comparing the category with the position in the valuelist.

This could be transformed into this:

Case(

    Get( CalculationRepetitionNumber ) =

    ValueCount(

        Left(

            ValueListItems( Get( FileName ); "MadeOf" );

            Position(

                ¶ & ValueListItems( Get( FileName ); "MadeOf" ) & ¶;

                ¶ & Extend(material) & ¶;

                1;

                1

            )

        )

    );

    1

)

And we're ready to summarize (count is enough here) .... but Usually are such statements compacted via Let( statements to make it evolve faster!

--sd