Determine difference between two group of records on two different tables based on date 1

[BxWill]

Have two linked text file tables in MS Access - Aug 2013 Customer Data and July 2013 Customer Data.

Is it possible to create a make table query that will consist of the difference between the similar groups of data on the two tables?

For example, if I have three records with sales activity of $100,000 for customer A during July 2013 and $250,000 of sales activity for customer A (8 records) during August 2013. Six records displaying total sales during July 2013 for customer B of $50,000 and four records displaying total sales during August 2013 for customer B of $100,000. Variance for sales would be $100,000.

I would like to generate a table that contain a record for the sale variance (across all customers). In essence, the sales activity for customer A, customer B, etc. during August 2013 relative to the sales activity for customers during July 2013.

Note, this is a simplified example using just two customers. My actual files contain thousands of customers but the categories that I would like to record a variance for are the same such as sales, phone expense, maintenance expense, etc.

Ideally, I would like to create a monthly Variance Report that displays the variance between the current month relative to the prior month.

Is this possible in MS Access? MS Excel?

If so, how is this achieved?

Thanks in advance.

 

[dhookom]

I have done something similar in the past with employee tables. My unique field was the EmployeeID so I created a couple union queries like:

SELECT "Current" as Table, EmployeeID, "FirstName" as Fld, FirstName as CurrentValue
FROM tblEmployees
UNION ALL
SELECT "Current", EmployeeID, "LastName", LastName
FROM tblEmployees
UNION ALL
SELECT "Current", EmployeeID, "Address", Address
FROM tblEmployees
UNION ALL
SELECT "Current", EmployeeID, "City", City
FROM tblEmployees

Create a similar query from the new table:

SELECT "New" as Table, EmployeeID, "FirstName" as Fld, FirstName as NewValue
FROM tblEmployeesNew
UNION ALL
SELECT "New", EmployeeID, "LastName", LastName
FROM tblEmployeesNew
UNION ALL
SELECT "New", EmployeeID, "Address", Address
FROM tblEmployeesNew
UNION ALL
SELECT "New", EmployeeID, "City", City
FROM tblEmployeesNew

I then created a query of the two union queries joining on EmployeeID and Fld. Set the criteria under CurrentValue to:
<>NewValue

Duane
Hook'D on Access
MS Access MVP