actual v budget variance sales query

[Juventus]

Hello,
I am new to Access.

I am trying to create a query to report actual sales performance v budget by product group, market, customer, rep, region, period, year

I have two tables with the following fields
Actual table with fields Order no, material, product group, market, customer, rep name, region, period, year, actual value
Budget tables with fields product group, market, customer, rep name, region, period, year budget value

I have attempted to create a query by
1. joining the two tables by rep name
2. created a rep name table and joined the actual and budget table as a one to many join.

Each query returns skewed actual and budget values ie 258 times and 865 times actual and budget values.

The actual and budget tables do not contain unique fields.


If anyone can suggest a way to create a query that returns the following results would be much appreciated

Rep Name, Region, Product Name, Actual Order value, Budget Order value
J Doe Melb Liquid seals 10,000 5,000


Thanks

 

[plog]

Can you provide better sample data? I will need 2 sets: A--starting sample data from your tables (include table and field names); and B--resulting data based on A (what the query should return based on the sample starting data.

Use this format for posting a table's data:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
David, 13, 2/20/2011
Larry, 44, 6/19/2014
Sally, 28, 8/12/2013

Again, 2 sets of data--starting and resulting.

 

[Juventus]

Hello Plog

Thanks for your assistance.

I have provided the data in the attached excel worksheet.

Please refer worksheet titled Tables which includes data for the two tables - refer column Q for tables name (Actual & Budget) & worksheet titled query.

Essentially I am trying to merge the two data sets and create a query to report the data as per the pivot table. I will then add a calculate field to derive actual v budget variance.

Cheers

 

[plog]

First, these shouldn't be in 2 different tables. They should be in one table. Then to differentiate between budget and actual, you have a field to tell you to which one the row belongs.

If you don't have control over that, then you should create a UNION query (http://www.techonthenet.com/sql/union.php) to merge the two datasets together. Once you have that, you build another query using the UNION query as its source and GROUP and SUM as necessary. The real key is the UNION, once your data is all together, its a simple query to aggregate your data.

 

[Juventus]

Hello Plog, I have identified my problem my two tables had inconsistent number of fields and field names, I am now on track thanks for your assistance