counting events from month to month access query

Posted on 2012-09-09
Last Modified: 2012-09-10
I have a number of tables that contain events where data has changed each month. each pk code [abicode] has events transmission events cc events and 8 other events.

I am not sure how to set up the infrastructure to count the number of events  each month.

I have a query counting the events:

SELECT Count(TblTransmission.ChangeId) AS CountOfChangeId, TblTransmission.AbiCode
FROM TblTransmission
GROUP BY TblTransmission.AbiCode
ORDER BY Count(TblTransmission.ChangeId) DESC;

I believe I need to store that value for each code and category of event. then each month do a recount of the events in each category when the new data comes in.

if the event count increased then the [abicode] needs to be reviewed if not then it can be ignored.

so how might i best set that up?

the transmission table as an example currently has these fields:
Changeid autonumber
abicode text
transmissionprev text
transmissionchange text
Question by:PeterBaileyUk
    LVL 39

    Expert Comment

    If you add changeyearmonth to your query, you should get count for every changeyearmonth

    Author Comment

    I created a table and put the counts in there effectively giving me the subcounts stored and then another table with the total count per abicode. then when the new data comes in I can do the compare, if i dont store then there is no way of knowing or i dont think so. hence the post
    LVL 39

    Expert Comment

    If your data could be changed backwards, then there are no other way to find changes, except recording some history.
    May be you can prepare sample DB with some data and show expected result?
    LVL 44

    Expert Comment

    Can you at least post a few dummy records showing examples of the data contained in each field?
    LVL 44

    Accepted Solution

    If your table contained as a minimum:

    ChangeID Integer
    AbiCode  Text
    ChangeDate DateTime

    And you ran this query:

    SELECT AbiCode, Format(ChangeDate, "yyyymm") as Chgyyyymm,  Count(ChangeDate) AS ChgCount
    FROM tblTransmissions
    GROUP BY AbiCode, Format(ChangeDate, "yyyymm")
    ORDER BY AbiCode;

    That will give you the  year month count dynamically for each AbiCode.  I, for one, am still guessing at what you are trying to do.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now