I have a scenario where I process data in two tables and extract certain fields into a third table for exporting to Excel.
The tables "apples costs " and "apples_export" have one record per ODS whereas table "apples vmp import" could have several records per ODS.
The query below is a check that the values before processing remain unaltered in the export file.
Code:
SELECT Sum([apples_export].[Avg_Discounted_Total]) AS Export_Avg_Discounted_Total, Sum([apples costs import].[Avg_Discounted_Total]) AS Import_Avg_Discounted_Total1, Sum([apples_export].[Avg_Disc_CatC]) AS Export_Avg_Disc_CatC, Sum([apples vmp import].[Average_Disc_CatC]) AS Import_Average_Disc_CatCFROM (apples_export INNER JOIN [apples costs import] ON apples_export.ODS = [apples costs import].ODS) INNER JOIN [apples vmp import] ON apples_export.ODS = [apples vmp import].ODS;
However the result of the query gives a spurious figure for the "Export_Avg_Disc_CatC" as the table actually contains £55,017.16 (see picture).
I've never created a query based on multiple tables before, where am I going wrong?