Querie/report

[treacle]

I have got a list of students in a table with course details etc
Some students, their name appears 10 times as they are in different subjects etc.
I need to report who has paid their fee's and each student only pays once, So although this one student appears 10 times i ahve to filter his surname down to be 1 person who has paid once..
Any help would be appreciated

 

[Corticus]

Hi treacle,

Create a query of the student exam table. Now insert all the fields that are unique to a student, but not a class (like the students name, but not the professor for a given class), including the field that tells you whether or not they paid. Now with the query in design view, right click on the area around where you put your tables, select properties, and set 'unique values' to true. You could now set the 'paid' field to true or false, or whatever you need to see. The idea is, you want to display unique students (this is why you exclude class info, because the same student with two different classes is considered two unique records, whereas as the same student's name twice, with no other data, would be only one record) that either paid, or didn't. If you have an 'amount paid' field, you will need to test it with criteria like >0 or something similar.

HTH,

 

[treacle]

Thanks for that.
I have done what you suggested and it has worked to a degree, I have managed to filter all but tow students into the paid or unpaid category. The problem is two students are enrolled in two many different courses. Some they have paid and other that they havent. I need to give 1 amount total that each student has paid.
I cannot seperate this student as one because he has paid different amounts so it isnt seeing it as the same record.
Am i Making sense?
Cheers

 

[Corticus]

Ah I see,

I think that would be the perfect place for a cross tab query.

Open your db, and on the main startup window, go to where your queries are, and select new|crosstab query.

First, tell the wizard the table you're interested in.
Use the students name fields as row headers.
Then use the class name (or number) as the column headers.
Finally, for the calculatd intersections, use the amount field, and select the sum function.

That should do it. If you still have trouble feel free to send me some sample data at corticus@theofficeexperts.com.

HTH,