Query that returns number of times an item appears alongside others in the row

[greyscale]

Hi,



I have a strange request. We recently asked phone staff to press a radio button on an access form (access97) each time they received a certain type of enquiry (100 enquiry types). They were able to press multiple enquiry types for each call.

We now need to report on the number of times that enquiry was selected as a single enquiry or as part of other enquiries

How do I write a query that would return the number of times a field appears on its own, as part of a double query, triple query etc. We need to account for the fact that up to 6 enquiries can be included on the one call/row. I have attached a small sample of the database, replacing sensitive data with generic field names, and an example of the report layout.

Thanks in advance

[ajetrumpet]

your data needs to be normalized. 104 fields is not normal really. plus, this would done MUCH easier in excel with the setup that you currently have. I would first recommend moving it there because doing any caluclations horizontally like this in access is next to impossible.

however, if you feel like using arrays you probably can. this might be the tough way to do it, but I did an experiment with your data and I came up with this query:

SELECT FIELDLIST.[FIELDNAME], 

GETQRYNUM([FIELDNAME],"SINGLES") AS SINGLES, 

GETQRYNUM([FIELDNAME],"DOUBLES") AS DOUBLES, 

GETQRYNUM([FIELDNAME],"TRIPLES") AS TRIPLES, 

GETQRYNUM([FIELDNAME],"QUADS") AS QUADS, 

GETQRYNUM([FIELDNAME],"QUINS") AS QUINS, 

GETQRYNUM([FIELDNAME],"SEXTUPLES") AS SEXTUPLES

FROM FIELDLIST;

I did this on a table that I manually created and populated with automation (one per rec). Then I took your imported excel data in an access table called TESTDATA and called this function from the query's SQL:

Option Compare Database
Option Explicit
Option Base 1

Public Function GETQRYNUM(FIELDNAME As String, VARTYPE As String) As Long

Dim CTR As Long 'VAR TO LOOP THROUGH FIELD INDEX
Dim QRYCOUNT As Long 'NUMBER OF QUERIES MADE IN A RECORD
Dim QRYMATCH As Boolean
Dim FLDINDEX As Long 'COLUMN INDEX COMES FROM DIGIT IN FIELDNAME
Dim DB As DAO.Database
Dim RS As DAO.Recordset 'TABLE
Dim QUERYTYPES(6) As String 'ARRAY FOR MATCHING QUERY TYPES (SINGLE, DOUBLE, TRIPLE, ETC...)

QUERYTYPES(1) = "SINGLES"
QUERYTYPES(2) = "DOUBLES"
QUERYTYPES(3) = "TRIPLES"
QUERYTYPES(4) = "QUADS"
QUERYTYPES(5) = "QUINS"
QUERYTYPES(6) = "SEXTUPLES"

GETQRYNUM = 0 'THE COUNT IS 0 TO START

Set DB = CurrentDb
Set RS = DB.OpenRecordset("TESTDATA", dbOpenDynaset)

    With RS
    
        'OPEN THE ENTIRE TABLE
        .MoveLast
        .MoveFirst
        
            Do Until .EOF 'LOOP THE RECORDS
            
                QRYCOUNT = 0
                QRYMATCH = False
                FLDINDEX = CLng(Right(FIELDNAME, Len(FIELDNAME) - InStr(FIELDNAME, " ")))
                
                    If .FIELDS(FLDINDEX).Value = "-1" Then 'CHECK FOR CRITERIA SATISFIED
                        
                        For CTR = 4 To 104 'COUNT QUERIES IN THE CALL HERE
                            If .FIELDS(CTR).Value = "-1" Then
                                QRYCOUNT = QRYCOUNT + 1
                            End If
                        Next CTR
                    
                    End If
                
                    If QRYCOUNT > 0 Then
                        If QUERYTYPES(QRYCOUNT) = VARTYPE Then
                            QRYMATCH = True 'TYPE OF QUERY WE'RE ASKING FOR MATCHES NUMBER FOUND IN REC
                            GETQRYNUM = GETQRYNUM + 1
                        End If
                    End If
                    
                        .MoveNext
                        
                Loop
                
            .Close
            
        End With

Set RS = Nothing
Set DB = Nothing
  
End Function

Here is the result I saw (which takes way too long to load because massive calculations have to run before display):



Certainly there is a better way for you to do this. Your criteria fields in a seperate table would be a wonderful place for you to start. That is how Access is meant to provide benefits over Excel. Without separating data like this you might as well just stick with the spreadsheet.

[greyscale]

Adam, thank you so much for investigating this so thoroughly for me. While it seems it can be done, i will take your advice and split the criteria fields into separate tables and see how we get on with that.

Thanks again. I would never have got there on my own

[ajetrumpet]

No problem. It was a good question to ask. Interesting one too. Good luck with it.