Query criteria looking to multiple columns (1 Viewer)

Hallo, I am Max and I am new to this forum. Although I have been reading a lot.

My experience with acces is limited but I am trying to learn.


I have made an Acces Table with 14 columns. It is a database where exams results are stored. The first three columns consist of Name, Date, Subject. Column 4 to 14 are questions where a mark between 1 and 5 is submitted.
I made a form so that other people can easily run a query with their personal criteria. For example users can filter on name, place and a range of data.
Now I have added a Option Box with radio buttons so that users can select a mark from 1 to 5.
I want to make sure the Query now filters all the rows in the table that contain a mark in the colums 4 to 14 that is below the users input.
For example the user selects the radiobutton "3", I want the query to return all the rows that have a mark below 3 in either of the 11 colums.
I can make this work by writing OR <[Forms]![Opvragen]![OptMark] in the SQL for the columns 4 to 14. The problem is now that the filter for date, name and subject doesnt work any more because of the OR statement.


How do I solve this? I hope I have explained the problem well enough and help will be much appreciated.


Greetings Max

I think your structure is wrong.?
You should have the the questions and answers in another table, then this issue would be very simple.

What happens if the questions go up to 20?

HTH

Hallo Gasman,


The table only contains the mark that the students received per question.
So the first column is the ID, second column; Name student, 3th column: Date the exam was made. 4th column: subject of the exam. 5th colum; mark for the first question (mark between 1 to 5). 6th column: mark for the second question. 7th column; enz.. until the 15th column with the mark for question nr 10.


The reason for the query is that the user wants to search for example all the tests that have at least one mark below 3 for a certain subject and student.



I hope I made the problem more clear,


Max

You have, but I'm afraid Gasman's answer still applies, you have "committed spreadsheet".

You data should be stored vertically - one question per line with just its corresponding data. Not horizontally, as per a spreadsheet.

With the correct data layout, if you add a question or remove one your data and queries etc still work.

Allright, looks like I have to do some fundamental changes. I am going to look into that. Thank you for the help

Not really as it was clear in the first place.
You have however further confirmed the point already made by Gasman.
Your structure is for a spreadsheet not a database

So either use Excel where this structure is fine
Or use Access as it is designed to be used and avoid duplication of data.
To do so, I suggest using 3 tables

tblStudents
StudentID (PK)
LastName
FirstName etc

tblExams
ExamID (PK)
Subject
ExamDate etc


tblExamMarks
ExamMarkID (PK)
ExamID (FK)
StudentID (FK)
QuestionNumber
Mark

Then to analyse the data, you join tables together with suitable queries, add the marks, calculate averages etc