My total doubles up

[Blunder1]

hi,

I new to access and an having trouble with joining muytliple tables... If i use the total and sum the two columns i need, it doubles the amount. I have run the query with no total and every row is doubled. I cant seem to see why this is happening and how to fix it.

Can anyone help?

Thanks

Blunder

SELECT COS_MAP.[Market of Settlement - Short Name], Entity_Map.[Entity name (re-worked)], AsiaandDTCVolume.[Russ - Region] AS [Processing Center], Depot_Map.[Market Partner], Sum(AsiaandDTCVolume.[Good Trades BO]) AS [Settlement Volume], Sum(NonABCFails.[Russ - Fails No]) AS [Fail Number], AsiaandDTCVolume.[Firm Depo Clearing Location ID], AsiaandDTCVolume.[Firm's Nostro Clearing Location ID], Depot_Map.Country, Depot_Map.[Depot Name]
FROM (((AsiaandDTCVolume LEFT JOIN COS_MAP ON AsiaandDTCVolume.[Market of Settlement Code] = COS_MAP.[Market of Settlement Code]) LEFT JOIN Entity_Map ON AsiaandDTCVolume.[Entity Id (OPP)] = Entity_Map.[Entity ID]) LEFT JOIN NonABCFails ON (AsiaandDTCVolume.[Firm's Nostro Clearing Location ID] = NonABCFails.[Firms Nostro Clearing Location Id]) AND (AsiaandDTCVolume.[Market of Settlement Code] = NonABCFails.[Market of Settlement Code]) AND (AsiaandDTCVolume.[Firm Depo Clearing Location ID] = NonABCFails.[Firms Depot Clearing Location Id])) LEFT JOIN Depot_Map ON AsiaandDTCVolume.[Firm Depo Clearing Location ID] = Depot_Map.Depot
GROUP BY COS_MAP.[Market of Settlement - Short Name], Entity_Map.[Entity name (re-worked)], AsiaandDTCVolume.[Russ - Region], Depot_Map.[Market Partner], AsiaandDTCVolume.[Firm Depo Clearing Location ID], AsiaandDTCVolume.[Firm's Nostro Clearing Location ID], Depot_Map.Country, Depot_Map.[Depot Name];

 

[sheetspread]

Have you checked if the records repeat because of the join?

 

[Joe4]

It usually means one of two things:

1. You have not done a join correctly between two tables. Maybe a join requires more than one field to be joined to create a one-to-one match, and your forgot to include that.

2. You have a one-to-many or many-to-many between two of your tables, causing the duplication.

Without knowing how your tables are related and seeing your data, it is virtually impossible for us to analyze that for you. It looks like you are joining 5 tables.

Here is what I would recommend doing to try to figure this out. It looks like you are joining 5 tables.

- Start off joining your first two tables ONLY, and do not make it an Aggregate Query yet.
- Inspect your data. Do you see fields duplicated (especially the ones you are trying to total?)
- If not, add in another table and repeat the process until you find where things are being duplicated.

Once you find where it is being duplicated, you know that it is that last relationship between your tables that is the problem, and you know where to focus your attention on fixing it.

 

[Blunder1]

Thank you both for your help, it makes sense what i need to do!