Create a form that would show up-to-date statistical records

[dilara]

Hi,

I am totally clueless on how will i do this.

I have table, tblJKTMain, that has several fields including date and form type.
I want to create a form that would summrized the current number of records in the table and that it would be sorted per form type on a monthly basis.

Attached is a sample data that i want the form to show.

Thanks for helping

[Robeen]

This is pretty easy to do in a Report rather than a Form.

Is there a reason you want to see your data in a Form?
If you must the data in a Form, I would recommend creating a query based on your table.
Add a field to the Query that pulls out the Year & Month part of your Date field:

Year-Month: Format([Report Date],"yyyy") & Format([Report Date],"mm")

Then [still in the query] you can Group by Year-Month and FormType - and Count one of the other fields to give you a count of the records in the Table.

I did a quick mock-up of this in a test DB I have and it looks like this [in SQL View]:

SELECT Format([DateField],"yyyy") & Format([DateField],"mm") AS [Year-Month], [YourTableName].FormType, Count([YourTableName].[SomeOtherField]) AS [Count_Of_Records]
FROM [YourTableName]
GROUP BY Format([DateField],"yyyy") & Format([DateField],"mm")], [YourTableName].FormType
ORDER BY Format([DateField],"yyyy") & Format([DateField],"mm")], [YourTableName].FormType;

Once you have the query giving you the data you need, you can base your Form on the query.

Hope this helps!

[dilara]

Thank you Robeen. Sorry, i was busy the last month.

Any way, the problem now is how to display the results in an unbound text box. I tried using the Dcount direct from the table but im having a hard time to diplay count for a particular month. Here is my control source in the text box

=DCount("[Form Type]","tblKLMain","[Submitted by] = 'BMSCTM'")

such display the aggregate count. i wanted to add another criteria for to just show the count for a particular month. Is this possible?

Looking forward to your reply. Thanks much

[Robeen]

I did this on a test Form of mine - and it works.

=DCount("[Customer Name]","00_AAA_Test","[Customer Name] = 'Marshall' AND Month([Report Date]) = 09")

I used the month function to get the month out of my 'Report Date' field - you might have to adjust according to what you need.

[dilara]

It didnt work.

=DCount("[Form Type]","tblJKTMain","[Submitted by] = 'BMSCTM' AND Month ([Report Date]) = 07")

Please note that the date format in the tblJKTMain is Medium Date (02-Jul-12). Further record from this table are "appended" by another data-entry table.

[Robeen]

=DCount("[Form Type]","tblJKTMain","[Submitted by] = 'BMSCTM' AND Month ([Report Date]) = 07")

You will have to replace my field names [Submitted by] & [Report Date] with your field names from tblJKTMain.

Let me know if that fixes your problem.

[dilara]

It Worked!I just forgot to change the [Report Date] to [Submitted Date].

Thanks Robeen

Please dont get tired of helping/teaching newbies like me

[Robeen]

Happy to help!!
All the best - and I will certainly continue to try & help when I can - since I've also received some good help here in the past.

[dilara]

Happy to help!!
All the best - and I will certainly continue to try & help when I can - since I've also received some good help here in the past.

Hi Robeen, hope this reaches you.

Is it possible to have more than 1 criteria for a particular field? Say i would want to count total for 3 users (BMSKM1, BMSBM2 and BMSMFN), in

=DCount("[Form Type]","tblJKTMain","[Submitted by] = 'BMSCTM' AND Month ([Date Submitted]) = 07")

I asked this for contigency purposes since there might be days where users who dont belong in our group will just fill-in, incase of Leaves/Absences. I would want their total to reflect as "others"

Thanks.

[Robeen]

Try this:

DCount("[Form Type]","tblJKTMain","[Submitted by] In ('BMSKM1', 'BMSBM2', 'BMSMFN') AND Month ([Date Submitted]) = 07")