[AccessD] Data dictionary to Report

jwcolby jwcolby at colbyconsulting.com
Sat Oct 6 10:49:57 CDT 2012


I have a database, 225 million rows, 240 columns.  Some of the fields apparently map to another 
database / marketing 'standard' somewhere in the world.  My client has provided a spreadsheet which 
tells me that field ABC of my table 'matches' field XYZ of this other database, sometimes with a 
'where value = xyz).  He wants data element counts, i.e. what are the codes and counts of those 
codes in our table / fields matching this other database.  How many records (the count) in our 
database has data in the specified field or matching the Where().

There are a 225 of these counts in the spreadsheet he provided to me.  He wants to be able to 
provide me a spreadsheet like this and match it against this db but also match it against other 
databases that we have, one spreadsheet for each of our databases, defining field matches.

Obviously this is simply a groupby / count on each specified field, or in some cases a count / 
where(value = xyz) .  I have to present the data back to him somehow.

My 'programmer' response is to design an application where I can import the spreadsheet he provides 
into a new table in each database that he gives me a spreadsheet for.  He tells me the field name in 
our db that he wants the counts for so my application would then generate the SQL statement to do 
the SELECT / Groupby / count, execute the count, get the results back into C#, create a sheet in a 
workbook, and dump the data for each field into a sheet of a spreadsheet.

Alternatively (and more realistically) denormalize the count into a comma delimited list and write 
the count back into the SQL Server table I just created, then paste the result back into the 
original spreadsheet he provided in a new column, or generate a new spreadsheet from the table.

I don't want to launch into developing this application if SQL Server can do this for me natively. 
Does SQL Server have this functionality native?  Can I just somehow generate a report of this?  Can 
I push a denormalized string back to an excel spreadsheet that the client provided?

It seems unlikely to me.

-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it



More information about the AccessD mailing list