Pulling records from summed fields that are =<

shanmac

New Member
Joined
Oct 11, 2006
Messages
7
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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,927
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 11, 2006
Messages
7
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
While still in Access, you could do this:
This would be a column in a query: (finish all the grades...)
Code:
GradeCount: fnGradeCount([grade 1], [grade 2], etc.....)
Then the function
Code:
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

New Member
Joined
Oct 11, 2006
Messages
7
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. :oops:
 

Watch MrExcel Video

Forum statistics

Threads
1,114,381
Messages
5,547,608
Members
410,802
Latest member
DataMgmtAnalyst7
Top