Access 2003 - Reporting Frequency of a Table Element

[JimR56]

I'm new to Access and am having trouble creating a report I need. Here's the scoop...

I've created a database with one table (Master). The table was populated with data from five different Excel spreadsheets - each spreadsheet was a "dictionary" containing names and descriptive data on data elements found in an agency's particular database application. Essentially I'm trying to create a "master catalog" of data elements so that we can identify the data elements common to most/all of the application databases. The "Master" table contains the following columns:

Data Element Name - (i.e. SSN)
Database Name - (that the Element originated from)
Table Name - (that the element was originally a part of)
Element Length - (1, 5, etc)
Data Type - (alpha, A/N, etc.
Cross-Reference Name - (a common name that the field might also be known as - "Social Security Number" as opposed to "SSN", "soc_sec_nbr", etc.) This is the name we will use to identify commonality of this element among the various disparate databases).

My goal is to generate a report that breaks on Cross-Reference Name, sorted by frequency of the Cross-Reference Name (from most-frequently-found to least-frequently-found), then by Originating Database Name, then Original Table Name - something like this:

Cross-Reference Name: Social Security Number
Times Found in Master Database: 87
DatabaseName TableName ElementName Type Length
HR Table-1 SSN A/N 11
Payroll SSN-TBL SOC N 9
and so on thru 87 detail lines...

Cross-Reference Name: Zip Code
Times Found in Master Database: 72
OrigDatabase TableName ElementName Type Length
Credit Zip-Table ZipCD A/N 10
Payroll Address Zip-Code N 9
Payroll Zip-Codes Zip-9 N 9
and so on thru 72 detail lines...

And so on thru all the remaining Cross-Reference Names...

How do you count/rank the frequency of a field, then use that field as the control break for a header/detail report???

Thanks in advance!

 

[markphsd]

You could do the ranking/frequency in code, either by looping through a recordset to get the rank, or you can get a crosstab query or something similiar to get you the results.

You can then sort by the results and create a query.

Then you can base the report off the query.

The only thing is, you can't dynamically change the record source of the report. So the first time you create this report, you will already have to have created the query. From then on, use code to recreate the query.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.