ms access report

• • Reply

  11 years ago

  by genius_supreme

  genius_supreme

  , Malaysia

  Joined 18 years ago

  Hi guys, need your help. (MS Access- VBA) I'm trying to create a report that looks something like an attendance report with names columns on the left and days columns on the left.

  This is how I want it to be Names of students on left and names of 12 month (Jan to Dec) columns on the right and for every student attend the course for each month there should be a (thick) check box or some thing to indicate the presence of the student.

  my table consist (tblTrx) stdDate,stdName, stdClass

  i have a form to get the date range txtBeginDate, txtEndDate

  I was told to use this codes in the sql of the Query SELECT tblTrx.StudentName, Sum(1=Month([TrxDate])) AS Jan, Sum(2=Month([TrxDate])) AS Feb, Sum(3=Month([TrxDate])) AS Mar FROM tblTrx;

  but it returned error saying You tried to execute a query that does not include the specified expression TrxDate as part of an aggregate function.

  I checked the help section and came across the TRANSFORM statement but still feeling lost!

  need help guys please.

  Report abuse
• • Reply

  11 years ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 19 years ago

  Hi,

  Firstly, you need to match up the field names in the query with the field names in the table; your table uses stdName and stdDate but the query uses StudentName and TrxDate. Once you have sorted that out, you can then use the GROUP BY function to have an aggregate, so you can end up with some SQL that looks a bit like this:

  SELECT tblTrx.StudentName, Sum(1=Month([TrxDate])) AS Jan, Sum(2=Month([TrxDate])) AS Feb, Sum(3=Month([TrxDate])) AS Mar FROM tblTrx GROUP BY tblTrx.StudentName;

  Which should set you on the way to solving your problem.

  Hope this helps!

  Report abuse