Number of values matching criteria across a row

[jenp]

Dear all:

I can't seem to figure this one out on my own; I'm trying to create a query that counts the number of values in fields across a row that match certain criteria. For example, the data I'm querying looks like:

ID     01-1   01-2   02-1   02-2   03-1   03-2   04-1   04-2
1       85     76     68     72     67     63     74     73
2       32     34     70     65     67     80     68     70

and I need to find out, for example, how many numbers for each id are greater than or equal to 70 but less than 80. I've tried using DCount, but it gives me the number overall that match the criteria instead of the number per row (so I'm sure I'm doing it wrong). I've tried using a crosstab query, but I can't suss out how they actually work when you've got more than one column of data that you need to work with unless I put ID as column headers and the other fields as row headers... that would make my query about 1500 columns wide and 10 rows long, though, which just seems wrong... ?

In a slightly different approach, I've done another query on the data that makes it look like:

ID    Number      First     Second     
1      01           85         76     
1      02           68         72     
1      03           67         63     
1      04           74         73
2      01           32         34     
2      02           70         65     
2      03           67         80     
2      04           68         70

But again, I keep ending up with silly answers (like the count for the entire set of data, rather than just per ID). I'm *sure* I'm just not quite getting how DCount works... and it's possible I'm trying to use it for something for which it wasn't designed. Any alternative examples would be most helpful.

I'm not sure how to compress the data any further. I could, technically, get it down to two columns (ID and <number to query>), but the only way I can see of doing it is to create a temporary table for the first column and append the second column's data to it, which I'd have to do every time any of the numbers changed.

I would prefer to use the first approach, as that query is useful in other calculations and the second is a bit of a faff (though not too bad, just a union query). Anyway, I'm just stuck and instead of continually banging my head against the wall, I thought I'd ask you kind folk for help.

-Jen

 

[neileg]

You're having problems because your design is not normalised. Instead of having this data as columns in a table it should be separate records in a related table as you have set out in your second example. You can run an aggregate query on this using >=70 and <80 as criteria and grouping on ID and using Count as the aggregate function.

 

[jenp]

I suspected the second query would be more useful for this particular bit, but I couldn't get *anything* to work properly, which is why I gave both formats.

Thanks for your advice, anyway, I'll give it a go. I'm sure I was just missing a bit out when I tried before.

-Jen

 

[jenp]

Mm, no, sorry - it's still not working for me no matter what I try. I keep getting absolutely nothing from the query.

Could you please provide a concrete example of what you mean using the format of the second set of data I posted earlier? If I can see exactly what you mean, I should be able to figure out how to do the rest of what I need to do...

Sorry for the trouble, but I do appreciate your help.

-Jen

 

[neileg]

I don't really have an understanding of what the data means so it's a bit difficult to advise.

 

[jenp]

Sorry, Neil - guess that would have been helpful. One of those days! Basically, it's for an exam markbook. There are 13 possible exams, each student sits up to 7 and receives three marks per exam; 1 mark from each of two examiners and a mark they agree on.

One of the ways in which a student's class is determined is based on the number of interim marks they receive which fall into that particular class's criteria. So, if they don't meet the criteria with their agreed marks, we can call them a 'borderline' and have a look at their 14 interim marks. If they meet a second (and possibly third) set of criteria, then they get the higher mark.

So, what I had in my first example was basically student_id and all of the interim marks they got for each of their exams - all in a row. The second example was student_id, paper_no, first_mark, second_mark for each paper.

Does that help at all?

Thanks again for your help.

 

[jenp]

Thanks again for all your help, Neil - when I tried to use the method you gave me, it ended up trying to count the number of students who had more than 70 marks instead of how many of their marks were >= 70. Like I said, I'm sure it was just something I was doing wrong.

Anyway, I've been able to sort this problem with help from a colleague here at the university. Essentially, the table ends up with the ID in one column and the mark in the other and I plugged the details into a bit of SQL using a subquery (which I'd never known one could do... could prove quite useful!):

SELECT temp.student_id, Count(temp.Mark) AS Firsts
FROM [select student_id, Mark from tmp_all_interim_marks where Mark >= 70]. AS temp
GROUP BY temp.student_id;

This does what I need it to do.

-Jen

 

[neileg]

Great, please you got it sorted. (been a bit busy so couldn't get back to you)