Just added a field and now query is over calculating!

[kate10123]

Hi there,

I am using a query to get a rough count of how many students have had more than one tutorial. This was working fine until I put a WHERE clause to add a 'TO' and 'FROM' date statement.

Here is my SQL:

SELECT Count(vTbl.StudentID) AS StudentsMore
FROM (SELECT StudentID
      FROM tbl_Tutorial
      GROUP BY StudentID
      HAVING Count(TutorialNo) > 1)  AS vTbl, tbl_Tutorial
WHERE (((tbl_Tutorial.Date) Between [Forms]![frm_Dates]![txtFrom] And [Forms]![frm_Dates]![txtTo]));

Does anyone have any idea where I may have gone wrong?

Thanks

Kate

 

[Kiwiman]

Howzit

Try the following:

SELECT tbl_Tutorial.StudentID AS StudentsMore
FROM tbl_Tutorial
WHERE (((tbl_Tutorial.Date) Between [Forms]![frm_Dates]![txtFrom] And [Forms]![frm_Dates]![txtTo]))
GROUP BY tbl_Tutorial.StudentID
HAVING (((Count(tbl_Tutorial.TutorialNo))>1));

 

[kate10123]

Thanks that worked really well.

I am having a similar problem with the SQL statement below:

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_Tutorial)  AS T, tbl_Tutorial
WHERE (((tbl_Tutorial.Date) Between [Forms]![frm_Dates]![txtFrom] And [Forms]![frm_Dates]![txtFrom]));

I am trying to get a unique count of studentIDs in the tutorial table but in a particular period. The unique count works but not specific to the dates entered.

 

[Kiwiman]

Howzit

Try this...

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_Tutorial WHERE (((tbl_Tutorial.Date) Between [Forms]![frm_Dates]![txtFrom] And [Forms]![frm_Dates]![txtFrom])))

 

[marianne]

i suggest you try using QUERY on making the SQL, it would be much easier for you I think before you apply it.