Displaying records when conditions met

[Generalsndy]

hello experts!!
plz help. im stuck in here...
grade table
name|subject |grade|sem|yr|
------------------------------
john|english 11 |2 |1 |1
john|math 11 |5 |1 |1
fox |english 11 |3 |1 |1
fox |history 11 |1 |1 |1

subject table
subject |prerequisite1|prerequisite2|sem
-------------------------------------------
english 12 |english 11 |none |2
math 12 |math 11 |none |2
history 12 |history 11 |none |2

when john is selected then the user can only see subjects that john can take like english 12 (since grade in math 11 is failed)
and when fox is selected then grid dislays english 12 and history 12 (since he pass the 2 subjects)
how to do this the easiest way? newbie in vfp

 

[TamarGranor]

Try building this one step at a time. First, figure out how to write a query that gives you all the courses a specified student has passed.

There's an interesting question here, though. In what I assume is much simplified data, you have only one year's worth, but won't your real data have last year's and the year before's and so on. So that you might also have an English 10 listing for John, which would lead you to show him English 11? If you can limit to only courses a student has passed in a specified year, that would resolve this problem, but then you wouldn't have a way to find courses a student is eligible for based on what they took in a prior year. For example, a student might take Science one year and then skip it the next year, but want to pick it up again in the 3rd year.

Once you have a query that produces a list of courses the student has passed (perhaps filtering out those where she has already taken a successor course), then you can use that as a subquery in a WHERE condition of a query of the Subject table.

Tamar

 

[JRB-Bldr]

Here is some un-tested code that you can begin to play around with.

NOTE1 - The Data Table Field Name "prerequisite1" is greater than 10 char - shorten it (same for 2)
NOTE2 - You do not inform us what a passing Grade is.
NOTE3 - You don't tell us if PreReq1 MUST be Passed BEFORE PreReq2 (or vice-versa)
The answers to NOTE2 & NOTE3 can dramatically affect whether or not the code below might be a reasonable approach.

* --- Find 'Passed' Courses for InputName (example:  "JOHN") ---
* --- Assumes that Grade 3 and above are "Passed" ---
SELECT *;
   FROM Grade;
   WHERE ALLTRIM(UPPER(Name)) == ALLTRIM(UPPER(InputName));
   AND Grade >= 3;
   INTO CURSOR CoursesPassed

* --- Use Previous List of 'Passed' Courses to Acquire New Courses Available ---
SELECT *;
   FROM Subject;
   WHERE prerequisite1 IN (SELECT Subject FROM CoursesPassed);
   OR prerequisite2 IN (SELECT Subject FROM CoursesPassed);
   INTO CURSOR CoursesAvailable

SELECT CoursesAvailable
BROWSE FIELDS Subject

BTW: This sort of has the 'feel' of school coursework.

Especially since you are not familiar with the VFP Table Maximum Field Name Length.​

Is it that or something else?​

Good Luck,
JRB-Bldr

 

[mjcmkrsr]

Hi,
Maybe the code below gives some hints.

SET DELETED ON

CLOSE ALL 

&&& Create cursor with names

CREATE CURSOR curNames (cPKey C(4), cName C(10))
INSERT INTO curNames VALUES ( "1000","Joe")
INSERT INTO curNames VALUES ( "1001","Sam")
INSERT INTO curNames VALUES ( "1002","Jill")
INSERT INTO curNames VALUES ( "1003","Mary")
INSERT INTO curNames VALUES ( "1004","Zoe")

&&& Create cursor with Subjects

CREATE CURSOR curSubjects (cPKey C(4), cSubject C(10))
INSERT INTO curSubjects VALUES ( "1000","English")
INSERT INTO curSubjects VALUES ( "1001","Math")
INSERT INTO curSubjects VALUES ( "1002","French")
INSERT INTO curSubjects VALUES ( "1003","History")
INSERT INTO curSubjects VALUES ( "1004","Chem")

&&& Create cursor with subjects taken

CREATE CURSOR curCross (cFKNames C(4), cFKSubjects C(4))
INSERT INTO curCross VALUES ( "1000","1000") && Joe takes English
INSERT INTO curCross VALUES ( "1000","1001") && Joe takes Math
INSERT INTO curCross VALUES ( "1000","1003") && Joe takes History
INSERT INTO curCross VALUES ( "1001","1000") && Sam takes English
INSERT INTO curCross VALUES ( "1001","1003") && Sam takes History
INSERT INTO curCross VALUES ( "1001","1004") && Sam takes Chem
INSERT INTO curCross VALUES ( "1002","1000") && Jill takes English
INSERT INTO curCross VALUES ( "1002","1002") && Jill takes French
INSERT INTO curCross VALUES ( "1002","1003") && Jill takes History
INSERT INTO curCross VALUES ( "1003","1003") && Mary takes History
INSERT INTO curCross VALUES ( "1004","1001") && Zoe takes Math
INSERT INTO curCross VALUES ( "1004","1004") && Zoe takes Chem

&&& Create cursor with all possible combinations

SELECT curNames.cPKey as cFKNames, curSubjects.cPKey as cFKSubjects FROM curNames, curSubjects ;
	INTO CURSOR curNamesBysubjects READWRITE 
	
&&& Create cursor with the subjects NOT taken

DELETE curNamesBySubjects from curNamesBySubjects	;
	join curCross on curNamesBySubjects.cFKNames = curCross.cFKNames AND curNamesBySubjects.cFKSubjects = curCross.cFKSubjects
	
&&& Show Names with subjects 

SELECT curNames.cName, curSubjects.cSubject FROM curNames ;
	JOIN curCross ON curNames.cPKey = curCross.cFKNames ;
	JOIN curSubjects ON curSubjects.cPKey = curCross.cFKSubjects ;
	ORDER BY 1 ;
	INTO CURSOR curByName

LOCATE 
BROWSE NOWAIT 

*!*	&&& Show Subjects with Names 

*!*	SELECT curNames.cName, curSubjects.cSubject FROM curNames ;
*!*		JOIN curCross ON curNames.cPKey = curCross.cFKNames ;
*!*		JOIN curSubjects ON curSubjects.cPKey = curCross.cFKSubjects ;
*!*		ORDER BY 2, 1 ;
*!*		INTO CURSOR curBySubject

*!*	LOCATE
*!*	BROWSE NOWAIT 

&&& Show Subjects NOT taken

SELECT curNames.cName, curSubjects.cSubject FROM curNames ;
	JOIN curNamesBySubjects ON curNames.cPKey = curNamesBySubjects.cFKNames ;
	JOIN curSubjects ON curSubjects.cPKey = curNamesBySubjects.cFKSubjects ;
	ORDER BY 1 ;
	INTO CURSOR curSubjectsNotTaken

LOCATE
BROWSE  
SET DELETED OFF 
CLOSE ALL

hth

MK