[AccessD] CrossTab Form

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