Getting audit trail

Question

Hi

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.

Thanks

Regards

Answer 1

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.

SELECT 
    t.EventID, 
    t.Qty AS PreviousQty, 
    t.Total AS PreviousTotal, 
    t.ModifiedOn AS PreviousModifiedOn,
    t2.Qty AS CurrentQty, 
    t2.Total AS CurrentTotal, 
    t2.ModifiedOn AS CurrentModifiedOn
FROM 
    YourTableName AS t
INNER JOIN 
    YourTableName AS t2 ON t.EventID = t2.EventID
WHERE 
    t.ModifiedOn >= #StartDate# AND t.ModifiedOn <= #EndDate#
    AND t.ID <> t2.ID
ORDER BY 
    t.EventID, t.ModifiedOn DESC;

Answer 2

Hi,

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.