ms access report

vba , ms access , access report , ms access report Malaysia
  • 11 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.

  • 11 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!

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The difference between theory and practice is smaller in theory than in practice.”