[AccessD] CrossTab Form

Thanks AD.  Your suggestion about formatting the data as strings will 
do the trick.

Much appreciated.

David

At 7/03/2008, you wrote:
>David,
>
>     Apparently, record source of the form is based upon a crosstab 
> table, with 16 columns representing products and 25 rows 
> representing various stages of data. While most of the rows display 
> routine numbers, some rows display percentages. These rows (showing 
> percentages) are not in a separate block but sandwiched amongst 
> normal rows  displaying numbers. While using only one text box per 
> column on a bound continuous form, you are seeking to display 
> values in these rows in proper percent format, without disturbing 
> normal display of numbers in other rows.
>
>     If the presumptions outlined in previous para hold good, action 
> could be considered on the following lines:
>
>     1 - For sake of illustration, let T_Crosstab be the name of 
> table having fields RowHdg (text type) for row heading and P_1, 
> P_2, P_3 (number type) for products.
>
>     2 - Add two fields named RowNum (number type) and RowType (text type).
>
>     3 - Populate the RowNum column with sequential numbers, in the 
> order in which various rows are required to be finally displayed.
>
>     4 - Identify percentage type rows by entering a character (say 
> P) in RowType column. For all other rows, leave this column blank (Null).
>
>     5 - Use sample query Q_CustomFormat, as given below, as record 
> source for the form.
>
>     Note - If you are in a position to ensure that incoming values, 
> as appended to table T_Crosstab, are suitably formatted strings, 
> the work-around suggested above would no longer be needed. In that 
> case, all the product fields (P_1, P_2, -- etc) would be of text type.
>
>Best wishes,
>A.D.Tejpal
>------------
>
>Q_CustomFormat  (Sample query)
>=================================
>SELECT RowHdg, RowNum, P_1, P_2, P_3
>FROM T_Crosstab
>WHERE RowType Is Null
>UNION ALL SELECT RowHdg, RowNum, Format(P_1, "Percent"), Format(P_2, 
>"Percent"), Format(P_3, "Percent")
>FROM T_Crosstab
>WHERE RowType Is Not Null
>ORDER BY RowNum;
>=================================
>
>   ----- Original Message -----
>   From: David Emerson
>   To: accessd at databaseadvisors.com
>   Sent: Thursday, March 06, 2008 08:14
>   Subject: [AccessD] CrossTab Form
>
>
>   I have an Access XP form (from a legacy database) that is based on a
>   crosstab query.  Across the top are a list of products.  Down the
>   first column are a list of headings: Opening Balance, Stock In, Total
>   Stock, Stock Out, Stock Out Percentage, Closing Balance etc (there
>   are 25 rows in total).  The data is gathered from a number of other
>   tables and queries which are linked only by the product code and are
>   in different formats, and then placed in a temporary table for the
>   crosstab.  The number of fields is consistent - there will always be
>   the same products, and the same headings (therefore the use of a
>   cross tab is not for flexibility of changing products, but to get the
>   data into the required format).  There are 16 products and 25
>   headings, therefore 400 fields of data.
>
>   Most of the rows are standard numbers but some are percentages.  The
>   problem is that all rows are formatted the same way with no decimal
>   places.  This means that the percentage rows only show whole
>   percentages (the numbers are calculated so that 2.75% is stored as
>   2.75 and shows as 3).  What I am looking for is a simple solution
>   where I can format each row of data on the continuous form to suit
>   the type of data.
>
>   The sledgehammer way is to create a table with one row and a text box
>   for all 400 fields.  This would then be used on a form which is set
>   out in a row format.
>
>   I suggested that the form be turned around so that the products are
>   down the side and the headings across the top (in standard Access
>   fashion) but this is not the format the client wants.
>
>   Is there any easy way around this?
>
>   Regards
>
>   David Emerson
>   Dalyn Software Ltd
>   Wellington, New Zealand
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com