Pulling records from summed fields that are =<

[shanmac]

Newbie Access user here.....

I have a table that contains 8 fields (ID#, grade 1, grade 2, grade 3, grade 4 etc...). The grade fields are either blank or contain a 'Y'. I want to find all ID#s that have 6 or more 'Y' across all the grade fields. For example, records that have 'Y' in grade 1, 2, 3, 5, 6, 7 or grade 1,2,3,4,5,6 or any combination that adds up to 6 or greater should be pulled.

How do I do this?

 

[Norie]

The first thing you should probably consider is redesign your table.

You don't need a seperate field for each grade.

The table should only really have 3 fields.

ID#

GradeValue

GradeClass(Type?)

 

[shanmac]

Yes I see your point, unfortunately, this is the original form how the data was given to me. Plus I don't know how to make it all one field as you suggest.

For now I'll pull the data into Excel, do it from there and then re-import into Access. Crazy, I know.

 

[VicRauch]

While still in Access, you could do this:
This would be a column in a query: (finish all the grades...)

GradeCount: fnGradeCount([grade 1], [grade 2], etc.....)

Then the function

Function fnGradeCount(g1, g2, g3, .....)
dim mGradeCount as integer
   If g1 = "Y" then mGradeCount = mGradeCount + 1	
   If g2 = "Y" then mGradeCount = mGradeCount + 1	
   If g3 = "Y" then mGradeCount = mGradeCount + 1	
   If g4 = "Y" then mGradeCount = mGradeCount + 1	
   If g5 = "Y" then mGradeCount = mGradeCount + 1	
   If g6 = "Y" then mGradeCount = mGradeCount + 1	
   If g7 = "Y" then mGradeCount = mGradeCount + 1	
   If g8 = "Y" then mGradeCount = mGradeCount + 1	
fnGradeCount = mGradeCount
End Function

HTH

 

[giacomo]

Yes I see your point, unfortunately, this is the original form how the data was given to me. Plus I don't know how to make it all one field as you suggest.

See this post for some code that may help you to normalize your table

Also, I noticed you posted this on the utteraccess site too and the feedback was pretty much the same. You need to work on the structure of your tables before you go any further with this. If not it will only cause you more headaches in the future. Check out the links in may signature for more information about normalization.

hth,
Giacomo

 

[shanmac]

Thanks for all the help posted here.

I will try the suggestions out. The biggest difficulty for me is that I have so little experience using Access that I don't even know how to implement some of these solutions.