Choose or Array?

[DaveMac]

In the detail of a report there are between 20 and 4500 records that will be 99% one number in the Destination field however every now and then I get some left over data that lands in the Destination Field and screws up my report. ? How would you look at numbers between 0 and 6 and determine which one shows up the most? All I want is the Value. I started down this road.

Count(0)
Count(1)
Count(2)
Count(3)
Count(4)
Count(5)
Count(6)

Now What!

 

[hwkranger]

make a query for "duplicate information" and you can get it.

or you can write a use Select Case month

where

select case ***Your field***
case 0
count(0) = count(0)+1
case 1
count(1) = count(1)+1
etc.. etc..
end select

you put all that inside a loop through your records.. I can give you exact code for that if you want, but.. the duplicate query would be simpler in my humble opinion. Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[DaveMac]

Thanks for the reply but how do I evaluate which number shows up the most. Does that select the case that shows up the most?

 

[hwkranger]

you'd have to then find which one is the largest.

add something like this after the select:

dim num as integer
dim i as integer
i = 1
num = 0

for i to 6 step 1
if num<count(i) then
num = count
next i
msgbox(&quot;most frequent is: &quot; & num)




Have a nice day
Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[cmmrfrds]

Since there are on 7 values, you could make a query that inverts the values into columns. Untested but the idea will work.

SELECT Count(IIf(([aCode]=0),0,0)) AS cnt0, Count(IIf(([aCode]=1),1,0)) AS cnt1, Count(IIf(([aCode]=2),2,0)) AS cnt2,
Count(IIf(([aCode]=3),3,0)) AS cnt3, Count(IIf(([aCode]=4),4,0)) AS cnt41, Count(IIf(([aCode]=5),5,0)) AS cnt5,
Count(IIf(([aCode]=6),6,0)) AS cnt6
FROM TestTable2

 

[WayneRyan]

Dave,

Select Distinct(tblColumn), count(*)
From YourTable
Group by tblColumn;

hth,
Wayne