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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,026
Messages
5,856,928
Members
431,838
Latest member
mydayisgood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top