Count records that do not exist

[TheGov]

Hi,

The subject line may sound strange, but I can't think of a better way to put it.

I am writing an app for a school reports system. I have a table with the following fields:

Name,Subject,Teacher,Report_text.

Every child should have 14 different subject reports. I need to find a way to produce a list of the subjects that have NOT been entered. I thought I might be able to use a second table as a lookup with a list of the 14 subjects in it, but I can't get my head round the logic. I have tried a COUNT function with GROUP, but that only returns the number of missing reports for each child, but not which ones are missing.

Any help REALLY appreciated, I have spent hours on this one.

Pete

 

[ChrisRChamberlain]

TheGov

Assuming you have a child table for the 14 reports, called REPORTS, you could, (untested) :-

CREA CURS missing (subject C(50))
SELE REPORTS
SCAN
[tab]IF EMPTY(REPORTS.report)
[tab][tab]INSERT INTO MISSING (subject) VALUES (REPORTS.report)
[tab]ENDI
ENDS

The cursor MISSING would contain the list of the missing reports.

You could then run the above code within a SCAN... ENDS for all pupils.

FAQ184-2483 - the answer to getting answered.​

Chris

 

[ramani]

Hi
***********************
CREATE TABLE table1 (cName C(10), Subject C(5), Teacher C(5), Reporttxt C(10))
INSERT INTO table1 (cNAME, SUBJECT ) VALUES ("AAA", "SUB1&quot
INSERT INTO table1 (cNAME, SUBJECT ) VALUES ("AAA", "SUB3&quot
INSERT INTO table1 (cNAME, SUBJECT ) VALUES ("AAA", "SUB5&quot
INSERT INTO table1 (cNAME, SUBJECT ) VALUES ("BBB", "SUB1&quot
INSERT INTO table1 (cNAME, SUBJECT ) VALUES ("CCC", "SUB1&quot
INSERT INTO table1 (cNAME, SUBJECT ) VALUES ("CCC", "SUB4&quot

CREATE TABLE SUBJECT (Subject C(5))
INSERT INTO subject (SUBJECT ) VALUES ("SUB1&quot
INSERT INTO subject (SUBJECT ) VALUES ("SUB2&quot
INSERT INTO subject (SUBJECT ) VALUES ("SUB3&quot
INSERT INTO subject (SUBJECT ) VALUES ("SUB4&quot
INSERT INTO subject (SUBJECT ) VALUES ("SUB5&quot

***************************************************

SELECT DISTINCT cname FROM table1 INTO CURSOR table11

SELECT a.subject, b.cname FROM subject a, table11 b INTO CURSOR table12

SELECT * FROM table12 WHERE subject+cname NOT IN ;
(SELECT subject+cname FROM table1)
*************************************
ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[ChrisRChamberlain]

TheGov

Apologies - brain failure setting in

CREA CURS missing (subject C(50))
SELE REPORTS
SCAN
IF EMPTY(REPORTS.report)
INSERT INTO MISSING (subject) VALUES (FIELD())
ENDI
ENDS

FAQ184-2483 - the answer to getting answered.​

Chris

 

[TheGov]

Hi Guys,

Ramani, your solution seems to be the best. I can see the logic behind it, but when I try to run it, I get 'operator/operand type mismatch'. I have trawled through the help files, but cannot find any reference to using + in this context.

Could you explain further?

Thanks
Pete

 

[german12]

The Gov,

I tried Ramanis program and it worked - I got a query on my screen where all the groups where displayed.
Would suggest to copy again that program into a test.prg-file
and then run it again - assume that you copied
or re-typed (?) it wrong the first time.

Regards from Germany

Klaus Peace worldwide - it starts here...