Automatic numbers for comparision (1 Viewer)

NT100 · Apr 11, 2018

I've 4 tables, tbl1, tbl2, tbl3 and tbl4.

fld1 of tbl1, fld2 of tbl2 contain values of primary key of tbl3. They are long integer type.

I have written a vba to loop tbl2 within the loop of tbl1 to compare fld1 with fld2. If comparison is equal, a counter is incremented and some field values from tbl1 and tbl2 will be written into tbl4.

I encounter weird results in which the comparisons is not as accurate as expected.

Is long integer cannot be accurately compared?

Any suggestions on this.

isladogs · Apr 11, 2018

Long integer should be fine. Double numbers can be problematic in this respect.

Define weird results' and give some examples where they occur.

plog · Apr 11, 2018

I have written a vba to loop tbl2 within the loop of tbl1 to compare fld1 with fld2. If comparison is equal, a counter is incremented and some field values from tbl1 and tbl2 will be written into tbl4.

Why? You just described an UPDATE query. You are using VBA to achieve what can be done simply in SQL.

Further, UPDATE queries are hack themselves. Why is all this data being moved around?

NT100 · Apr 11, 2018

Pls. find my ACCESS program as attached.

Just click "PIP1 Student-Teacher Mapping" to start the mapping work.
Normally, there should be 241 records in "tblST_Map_PIP" but 207 records.

You may run "qryUpdate_Student_PIP_Map_Reset" and "qryUpdate_TAvail_PIP_Map_Reset" to clear the "mapped" field in the tables of tblTAvail_PIP and tblStudent_PIP, and "qryDelete_ST_Map_PIP" delete those added records from the results of mapping work as what "PIP1 Student-Teacher Mapping" did.

My VBA has been re-examined but still get the results unexpected. I hope this information is useful to you to isolate the problems.

Thank you.

jdraw · Apr 11, 2018

NT100,

In Access you must explicitly DIM variables. If you do not, then variables are dimmed as Variant.
So this

Code:

Dim rsTutorSeq, rsTutor, rsStudent, rsST_Map As Recordset
    Dim sStudent, sTutor As String
    Dim iSQuota, iSCnt, iSPSCnt, iSPSQuota As Integer

does not do what you think. It may not be a factor in your counts, but you should be aware.

You have shown us HOW you did something and are saying the counts are incorrect.
Why should the count be 241 and not 207? What exactly is the "algorithm in plain English" to get to the 241?
You did not answer the questions posed by ridders or plog???

NT100 · Apr 11, 2018

There are some criteria

Each tutor states the number of sessions he/she is available for teaching, there're 25 sessions in year 1 for selections.

In each session, the tutor states the number of students he/she can take, usually 1 student or 2.

The tutor also states that the total number of students he/she can take in the academic year.

e.g. a tutor fills all 25 sessions, he/she takes 2 students in each session but only 1 student in the academic year. Eventually, the tutor takes one student in one session throughout the academic year.

My work is to do the least available first fit algorithm, the least number of sessions the tutor is willing to take student will be mapped to the students first. E.g. If a tutor only teaches a session, he will be first assigned to the student and so on.

The algorithm is to assign 241 students (qryStudent_PIP1_2016) to the tutors and meet the above criteria, then put the tutor-student assignment into tbl4.

The following SQL is to sort in ascending order of session availability of the tutors

SELECT tblTAvail_PIP.TRef, Count(tblTAvail_PIP.Session) AS SessionCnt
FROM tblTAvail_PIP
WHERE (((tblTAvail_PIP.AcademicYr)=2016) AND ((tblTAvail_PIP.PIPYr)=1))
GROUP BY tblTAvail_PIP.TRef
ORDER BY Count(tblTAvail_PIP.Session);

I hope the above criteria and algorithm can help my work.

NT100 · Apr 11, 2018

jdraw said:
NT100,

In Access you must explicitly DIM variables. If you do not, then variables are dimmed as Variant.
So this

Code:

Dim rsTutorSeq, rsTutor, rsStudent, rsST_Map As Recordset Dim sStudent, sTutor As String Dim iSQuota, iSCnt, iSPSCnt, iSPSQuota As Integer

