Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


counting events from month to month access query

Posted on 2012-09-09
Medium Priority
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
  • 2
  • 2
LVL 40

Expert Comment

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

Author Comment

ID: 38380742
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 40

Expert Comment

ID: 38380942
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

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

Accepted Solution

GRayL earned 2000 total points
ID: 38383136
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")

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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

581 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