complex query

[hermie]

In my students database I want to calculate the total of presens days in the
school. The calculation should be like: total days - (total absents + total
special days).
The SQL view of the total present days query is:


SELECT Students.ss, Students.Grupo, Students.Nombre, Students.ApellidoP,
Work_Days(dates!begdate,dates!enddate) AS daysprisem,
Work_Days2(dates!begdates,dates!enddates) AS dayssecsem

FROM Students INNER JOIN dates ON Students.begdate = dates.begdate;



From this query I made a query of total days - absents SQL view is:



SELECT Students.ss, Students.Grupo, Students.Nombre, Students.ApellidoP,
Querypresentes.daysprisem, [Ausente de estudiantes_Crosstab by ss].[2004] AS
ausentes2004, [daysprisem]-IIf(IsNull([2004]),0,[2004]) AS total,
Querypresentes.dayssecsem, [Ausente de estudiantes_Crosstab by ss].[2005],
[dayssecsem]-[2005] AS totals

FROM (Students LEFT JOIN Querypresentes ON Students.ss = Querypresentes.ss)
LEFT JOIN [Ausente de estudiantes_Crosstab by ss] ON Students.ss = [Ausente
de estudiantes_Crosstab by ss].ss

GROUP BY Students.ss, Students.Grupo, Students.Nombre, Students.ApellidoP,
Querypresentes.daysprisem, [Ausente de estudiantes_Crosstab by ss].[2004],
[daysprisem]-IIf(IsNull([2004]),0,[2004]), Querypresentes.dayssecsem,
[Ausente de estudiantes_Crosstab by ss].[2005], [dayssecsem]-[2005]

ORDER BY Students.Grupo;





Now I want to make a query that substracts the special days.

The special days table is simple, only day of asbent field and the reason
field. From this table I made a select

Query that count the total days.

SELECT Count([dia especiales].Diaespec) AS CountOfDiaespec

FROM [dia especiales];



How can I make a query that shows for each student the total days present??



Any help is welcome