[AccessD] I need a crosstab (kinda)

JC,

    It would help if you were in a position to reproduce in your post, the structure of table tblOrderData, along with a few rows of sample dummy data under each column. This should be followed by a sample depiction of the desired output matrix sought by you.

    Alternatively, you could place a zipped copy of skeleton mdb containing the above details at a convenient web site from where it could be downloaded.

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: jwcolby 
  To: Access Developers discussion and problem solving ; Dba-Sqlserver 
  Sent: Saturday, December 05, 2009 01:12
  Subject: [AccessD] I need a crosstab (kinda)


  I have a table that I create for my DBFH client orders.  This table has a set of fields which are the where clause fields.  Every single "where clause" field (for data placed in the table) gets created in the table, then the actual codes that match the where are placed in those fields.

  For example, the source table has a a field called MOB and these fields contain a code of '1' or '2' or they contain the empty string ''.  So when I create tblOrderData, IF I am using the MOB field in the where clause to select data to place in tblOrderData, I create a MOB field in tblOrderData and whatever that field contains gets placed in this field.

  I do this because the client might say "MOB" AND ("Kids_1_3" OR "Kids_4_7" or...) etc.  I need to be able to visually prove that my data matches the where clause.  Thus the where clause fields in tblOrderData.

  Additionally however the client wants to see counts of each where clause field, and counts of the intersection of where clause fields.  "count of MOB AND Kids_1_3", Count of MOB and Kids_4_7" etc. If I have 4 where clause fields I would have a 4x4 matrix with counts of the intersections.  5 where clause fields would give me a 5x5 matrix of counts of intersections.

  He thinks of it as a table where the column headers are the where clause field names and the row headers are the where clause field names.  I am pretty sure I do not know how to do this in SQL.

  Is this possible in SQL?  It is kind of a crosstab but not really.
  -- 
  John W. Colby
  www.ColbyConsulting.com