Getting audit trail

-- -- 706 Reputation points


I have a table as below which is bound to an MS Access form in front end app.

ID PK AutoNumber

EventID Number

Qty Number

Total Currency

ModifiedOn Date/Time

How can I get a report of variations to Qty and Total over a date period? I need the variations on a per EventID basis so I can check what qty/amounts have gone up and down and if I need to check which event they pertain to then I also get the EventID with the variation.



Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
798 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Vahid Ghafarpour 1,845 Reputation points

    You can use this query as the data source for your report and design the report layout as per your requirements, displaying the relevant information about variations in Qty and Total on a per EventID basis.

        t.Qty AS PreviousQty, 
        t.Total AS PreviousTotal, 
        t.ModifiedOn AS PreviousModifiedOn,
        t2.Qty AS CurrentQty, 
        t2.Total AS CurrentTotal, 
        t2.ModifiedOn AS CurrentModifiedOn
        YourTableName AS t
        YourTableName AS t2 ON t.EventID = t2.EventID
        t.ModifiedOn >= #StartDate# AND t.ModifiedOn <= #EndDate#
        AND t.ID <> t2.ID
        t.EventID, t.ModifiedOn DESC;
    0 comments No comments

  2. Tanay Prasad 325 Reputation points


    To generate a report of variations in the "Qty" and "Total" fields over a date period on a per "EventID" basis, you can use a query in Microsoft Access. Here's an example of how you can create the query:

    Open your Access database and navigate to the "Create" tab.

    Click on the "Query Design" button to create a new query in Design View.

    In the "Show Table" dialog box, select the table that contains your data and click "Add."

    Close the "Show Table" dialog box.

    In the query designer, add the following fields to the design grid: "EventID," "Qty," "Total," and "ModifiedOn."

    In the "Criteria" row of the "ModifiedOn" field, enter the date range you want to filter by, such as:

    • Between [Start Date] And [End Date]
    • Replace [Start Date] and [End Date] with the actual date criteria, like #01/01/2023# and #12/31/2023#.

    In the "Query Type" section of the "Design" tab, select "Group By."

    Right-click on the "Qty" field and select "Group By."

    Right-click on the "Total" field and select "Group By."

    In the "Design" tab, click on the "Run" button to execute the query.

    The query results will display the variations in "Qty" and "Total" for each unique "EventID" within the specified date range. You can export the query results to a report for better presentation or further analysis.

    I hope this helps!

    Best Regards.

    0 comments No comments