Do while loop (1 Viewer)

Haider Majeed · Oct 13, 2019

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 · Oct 13, 2019

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 · Oct 13, 2019

theDBguy said:
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 · Oct 13, 2019

Haider Majeed said:
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 · Oct 13, 2019

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.

Code:

update marks set Fail_Topics = fnkFailed([s_name])

add the code in a Module

Code:

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 · Oct 13, 2019

bob fitz said:
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 · Oct 13, 2019

Haider Majeed said:
is this valuable?

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

plog · Oct 13, 2019

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 · Oct 13, 2019

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 · Oct 13, 2019

I apologize dear and This is the structure of tables

theDBguy · Oct 13, 2019

Haider Majeed said:
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 · Oct 13, 2019

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 · Oct 13, 2019

Haider Majeed said:
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 · Oct 13, 2019

consider this design.
you add Scores to Form frmScores.

your final result is in query, FinalQuery.

theDBguy · Oct 13, 2019

theDBguy said:
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 · Oct 13, 2019

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 · Oct 13, 2019

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

Regards

theDBguy · Oct 13, 2019

Haider Majeed said:
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.

Do while loop (1 Viewer)

New member

Attachments

I’m here to help

New member

Attachments

AWF VIP

..forever waiting... waiting for jellybean!

New member

Attachments

I’m here to help

Banishment Pending

Nifty Access Guy

New member

Attachments

I’m here to help

Attachments

New member

I’m here to help

..forever waiting... waiting for jellybean!

Attachments

I’m here to help

Attachments

New member

New member

I’m here to help

Similar threads

Users who are viewing this thread