Counting with multiple criteria in a field

[pfunk]

I am slightly capable of using Access but have come to as stand still at this current time and any help would be greatly appreciated. I have posted this in another forum and all the responces I have gotten have been about trying to "Normalize" my data entry.


I have a query that breaks down all the papers that come in by a time frame. From there I am trying to see which topics are discussed within these papers. The issue at hand is that one paper can touch on one or many of the 32 topics to chose from. What I am trying to create is a query that shows each student as a column and then the Topics going across the top and a point being given if each student discusses that topic.

I have 3 tables:
Tables Are: (TableName - Field1 - Field2 - Field3) (The Primary Keys are the ID numbers which are AutoNumbered)
Topics - ID - ShortTitle - Meaning
Lists all the topics that will be an option

Students - ID - Student
List of all students (No there are no repitions of names and won't be)

Papers - Student(Selected from dropdown) - DateRecieved - Topic(More than one Topic ShortTitle will be in this field)
This is the Data Entry Field that is tied to a form where the user can input the data.


So from the pictures I want the end results to be

StudentName - ARM - WAR - DPB

Sam - 1 - 1 - 1
Shawn - 0 - 0 - 0
Sarah - 0 - 1 - 0
Cindy - 0 - 2 - 1
Thomas - 1 - 1 - 0
James - 0 - 0 - 0

I know it is bad to have multiple criteria in a field but with 32 items that answer this I don't know another way. I also have a field which has 122 items that answer it so that is even worse.

[offie]

In Papers, how did you get Topics? Did you type it in? Was is it a function/calculation?

What I would do is to make 3 checkbox fields in Papers. This would make the final product a super simple count query

If the issue is that you already did a bunch of work and don't want to do it again you're gonna have to come up with something tricky using a combination of iif() or righ() and left(), maybe even getting criteria to match or something?
Maybe something like IIF([Papers].[Topics] is like "*" & "ARM" & "*", True, False) I just made this up so it might not work, but try it out, you'd have to make 3 of these, switching out ARM for the other 2

Also: In Papers, I would put the Student ID in, not the name.

[pfunk]

In Papers, how did you get Topics? Did you type it in? Was is it a function/calculation?

What I would do is to make 3 checkbox fields in Papers. This would make the final product a super simple count query

If the issue is that you already did a bunch of work and don't want to do it again you're gonna have to come up with something tricky using a combination of iif() or righ() and left(), maybe even getting criteria to match or something?
Maybe something like IIF([Papers].[Topics] is like "*" & "ARM" & "*", True, False) I just made this up so it might not work, but try it out, you'd have to make 3 of these, switching out ARM for the other 2

Also: In Papers, I would put the Student ID in, not the name.

They are copied from the paper submitted with these codes on them, so basically they are typed. I would love to use checkboxes however their are 32 different Topics and the data is entered from a Form onto Papers so then I would need 32 differnet checkboxes which looks unfriendly. Also like I said I will be using this code in the same manner with a different field but this one has 122 options and checkboxes is way out of the question there.

[June7]

This is extremely inefficient and hard to manage. Is this already a fully developed db with a lot of records in this structure? If not, suggest you redesign now before getting in any deeper. Otherwise, there are ways to get the structure fixed.

Multi-value field would be preferable to the comma separated values string, and I don't even like multi-value fields.

[pfunk]

This is extremely inefficient and hard to manage. Is this already a fully developed db with a lot of records in this structure? If not, suggest you redesign now before getting in any deeper. Otherwise, there are ways to get the structure fixed.

Multi-value field would be preferable to the comma separated values string, and I don't even like multi-value fields.

I am building it so I can change it at will. It already has data in it yes cause I have to keep stats. I am almost finished with it I am just missing this portion and another which I am sure I will need help with. What would you recomend I do then since one paper can have more than one topic? This is just an example due to restrictions on the data that is in it I must build a "fake" db to get help

[June7]

Options:

1. a child table to associate topics to paper:

tblStudentPapers
PaperID
StudentID
DateReceived

tblPapersTopics
PaperID
TopicID

2. multi-value field in tblStudentPapers

[pfunk]

I think I am understanding. But since I want to have a count to give each student credit wouldn't I want the query to make it be just

StudentTopicQry
Student
Topic

Where it breaks down the multiple entries into one field into its own record and from there do a count?

[June7]

That can be accomplished with either approach, probably easier with option 1.

Should understand multi-value fields before you decide http://office.microsoft.com/en-us/ac...001233722.aspx

[pfunk]

That can be accomplished with either approach, probably easier with option 1.

Should understand multi-value fields before you decide http://office.microsoft.com/en-us/ac...001233722.aspx

I saw this yesterday evening right before I left for work but couldn't find the link again. Thanks for your help I think this is the route I will end up using!

[pfunk]

Okay, saw you marked it as Solved but not completely yet.
So now I have a Query: StudentTopicQry which has two fields: Student and Paper.Topic.Value
What I am trying to do from here is Have each student even the ones not in this Qry (RIGHT JOIN Students Table I think) show with each Topic (Name not a Value number) going across the top and the count show for each Student per topic.

[June7]

I haven't used multi-value fields much but try:

Join students table into the StudentTopQry, join that query to topics table to get the ShortTitle (alternative is to save the ShortTitle in the multi-value field instead of ID and eliminate this join step), then do a CROSSTAB query.