Hammer,

It is safe to assume that what I sent you are the only records in question.  Thousands more will be imported later.

Option #2 is what I want to do.  What other questions do you have?

By the way: how do you quote from a previous post?  That would make my quotes more presentable.

Blightman,

Attached is a Mass Update showing an example of doing this. Retrieve your records, sorted by violation code. Run this Mass Update.

Note that about 80 percent of this code is only necessary because the State is buried in the City value and must be parsed out. Because of this, we have to do a bunch of bookkeeping to track the occurences, rather than just being able to sort by State.

This can also be adjusted so that you click a command button, or so that the output goes to a printer.

The Mass Update will produce something like this in the WriteLn window:

Violation: CIN
		3
HI		2
Total Violations for CIN: 5
----------
Violation: GEN
WI		1
		1
Chile		1
Total Violations for GEN: 3
----------
Violation: INS
Chile		1
TX		1
Total Violations for INS: 2
----------
Violation: NCK
NY		1
MI		1
IN		1
Total Violations for NCK: 3
----------
Violation: NCL
VA		1
NC		1
NY		2
CO		2
IL		2
NV		1
TX		1
TX		1
FL		1
HI		1
PA		1
MO		1
OK		1
AR		1
OH		1
Total Violations for NCL: 18
----------
Violation: SHC
MA		1
GA		1
Total Violations for SHC: 2
----------
Violation: SHP
LA		1
Total Violations for SHP: 1
----------
Violation: SPC
MS		1
NJ		1
Total Violations for SPC: 2
----------
Violation: TMP
SD		2
AZ		3
VT		1
PA		1
LA		2
VA		1
CO		1
CA		1
Total Violations for TMP: 12
----------
---------------------------------
---------------------------------
Total Violations: 48 

Here's the code:

** PROGRAMMING SECTION: [ GLOBAL CODE ] [] **
// Declare variable to hold the compare values
stat gvState as String
stat gvViolation as String

// Declare variable to hold summary
stat gvGrandTotal as Int

// Declare array to hold state totals
stat gvaStateCounts as Array[100, 2] of String

// Function to parse the State out of the City value.
// The State is whatever follows the comma and space.
Function GetState(vVal as String) As String
var vPos as Int
var vLen as Int
var vRet as String

	vRet = ""
	vLen = @Len(vVal)

	// Find the comma
	vPos = @InStr(vVal, ",")

	// Advance past the comma and space
	vPos = vPos + 2

	// Make sure there is something there
	If(vLen >= vPos)
	{
		// Parse out the State.
		vRet = @Mid(vVal, vPos, vLen)
	}

	Return(vRet)

End Function

// Subroutine to clear state totals array
Subroutine ClearCounts()
var vOuterLoop as Int
var vInnerLoop as Int

	For vOuterLoop = 1 To 100
		For vInnerLoop = 1 To 2
			gvaStateCounts[vOuterLoop, vInnerLoop] = ""
		Next
	Next

End Subroutine

// Function to find a state in the state totals array
Function FindState(vVal as String) As Int
var vLoop as Int
var vRet as Int

	vRet = 0
	vLoop = 1
	While((vLoop <= 5) And (vRet = 0))
	{
		If vVal = gvaStateCounts[vLoop, 1]
		{
			vRet = vLoop
		}
		vLoop += 1
	}

	Return(vRet)

End Function


// Function to find an empty slot in the state totals array
Function FindStateSlot() As Int
var vLoop as Int
var vCount as Int
var vRet as Int

	vRet = 0
	vLoop = 1
	While((vLoop <= 100) And (vRet = 0))
	{
		vCount = @ToNumber(gvaStateCounts[vLoop, 2])
		If (gvaStateCounts[vLoop, 1] = "") And (vCount = 0)
		{
			vRet = vLoop
		}
		vLoop += 1
	}

	Return(vRet)

End Function


// Subroutine to write summary to WriteLn Window
Subroutine WriteSummary()
var vLoop as Int
var vCount as Int
var vTotal as Int

	WriteLn("Violation: " + gvViolation)
	For vLoop = 1 To 100
		vCount = @ToNumber(gvaStateCounts[vLoop, 2])
		If (gvaStateCounts[vLoop, 1] <> "") Or (vCount > 0)
		{
			WriteLn(gvaStateCounts[vLoop, 1] + Tab(2) + @Str(vCount))
			vTotal += vCount
		}
	Next
	WriteLn("Total Violations for " + gvViolation + ": " + @Str(vTotal))
	WriteLn("----------")
	gvGrandTotal += vTotal

End Subroutine


	// Initialize variables to values in first record
	gvState = GetState(City0)
	gvViolation = Violation Code

	// Initialize Summary
	gvGrandTotal = 0

	// Initialize State Counts
	ClearCounts()
	gvaStateCounts[1, 1] = gvState
	gvaStateCounts[1, 2] = "0"






** PROGRAMMING SECTION: [Courier] [Mass Update] **
var vCurState as String
var vCurViolation as String
var vPos as Int

	// Initialize variables to values in current record
	vCurState = GetState(City0)
	vCurViolation = Violation Code

	If vCurViolation <> gvViolation
	{
		WriteSummary()
		ClearCounts()
		gvaStateCounts[1, 1] = vCurState
		gvaStateCounts[1, 2] = "0"
		gvViolation = vCurViolation
	}

	vPos = FindState(vCurState)
	If vPos > 0
	{
		gvaStateCounts[vPos, 2] = @Str(@ToNumber(gvaStateCounts[vPos, 2]) + 1)
	}
	Else
	{
		vPos = FindStateSlot()
		If vPos > 0
		{
			gvaStateCounts[vPos, 1] = vCurState
			gvaStateCounts[vPos, 2] = @Str(@ToNumber(gvaStateCounts[vPos, 2]) + 1)
		}
	}

	If @ResultSetCurrentPosition() = @ResultSetTotal()
	{
		WriteSummary()
		WriteLn("---------------------------------")
		WriteLn("---------------------------------")
		WriteLn("Total Violations: " + @Str(gvGrandTotal))
	} 

Blightman,

The code I provided is a Mass Update, not regular Form programming. Part of it goes in Global Code for the Mass Update, the other part can go in any other element.

The part headed with
** PROGRAMMING SECTION: [ GLOBAL CODE ] [] **
goes in Global Code. Do not include the header line itself.

The part headed with
** PROGRAMMING SECTION: [Courier] [Mass Update] **
goes in any other element. Do not include the header line itself.

Remember to sort your retrieved records by violation code, or it won't work properly.

Just out of curiosity, and for my own reference in the future, if I wanted option #1 (State is primary), if I were to take the code you sent and change the references to "state" and make them "violation," would I get the the results that I would want?