Spurious result in multi table query

[keith2511]

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.

 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?

[Minty]

Can you post up a picture of the relationships between the three tables.
From you description I don't know why you would need the third table "apples vmp import" in the query at all?