does not do what you think. It may not be a factor in your counts, but you should be aware.

You have shown us HOW you did something and are saying the counts are incorrect.
Why should the count be 241 and not 207? What exactly is the "algorithm in plain English" to get to the 241?
You did not answer the questions posed by ridders or plog???

All variables are explicitly declared. My work is to assign the suitable tutors to 241 students NOT 207 students as the program outputs.

There're 688 sessions available among the tutors for the students. My work only did 172 numbers of tutors count for the students.

jdraw · Apr 11, 2018

Dim rsTutorSeq, rsTutor, rsStudent, rsST_Map As Recordset

actually does/results in the following

Dim rsTutorSeq as variant
Dim rsTutor as variant
Dim rsStudent as variant and
Dim rsST_Map As Recordset

If you want all of the following to be Integers
Dim iSQuota, iSCnt, iSPSCnt, iSPSQuota As Integer
then use
Dim iSQuota as Integer, ISCnt as Integer,iSPSCnt as Integer, iSPSQuota As Integer
or individual Dim statements as shown above.

Good luck with your project

Pat Hartman · Apr 11, 2018

There is lots wrong with the code but I did not try to follow it closely enough to figure otu what is the problem. One thing I did find is that you have SUID defined as numeric. It is NOT a number. It is a CODE. Codes are text strings so I changed it from double to short text.

Another confusion for me was the use of ID as the pk name. I had trouble matching the pk to fk because the names were different and you have no relationship diagram. You have defined autonumbers but I think you are not using them correctly.

In any event, I produced a list of rows that are missing. Perhaps you can look at them and figure out why.

Code:

ID	SUID	AcademicYr	PIPYr	Session	Gp	Remarks	TTID	Mapped
456	3035361556	2016	1	25	23		25	No
457	3035361879	2016	1	25	23		25	No
468	3035378341	2016	1	23	21		23	No
519	3035381374	2016	1	23	21		23	No
521	3035381439	2016	1	25	23		25	No
526	3035382031	2016	1	25	23		25	No
529	3035382196	2016	1	23	21		23	No
530	3035382380	2016	1	25	23		25	No
541	3035383310	2016	1	22	20		22	No
543	3035383487	2016	1	22	20		22	No
552	3035384118	2016	1	25	23		25	No
553	3035384273	2016	1	22	21		22	No
554	3035384443	2016	1	22	20		22	No
558	3035384596	2016	1	17	15		17	No
560	3035384883	2016	1	17	15		17	No
574	3035386491	2016	1	17	15		17	No
584	3035387122	2016	1	23	21		23	No
600	3035388011	2016	1	17	15		17	No
601	3035388114	2016	1	23	21		23	No
604	3035388310	2016	1	25	23		25	No
605	3035388425	2016	1	25	23		25	No
615	3035388956	2016	1	24	22	C	24	No
616	3035389106	2016	1	24	22		24	No
617	3035389314	2016	1	24	22		24	No
619	3035389857	2016	1	25	23		25	No
620	3035390088	2016	1	22	20		22	No
627	3035394668	2016	1	17	15		17	No
628	3035394670	2016	1	22	20		22	No
631	3035395167	2016	1	24	22		24	No
637	3035396070	2016	1	17	15		17	No
641	3035396329	2016	1	24	22		24	No
657	3035401215	2016	1	24	22		24	No
658	3035401239	2016	1	25	23		25	No
659	3035402063	2016	1	17	15		17	No

NT100 · Apr 12, 2018

jdraw said:
Dim rsTutorSeq, rsTutor, rsStudent, rsST_Map As Recordset

actually does/results in the following

Dim rsTutorSeq as variant
Dim rsTutor as variant
Dim rsStudent as variant and
Dim rsST_Map As Recordset

If you want all of the following to be Integers
Dim iSQuota, iSCnt, iSPSCnt, iSPSQuota As Integer
then use
Dim iSQuota as Integer, ISCnt as Integer,iSPSCnt as Integer, iSPSQuota As Integer
or individual Dim statements as shown above.

Good luck with your project

