Do while loop

[Haider Majeed]

I have a table named "Marks" contains marks for several topics for each student. I need a code to insert the topics with marks less than 50 in a filled named "fail_topics" corresponding to each student.

I appreciate your support

 

[theDBguy]

Hi. Welcome to AWF! If that's an image of your table structure, then you may have a non normalized design. If so, you might consider normalizing your table structure first, if it's not too late.

 

[Haider Majeed]

Hi. Welcome to AWF! If that's an image of your table structure, then you may have a non normalized design. If so, you might consider normalizing your table structure first, if it's not too late.

is this valuable?

 

[bob fitz]

is this valuable?

The picture shows a table which does not look normalised. Your first step would be to normalise your db as already recommended by theDBguy.

If you do not understand what normalising a db is about, have a quick search of this forum or google.

 

[arnelgp]

like said, you need to normalized your table, eg:

tbl_student
id (autonumber)
student_name (short text)

tbl_topic
id (auto)
topic (short text)

tbl_scores
s_id (long, id of tbl_student)
t_id (long, id of topic)
score (number)

'=============
if you are unable to do the above you can create a query and a function.

update marks set Fail_Topics = fnkFailed([s_name])

add the code in a Module

public function fnkFailed(s_name as variant) As string
dim s_return as string
If Trim(s_name & "")="" then exit function
with currentdb.openrecordset("select * from marks where st_name = '" & s_name & "'")
    	if not (.bof and .eof) then 
		.movefirst

		if nz(!topic1, 0) < 50 then _
			s_return = s_return & "Topic1, "
		if nz(!topic2, 0) < 50 then _
			s_return = s_return & "Topic2, "
		if nz(!topic3, 0) < 50 then _
			s_return = s_return & "Topic3, "
		
		if s_return <> "" then s_return = left(s_return, len(s_return)-2)
	end if
end with
fnkFailed = s_return

 

[Haider Majeed]

The picture shows a table which does not look normalised. Your first step would be to normalise your db as already recommended by theDBguy.

If you do not understand what normalising a db is about, have a quick search of this forum or google.

thanks

I divide the work into 2 tables one containing the marks and the other the Fail topics

the needed code is to insert the results for each student as creation of a new table or updating a field in an existed table

regards

 

[theDBguy]

is this valuable?

Hi. If you're stuck with that structure, then check out the IIf() function. You might not need a loop.

 

[plog]

Everyone replying understands what you want. You need to understand what they are saying. I will be the 4th in this thread person to propose this:

Set up your tables properly. This process is called normalization (https://en.wikipedia.org/wiki/Datab...n is the process,part of his relational model)

You are not using Access properly. There are rules to setting up tables and you have not employed them. Further, what you want to do is break those rules even more.
You are heading down a bad path and everyone is trying to make your life in the future easier.

To your specific issue--once you properly structure your data you will be able to use Allen Browne's function ConcatRelated(http://allenbrowne.com/func-concat.html).

The first step though is normalizing your data (arnelgp showed what your tables should look like when structured properly). Do that before moving forward on this issue you posted about.

 

[Uncle Gizmo]

I'll just say the same to reinforce what everyone else is saying!

I also did a Blog on the subject which is on my website here:- Excel in Access

Basically that's what you're trying to do, you're trying to use MS Access as if it were Excel. The problem is, it is possible, but as you are already finding, extracting useful information is difficult.

If you carry on with the wrong structure, then the problems just get worse and worse... That's why is everybody is telling you, because we've all been down that road and we know how frustrating and difficult it gets.

You're not doing anything wrong as you've taken the first step on your journey of how to learn to do things in MS Access... Just take a step back and take the advice...

 

[Haider Majeed]

I apologize dear and This is the structure of tables

 

[theDBguy]

I apologize dear and This is the structure of tables

Hi. Unfortunately, that's exactly what we meant by a non-normalized table structure. If it's not too late, you should make an attempt to normalize it. If you did, then your table might look something more like this instead:

 

[Haider Majeed]

Thank a lot for your efforts. You mean I can not loop through these recordset to find topics with less than 50 and to insert them in a new field in the way I mentioned?

 

[theDBguy]

Thank a lot for your efforts. You mean I can not loop through these recordset to find topics with less than 50 and to insert them in a new field in the way I mentioned?

Yes, you can. We're not saying you can't. However, I also mentioned earlier you might not need a loop even with the current table structure by using an IIf() statement. What we're trying to tell you is if you're going to use a database, then we would like to encourage you to use it properly. That's all. No one is forcing you to do anything you don't want to or can't do. Maybe it's out of your hands. If so, you might ask if they (whoever is in control) can make the changes for you.

 

[arnelgp]

consider this design.
you add Scores to Form frmScores.

your final result is in query, FinalQuery.

 

[theDBguy]

Yes, you can. We're not saying you can't. However, I also mentioned earlier you might not need a loop even with the current table structure by using an IIf() statement. What we're trying to tell you is if you're going to use a database, then we would like to encourage you to use it properly. That's all. No one is forcing you to do anything you don't want to or can't do. Maybe it's out of your hands. If so, you might ask if they (whoever is in control) can make the changes for you.

Hi. Here's what I mean by using an IIf() statement instead of a loop. Open up Query1.

 

[Haider Majeed]

Thanks a lot Mr. Arnelgp I thinks it is difficult to deal with thousands of records in "Marks table" in this way. I appreciate your advice.

 

[Haider Majeed]

Thanks a lot I got benefit from the application of the query

Regards

 

[theDBguy]

Thanks a lot I got benefit from the application of the query

Regards

Hi. We're all happy to assist. Good luck with your project.