Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
ms access report with check box?
I am trying to create a report in MS Access with a Check Box which appears as Checked for selected month.
I.e:
I have fields from a table tblTrx (Student Name, TrxDate, Amount)
I want the report to display Student Name and a checked [Check Box] for the respective month from Jan to Dec
Example Output (Report)
Student Name Jan Feb Mar .... Dec
Student 1 /
Student 2 / /
Student 3 / /
(/ =indicates checked [Check Box])
I hope you get what i mean (Just like an Attendance Report displayed from Jan to Dec -12 Columns)
Im running a query which has a date range criteria from a form (begin date & end date)
my current sql query is
SELECT tblTrx.TrxDate, tblTrx.StudentName, tblTrx.Category
FROM tblTrx
WHERE (((tblTrx.TrxDate) Between [Forms]![frmTrxHistory].[txtFromDate].[value] And [Forms]![frmTrxHistory].[txtToDate].[value]) AND ((tblTrx.Category)="I - Senior" Or (tblTrx.Category)="I - Fresh" Or (tblTrx.Category)="I - Junior") );
after i run the sql statement you gave it gave me an error message ..TrxDate... not aggregate ... some thing like that
could you kindly provide a detailed suggestion
Yo.. Chris
I tried your revised codes... they seem to be working but.. there is a slight problem... or maybe i missed out something... the report appears as 0 (zeros) and -1 instead of check mark...
how do i do that
2 Answers
- Anonymous1 decade agoFavorite Answer
The trick is to make sure you have a query that produces Boolean (yes/no) fields that will be displayed on the report as check boxes. Left as dates, they can't really be check boxes on the report. Here is an example query in SQL that converts the dates into Booleans that will work the way you want in Access:
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;
If you use the wizard to build a report from this query, it will default as check boxes. You've left out some details about your database, so keep in mind that all years' dates will show up unless you further filter on the specific year you want to see.
-----
I can't gather from your SQL statement how you really want to group the data. So here's what I am assuming:
You have a table tblTrx which contains records of a student's visits. Every time the student attends class, he gets a new record added to the table, which could mean more than once a month...
Here's some data:
trxDate StudentName Category
---------- ----------- ----------
Jan 1,2008 Mike I - Junior
Jan 2,2008 Mike I - Junior
Jan 3,2008 Mike I - Junior
Feb 1,2008 Mike I - Junior
Jan 5,2008 Carol I - Freshman
Mar 6,2008 Carol I - Freshman
Mar 7,2008 Carol I - Freshman
Feb 6,2008 Fred I - Senior
And here is the report I think you want, (1Jan08 - 31Dec08)
StudentName Jan Feb Mar
----------- --- --- --- ...
Mike.........[x]..[x]..[ ]
Carol........[x]..[ ]..[x]
Fred.........[ ]..[x]..[ ]
No category is displayed or grouped on, just all students listed together.
If this is what you want, here is a query that will work. (Since you didn't provide the whole SQL statement, mine will differ)
SELECT tblTrx.StudentName,
Sum(1=Month([TrxDate])) AS Jan,
Sum(2=Month([TrxDate])) AS Feb,
Sum(3=Month([TrxDate])) AS Mar
FROM tblTrx
WHERE tblTrx.TrxDate Between [startdate] And [enddate]
GROUP BY tblTrx.StudentName
Some things to keep in mind: Don't select TrxDate or Category because it will affect the grouping. Also you'll notice that we SUM the boolean expression #=Month([TrxDate]) for each month. We do this to aggregate the instances each student attends per month. Access will still interpret the added up Booleans as a Boolean and anything non-zero will get a check mark.