Individual declarations were made, however, the output count is still below the expectation.

Welcome any idea on this.

NT100 · Apr 12, 2018

Pat Hartman said:
There is lots wrong with the code but I did not try to follow it closely enough to figure otu what is the problem. One thing I did find is that you have SUID defined as numeric. It is NOT a number. It is a CODE. Codes are text strings so I changed it from double to short text.

Another confusion for me was the use of ID as the pk name. I had trouble matching the pk to fk because the names were different and you have no relationship diagram. You have defined autonumbers but I think you are not using them correctly.

In any event, I produced a list of rows that are missing. Perhaps you can look at them and figure out why.

Code:

ID SUID AcademicYr PIPYr Session Gp Remarks TTID Mapped 456 3035361556 2016 1 25 23 25 No 457 3035361879 2016 1 25 23 25 No 468 3035378341 2016 1 23 21 23 No 519 3035381374 2016 1 23 21 23 No 521 3035381439 2016 1 25 23 25 No 526 3035382031 2016 1 25 23 25 No 529 3035382196 2016 1 23 21 23 No 530 3035382380 2016 1 25 23 25 No 541 3035383310 2016 1 22 20 22 No 543 3035383487 2016 1 22 20 22 No 552 3035384118 2016 1 25 23 25 No 553 3035384273 2016 1 22 21 22 No 554 3035384443 2016 1 22 20 22 No 558 3035384596 2016 1 17 15 17 No 560 3035384883 2016 1 17 15 17 No 574 3035386491 2016 1 17 15 17 No 584 3035387122 2016 1 23 21 23 No 600 3035388011 2016 1 17 15 17 No 601 3035388114 2016 1 23 21 23 No 604 3035388310 2016 1 25 23 25 No 605 3035388425 2016 1 25 23 25 No 615 3035388956 2016 1 24 22 C 24 No 616 3035389106 2016 1 24 22 24 No 617 3035389314 2016 1 24 22 24 No 619 3035389857 2016 1 25 23 25 No 620 3035390088 2016 1 22 20 22 No 627 3035394668 2016 1 17 15 17 No 628 3035394670 2016 1 22 20 22 No 631 3035395167 2016 1 24 22 24 No 637 3035396070 2016 1 17 15 17 No 641 3035396329 2016 1 24 22 24 No 657 3035401215 2016 1 24 22 24 No 658 3035401239 2016 1 25 23 25 No 659 3035402063 2016 1 17 15 17 No

Thank you for your work.
The program codes have been examined and re-examined but still can't find out a bug. Would you give some hints on this.

My program code works on a few keys' conditions, SUID is only for assignment once the condition is satisfied. Besides, SUID contains all numbers only. Any change of its type doesn't help my counters output of the program code.

Welcome any idea.

Pat Hartman · Apr 12, 2018

I'd actually have to understand the code to get to the bottom of this. I provided the list of unassigned records. Take each one and step through the code line by line to see where the record is falling out. To do this with the minimum number of records, change the query from the existing criteria to just hard code the SUID of one record that works and one that doesn't. That way it is easy to step through the code and follow it.

NT100 · Apr 12, 2018

Pat Hartman said:
I'd actually have to understand the code to get to the bottom of this. I provided the list of unassigned records. Take each one and step through the code line by line to see where the record is falling out. To do this with the minimum number of records, change the query from the existing criteria to just hard code the SUID of one record that works and one that doesn't. That way it is easy to step through the code and follow it.

That's. I inspected the codes line by line and applied minor changes. There's a possibility that the actual attendances of the tutors (the criteria stated before) is below 241 student number.

My next step is to examine those students' sessions which the tutors do not attend. Any suggestions on this?

Automatic numbers for comparision (1 Viewer)

NT100

Registered User.

isladogs

MVP / VIP

plog

Banishment Pending

NT100

Registered User.

Attachments

jdraw

Super Moderator

NT100

Registered User.

NT100

Registered User.

jdraw

Super Moderator

Pat Hartman

Super Moderator

NT100

Registered User.

NT100

Registered User.

Pat Hartman

Super Moderator

NT100

Registered User.

Similar threads

Users who are viewing this thread