Selecting records based on table data

[Viscount]

I hope that somone can give me some help.

CR 10 with SQL2000

I have got 2 tables, one containing demographic data (demo_table) and the other containing appointment data (appt_table). The appointment table records if the person attended or did not attend (DNA).

demo_table

Id# Name
1 Bob
2 Jim
3 Sue
4 Tom

appt_table

id# date attended
1 3/3 True
1 4/3 False
2 4/3 false
2 6/3 false
1 4/5 false
3 7/7 true

I have created a report based on information contained in the demo_table. What I would like to do is remove everyone who has attended and leave those who have DNA'ed or have not had an appointment.

Using the abve example the list should only have Jim and Tom as Jim DNA'ed twice and Tom because he has not had an appointment.

Thanks for any help.

Vis

 

[Madawc]

Group by Id. Do a running total for attended 'True' and another for 'False'. Hide everything except the group footer. Suppress the group footer when it does not meet the criteria.

Tom, with no entires in the appt_table, will have no group and will automatically be excluded.

Madawc Williams (East Anglia, UK)

 

[ShortyA]

You could write a stored procedure along the lines of:

SELECT Name FROM demo_table
where 
	(
	Id# not exists in
//Exclude where no appointments
		(
		SELECT id# FROM appt_table
		)
	or Id# exists in
//Exclude where did not attend appointment
		(
		SELECT id# FROM appt_table
		WHERE attended='false'
		)
	)

My SQL is not 100% but the above should provide the process. In Crystal Reports 8 you could edit the SQL Query from the Database menu but not sure if you can do that in version 10. This would be the first thing to try before writing a stored procedure.
Hope this helps,
ShortyA

 

[Viscount]

Thanks for your help - with some jiggery-pokery I have managed to sort it - I hope!

Cheers

Vis