Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2012
    Posts
    6

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

    Hi,

    I am totally clueless on how will i do this or if its possible.

    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 on a monthly basis and per form type:

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

    Thanks for helping
    Attached Thumbnails Attached Thumbnails stat.JPG  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes what you want can be done
    you could use a series of queries that SUM or COUNT or AVERAGE whatever metric you are looking at GROUP BY what ever sorting and grouping you want. Bear in mind if you use GROUP BY and you need a where clause to limit rows then you need to use the HAVING expression

    the first step is to marshall the data, having done that then think about the presentation
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2012
    Posts
    6
    Can you give me a more detailed procedure? Please pardon me as my previous experience with access is limited on conceptualization only. Before i have a staff to do for me but now, i should be doing by myself. Thank you.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Before we get to your question, let's clear something else up. You can view data in a table, filter/organize it in a query, add data in an organized manner in a form, and display it in a report. Forms, such as switchboards, are also used to house command buttons to command Access to perform individual tasks. However, you don't display data in forms but in reports.

    Having gotten that out of the way, let's see what healdem wrote. By the way, healdem's always right. Iv'e never seen him out on a limb, although he claims that to be his location.

    Reports are built on recordsources. The record source for a report can either be a table or a query. Since you want to summarize your data, and filter it depending on year (you don't want last year's January data coming out with this year's January data, do you?), it will no doubt be a query.

    In order to summarize the data in a query, it will have to be a Totals query. That being the case, you will have to add, count, or average the correct column(s) (depending on the method of summary), and detailing the column data correctly using the having clause. Also, since you're summarizing by date, and filtering by year, you'll need a where clause.

    All these are available for the asking in the Help file, which is excellent. Don't attempt this complex query without consulting it if you're not familiar with the clauses and filters and how they work.

    We'll be here to help you if you get stuck, but I'm giving you the opportunity of learning this once for a lifetime.

    Good luck,

    Sam

  5. #5
    Join Date
    Jun 2012
    Posts
    6

    Thank you for your replies.

    I have done a count query and it worked fine. However, I want a more customed layout and it would be more practical for all users to see the stats in the start-up form. Having said that, i did managed to show the count through unbound textbox unsing:
    =DCount("[Form Type]","tblJKTMain","[Submitted by] = 'BMSCTM'") as the control source.

    The problem now is that, the amount returned is the TOTAL records. How can i get the number records for a particular month?

    Thanks

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a where clause to extract records for a specific month
    OR
    use a group by clause if you wanted say allmonths

    use a where clause and a group by if say you wanted the last n months

    your where clause acts as a filter / limiter

    say you wanted the records since the start of the year
    where adatecolumn >= "#01/01/" & year(now()) & "#"

    then using a group by year(adatecolumn), month(adatecolumn)

    you would get values month by month
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2012
    Posts
    6
    What specific string should the control source of the text box look like?

    Please pardon my ignorance or lack of knowledge, i've been "google-searching" for possible remedies the wholeday but i cant seem to find one

    Thank you

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    get the query right before worrying about the controls or whatever.
    once you have the data marshalled then you can start qorking out how to assign any value to the control.

    havign said that if all you want is a simple sum then =dsum('<the name of the column to be summed>', '<the table or query name>',',<the where clause [ without the where]>')
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2012
    Posts
    6
    Hi healdem. I did not made a query to get the count of records from my tblJKTMain. But if necessary, here it is:

    SELECT Format([Date Submitted],"mmmm") AS [Month], tblJKTMain.[Submitted by] AS Username, Count(tblJKTMain.[Form Type]) AS NBRs
    FROM tblJKTMain
    GROUP BY Format([Date Submitted],"mmmm"), tblJKTMain.[Submitted by]
    ORDER BY Format([Date Submitted],"mmmm"), tblJKTMain.[Submitted by];

    That statement will display the attached file.

    My problem with this is that i don't know how show all count for all USERNAMES. Further, by next week, record for August should come in, that will again added another 2-3 rows for the query.

    Please also note that what im trying to accomplish is to display count of records from multiple table source.

    Hope to here from you soon and thank you for your patience
    Attached Thumbnails Attached Thumbnails count query.JPG  

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it doesn't matter if its from one, two or hordes of tables...

    get the query right.

    once the query is right (ie right values) then you can stuff those values into controls on a for or report

    incidentally your group by clause is wrong, that will group by month, biut it will stuff all August into the same row ie Aug 2012,2011.........1900)
    and worse still it will sort by month name, not month number IE APR,AUG,FEB.....SEP
    bear in mind that dates can be expressed n multiple ways and you do not need to do the formatting of date value in the query itself, it can be done in the form or report
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jun 2012
    Posts
    6
    Any specific suggestion on how can i correct the query? or the control source on the form?Thank you

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    add the username to the grtoup by clause (along with the year)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •