Counting based on criteria (1 Viewer)

[Tomrobo]

Hello,

I have a table with 6 fields I would like to count.

Choice1, Choice2, Choice3, Choice4, Choice5 and Choice6

The table stores choices that a user may wish to enter. The choices are selected from another table called 'choices'. The user enters in a choice using 6 combo boxes in a form.

I was wondering if it was at all possible to have some VBA code that (perhaps) in a msgbox would show all the unique choices and the number of times that the choice has appeared in the table.

The same choice can appear in all of the 6 fields. e.g. 'Garlic Bread' could be selected and stored in Choice5 on one users record and Choice6 on the next.

I'm really after a way to count the number of times each unique entry is selected.

Many thanks

 

[vbaInet]

There are several ways I can think of:

1. Use a report to list out all the records, hide the detail section so it doesn't show, put a subreport on the Footer section that will list all possible choices, create 6 alias fields which will act as column headers for the count and on each of them you have something like:

CountChoice1: Count(IIF([Reports]![NameOfReport]![Choice1] = [Field], 1, Null))

Where field could be Garlic Bread or any of the possible choices.
2. Use a form and perform the same procedure as explained above
3. Create a query to list out all the possible choices including all the records of customer choices. Set the query to a Totals query. The 6 alias fields which will act as column headers will now look like:

CountChoice1: Count(IIF([Choice1] = [Field], 1, Null))

Repeat that 6 times under each choice.
4. Use a query to list out all the possible choices ONLY and for the alias fields use a DCount() function instead.

No. 3 would be your best bet.

 

[Tomrobo]

Many thanks for the informative post!

I will have a try with your helpful solution and i'll post my progress!

Thanks again

T

 

[Tomrobo]

Hi,

I've attempted number 3 from your suggestions. However I don't seem to be lucky. I'm quite a novice so the likelihood is i've missed a key step along the way!

The main objective is to somehow have a look at all six choices add together the unique choices and the ammount of times they appear in the table. Then display these in a message box or even a graph!

I have attached a screenshot of how the table looks.

Also This was the sort of code initially had a look at. As you can see it won't function to what i need as the choices can change.

Private Sub Command0_Click()
    Dim db As Database
    Dim ChoiceCount As DAO.Recordset
    Dim count1 As Integer
    Dim count2 As Integer
    Dim count3 As Integer

    count1 = 0
    count2 = 0
    count3 = 0
    Set db = CurrentDb
    Set ChoiceCount = db.OpenRecordset("StudentChoices", dbOpenDynaset)
    copyofdiscounts.MoveFirst
    Do Until ChoiceCount.EOF
        If ChoiceCount!**CHOICE1?** = 1 Then
            count1 = count1 + 1
        End If
        If ChoiceCount!**CHOICE2?** = 1 Then
            count2 = count2 + 1
        End If
        copyofdiscounts.MoveNext
    Loop
    MsgBox Choice1 & " Appears " & count1 & " Times."
            ......**etc


End Sub

 

[vbaInet]

Let me see a screenshot of what you've tried. A screenshot of the query in design view. Expand one of the Count fields too.

 

[Tomrobo]

Thanks, i've managed to sort out the querey now. Using the

CountChoice1: Count(IIF([Choice1] = [Field], 1, Null))

given above. As a complete beginner I was just being very slow!

Now I have by query working (see attached), I was wondering how to then use this in say a form to show all the choices and the amount (count) of times they have been chosen?


Thanks

T

 

[DCrake]

If this was my problem I would have resolved it using a union query

Select Choice1 as Choice from Table
Union all
Select Choice2 as Choice from Table
Union all
Select Choice3 as Choice from Table
etc

then simply do a group by choice and count choice

 

[vbaInet]

Good to know!

What do you mean by your second request?

By the way, can I see a sample of records that's returned by your query? Like a screenshot.

 

[Tomrobo]

Sure. Please find attached my query results.

From this query I would would like on a form, to list all of the module names and then list the amount of times they have been chosen.

I've managed so far to use:

LBLPopularModule.Caption = DLookup("ModuleName", "QRYModuleChoices", (DMax("Total", "QRYModuleChoices")))

However this has a number of pitfalls:
1. Only shows the name of the most popular module and not the value.
2. If there are 2 modules with the same amount of choices, it will only show 1.
3.Only shows one module as i've set it for the max, ideally showing all and then perhaps highlighting the most popular and least popular in the same way.

I really appreciate the help i've been receiving guys. Many thanks!

 

[vbaInet]

From this query I would would like on a form, to list all of the module names and then list the amount of times they have been chosen.

You have the query, use that as the record source of your form.

3.Only shows one module as i've set it for the max, ideally showing all and then perhaps highlighting the most popular and least popular in the same way.

Select the Total control on the form, click Conditional Formatting in the ribbon or menubar and use the DMax() you wrote in there. It will be:

Field Value Is Equal To DMax("Total", "QRYModuleChoices")

There's really no point highlighting the least popular because there are far too many zeros there. If you still want to, just add another condition with DMin().