VB Array & Access

[Sabian]

I am trying to retrieve records from an access database and load them into
an array in VB (unless there is a better way). Any help would be greatly
appreciated. Here is exactly what I am trying to do:

I have five fields in the database that each contain numbers 1 - 35. Everyday,
a new series of five numbers is added to the database from 1 - 35. I want
to bring those records into VB6, sort them, and then show the 10 numbers
entered the mos in the database in text boxes.

Example:

Day 1: 2,3,4,5,6
Day 2: 2,3,34,13,23
Day 3: 6,9,11,15,28
Day 4: 2,3,4,7,9

I want to show in the text boxes that "2" was selected 3 times, "3" was selected
3 times, "9" was selected twice... and so on for the top 10 numbers selected
the most.

Any idea's?

[Douglas J. Steele]

Anytime you have a repeating group (five fields, each storing essentially
the same thing) in a table, it's a good sign that the design hasn't been
properly normalized.

In this case, I'd advise storing your 5 fields as 5 rows instead.

Your new table would have something like:

WhatDay, WhichNumber, WhatValue

Day 1, Number1, 2
Day 1, Number2, 3
Day 1, Number3, 4
Day 1, Number4, 5
Day 1, Number5, 6
Day 2, Number1, 2
Day 2, Number2, 3
Day 2, Number3, 34
Day 2, Number4, 13
Day 2, Number5, 23

etc.

Now, it's a simple query to figure out how many times each of the top 10
numbers were selected. Something along the lines of the following untested
air-code:

SELECT TOP 10 WhatValue, Count(*) FROM MyTable ORDER BY Count(*) DESC

You can still retrieve your numbers in the same format as you currently are
by using a Crosstab query.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


"Sabian" <sabian@intelogic.net> wrote in message
news:3e29dd43$1@tnews.web.devx.com...
>
> I am trying to retrieve records from an access database and load them into
> an array in VB (unless there is a better way). Any help would be greatly
> appreciated. Here is exactly what I am trying to do:
>
> I have five fields in the database that each contain numbers 1 - 35.
Everyday,
> a new series of five numbers is added to the database from 1 - 35. I want
> to bring those records into VB6, sort them, and then show the 10 numbers
> entered the mos in the database in text boxes.
>
> Example:
>
> Day 1: 2,3,4,5,6
> Day 2: 2,3,34,13,23
> Day 3: 6,9,11,15,28
> Day 4: 2,3,4,7,9
>
> I want to show in the text boxes that "2" was selected 3 times, "3" was
selected
> 3 times, "9" was selected twice... and so on for the top 10 numbers
selected
> the most.
>
> Any idea's?