Student Payment Query not working

[jcpty]

Hi there! this is my first time in this forum and I find it really helpful! I am using the student database template from MS with some modifications made by me. I use this database in a small school and apart of all the information in the template tables I need also to track student payments, these includes enrollment payment (tuition), monthly payments, credits and others. I created a payment table that is fed from a subform tab in the student details form. My dilema is the following:
I created a query to search who has paid for a particular month from the payments table, that works fine. When I select a month it shows the list of students that are current with their payments for that month, but I also need to know who is not current. I´ve tried the join modifiers with no luck. Basically what I need is to know in any particular month who is current and who is not for the month in payment type(mensualidad).

Attached is the DB, sorry for the spanish and I hope a good soul can help me... I am not familiar with access.

[June7]

Which query or report in your project is supposed to accomplish this? Did you not include it?

Post the query statement for analysis.

What are the fields [Tipo de Pago] and [Mes] and [Recargo] in Pagos table for?

[jcpty]

Thanks for your reply June 7. I am trying to accomplish that in the [Consulta - Alumnos no Solventes] Query. Tipo de Pago is to select what the student is paying for, it can be Matricula (enrolment), Mensualidad (monthly payment), Abono(deposit), otro(other). Mes is the "month" they are paying (from March to December) and "recargo" is an extra charge for paying late. What I want to accomplish is to run a report at anytime in where I select the month and it shows me who has paid and who hasn´t. I tried to do it all in one query but it didn´t work, I figured out how to run it (Consulta - Alumnos Solventes) from the "Pagos" table since it collects the payment data, but I don´t know how to make the query check the payment list in tbl "Pagos" and then check the students tbl and display from the students table who is not present for any particular month make sense?

Thanks and Blessings...

[June7]

Might have it.

Need a table of months. 12 records, one for each month.

First query (AllMonthsAlumnos)
SELECT Mes, Id FROM Mes, Alumnos;

Second query (PagosMesMensualidad)
SELECT Id, Mes, [Tipo de Pago] FROM Pagos WHERE [Tipo de Pago]="Mensualidad";

Third query
SELECT AllMonthsAlumnos.Mes, AllMonthsAlumnos.Id, PagosMesMensualidad.[Tipo de Pago]
FROM PagosMesMensualidad RIGHT JOIN AllMonthsAlumnos ON (PagosMesMensualidad.Mes = AllMonthsAlumnos.Mes) AND (PagosMesMensualidad.Id = AllMonthsAlumnos.Id)
WHERE (((AllMonthsAlumnos.Mes)=[Ingrese el Mes]) AND ((PagosMesMensualidad.[Tipo de Pago]) Is Null));

If you include multiple years in database will need to include filter criteria for the year.

[jcpty]

Thanks June7! I created a table of months(meses), but to what is this table related? the Pagos table month field? the table has one field for each month, but what I don´t get is what data I need to populate in there. Below is the link to the picture of the table relationships.

https://skydrive.live.com/redir.aspx...GOon9QjqaYYqhA

Thanks again for your reply and Blessings...

[June7]

Not one field for each month, just one field with 12 records. The data is the month names. The query SQL shows the relationship in the join clause.

That link won't open for me.

[jcpty]

Okay... one field with the months, now I get it. here is another link for the pic
http://www.flickr.com/photos/jcapty/6584228843/

[jcpty]

Hi I created the queries and the table, but I get an error message... attached is the DB. Maybe I am doing something wrong, how the query check who is not present in particualr month? the Third Query?

[June7]

The third query shows all months and any students with no Mensualidad payment for each month.

What is the error message?

Delete the autonumber ID field from Mes table. It is not needed and causing confusion.

[jcpty]

I deleted the autonumber ID and the error is gone!! and I was able to create a report with that info, Thanks a bunch for your help... one last question, if I want to run "all month" query I just need to delete =[Ingrese el Mes] from the third query and it will show all months right?

Blessings...

[June7]

Yes.

Recall I said earlier if data is multi-year, will need filter criteria for year.