[AccessD] I need a crosstab (kinda)

jwcolby jwcolby at colbyconsulting.com
Fri Dec 4 13:42:59 CST 2009


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



More information about the AccessD mailing list