Counting across multiple fields

[weekend00]

Maybe we can try this way:



1 create query:
MasterQry

 
SELECT MasterTable.[App Abx1] FROM MasterTable union all 
SELECT MasterTable.[App Abx2] FROM MasterTable UNION ALL 
SELECT MasterTable.[App Abx3] FROM MasterTable;

2 create query:
resultQry

SELECT MasterQry.[App Abx1], Count(MasterQry.[App Abx1]) AS [CountOfApp Abx1]
FROM MasterQry
GROUP BY MasterQry.[App Abx1];

run the second query to get result.

[ajetrumpet]

you don't want field summations, you want the ocurrances of every distinct values in your table.

with 4 fields in your table, 1st being ID, the rest being the areas to search, run this on your table:

dim db as dao.database
dim rs as dao.recordset
dim vals() as string
dim valcounts() long
dim i as long
dim j as long
dim k as long

i=0

ReDim Preserve vals(i)
ReDim Preserve valcounts(i)

i=1

set db=currentdb
set rs=db.openrecordset("master database table")

with rs
   .movelast
   .movefirst

      do until .eof

         for j = 1 to 3
            if not i = 0 then
               for k = lbound(vals) to ubound(vals)
                  if vals(k) = .fields(j) 'VALUE ALREADY RECORDED
                     valcounts(k) = valcounts(k) + 1
                        goto NextTask
                  end if
               next k

                  ReDim Preserve vals(i)
                  ReDim Preserve valcounts(i) 'MAKE NEW ARRAY ELEMENTS
                     vals(i) = .fields(j)
                     valcounts(i) = valcounts(i) + 1
                        i = i + 1

            else
               vals(i) = .fields(j)
               valcounts(i) = 1
            
            end if

NextTask:
         next j
                       .movenext
      loop

end with

for k = lbound(vals) to ubound(vals)
   debug.print "Value " & """" & vals(k) & """" & _
                   " appears " & cstr(valcounts(k)) & " times."
next k

rs.close
db.close

set rs=nothing
set db=nothing

I did not test this, so run it if you want to and if it errors let me know where.

[shak2]

Hey:

The following two strings are marked in red:
dim valcounts() long

and this one

if vals(k) = .fields(j) 'VALUE ALREADY RECORDED

[ajetrumpet]

Hey:

The following two strings are marked in red:
dim valcounts() long

and this one

if vals(k) = .fields(j) 'VALUE ALREADY RECORDED

sorry...

here are the correct lines:

Dim valcounts() As Long
If vals(k) = .Fields(j) Then 'VALUE ALREADY RECORDED

[shak2]

Hey:

Thanks a million. Now stupid question ( I know I know, horrible). How do I execute/run it?

[ajetrumpet]

Hey:

Thanks a million. Now stupid question ( I know I know, horrible). How do I execute/run it?

CLICK INSIDE IT AND PRESS F5

i'm takin off. see ya later chief.

[shak2]

ok It didn't work, but thnx!

[weekend00]

Please try my queries.

[shak2]

Maybe we can try this way:

1 create query:
MasterQry

 
SELECT MasterTable.[App Abx1] FROM MasterTable union all 
SELECT MasterTable.[App Abx2] FROM MasterTable UNION ALL 
SELECT MasterTable.[App Abx3] FROM MasterTable;

2 create query:
resultQry

SELECT MasterQry.[App Abx1], Count(MasterQry.[App Abx1]) AS [CountOfApp Abx1]
FROM MasterQry
GROUP BY MasterQry.[App Abx1];

run the second query to get result.

UMM..... Doesnt work

[weekend00]

I had tested them in my computer.

Please check the table name and field names.

[shak2]

HATS OFF TO YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! AWESOME! IT WORKED. WANNA HELP ME WITH SOMETHING ELSE? I want to do the same thing, except this time all my selections (so all thirty of those abx's are fields - yeah I know 30 fields). But, What I want now is to count each field seperately, but only pull up those that are selected since datatype is now yes/no for each field.

When I try to do the query in access, or using the wizard, i pull up a master that just gives me all of them. The problem is that now I have to click one field at a time and change it to 'is selected'. if I do that for all fields at the same time i get nothing, why? because it counts as field 1 is yes and field 2 is yes and field 3 is yes, and that is not how i want it.

here's the sql code for the master:

SELECT [Master Database Table].Date, [Master Database Table].[Record Number], [Master Database Table].Acyclovir, [Master Database Table].Albendazole, [Master Database Table].Amikacin, [Master Database Table].[Amphotericin B deoxycholate], [Master Database Table].[Amphotericin B lipid complex], [Master Database Table].Azithromycin, [Master Database Table].Aztreonam, [Master Database Table].Cefepime, [Master Database Table].Cefotaxime, [Master Database Table].Ceftriaxone, [Master Database Table].Chloramphenicol, [Master Database Table].Ciprofloxacin, [Master Database Table].Clarithromycin, [Master Database Table].Daptomycin, [Master Database Table].Fluconazole, [Master Database Table].Flucytosine, [Master Database Table].Foscarnet, [Master Database Table].Ganciclovir, [Master Database Table].Imipenem, [Master Database Table].Itraconazole, [Master Database Table].Linezolid, [Master Database Table].Meropenem, [Master Database Table].Micafungin, [Master Database Table].Moxifloxacin, [Master Database Table].Pentamidine, [Master Database Table].[Piperacillin-Tazobactam], [Master Database Table].[Polymyxin B], [Master Database Table].Tigecycline, [Master Database Table].[Vancomycin IV], [Master Database Table].[Vancomycin PO], [Master Database Table].[Vancomycin PR], [Master Database Table].Voriconazole, [Master Database Table].Oseltamivir, [Master Database Table].Rimantadine, [Master Database Table].[Other Agent]
FROM [Master Database Table];

[weekend00]

I tried to understand, but I am still not very clear what you want. would you again give an example of the table and describe again you goal?

[weekend00]

So you want to count how many "YES" for each field, right?
it's much easier to do in Excel by using COUNTIF.

[shak2]

Yes, Exactly!

[weekend00]

Do it in Excel using COUNTIF.