Combining (Joining) Two Reports Creates Multiplied Numbers

[sixsigma125]

I have two reports that I'm utilizing in access that are joined via a common key, which we'll call "Store Number".

Both reports (brought in as linked tables) are identical, with the exception of their data columns [though named the same].

So, for example, on each imported table, there are three columns. Store Number, Flavor, and POS. The only difference is that the first table has POS numbers from last month, and the second file has POS numbers from Last week. The goal was to make a report that shows Store Number, Flavor, and POS Last Month, POS Last Week in an excel pivot table (exported). However, whenever I link the two reports via "Store Number", POS Last Month brings back some astronomically large number, much higher than what it should be (summed, of course). What could cause this? I've copied the SQL below. Maybe a syntax issue?


SELECT [Test File 1].[Flavor], [Test File 1].[Range 1 LM POS Sales], [Test File 2].[Range 1 LWK POS Sales]
FROM [Test File 1] INNER JOIN [Test File 2] ON [Test File 1].[Store Number] = [Test File 2].[Store Number]
GROUP BY [Test File 1].[Flavor], [Test File 1].[Range 1 LM POS Sales], [Test File 2].[Range 1 LWK POS Sales];

[June7]

So this is a single report, not report/subreport? Possibly instead of a single report with a query that joins data, should be report/subreport arrangement. Maybe even 2 subreports. Main report for the parent StoreNumber record. Then a subreport each for last month and last week.