CODEWITHSUNDEEP
sqlms-access-2010
rohrl77
rohrl77

Reputation: 3337

MS Access 2010 query returns more rows than in table

I have a query in MS Access 2010 which returns more rows than there are in the source table.

I have tried to recreate what is happening with a simplified example, but could not get the same effect. I have set the parameters so that all records in the source table should be included, but only those that match from the other tables.

The tbl_Toyota2012 has 178'205 records... the result of the query returns 180'365 records.

Here is my SQL code:

SELECT 
   tbl_Toyota2012.[FC-Teile-Nr], tbl_Toyota2012.BEZEICHNUNG, tbl_Toyota2012.PFC, 
   tbl_Toyota2012.PC, tbl_Toyota2012.WARENGRUPPE, tbl_PriceCodes.PRICE_CODE, 
   tbl_PriceCodes.NORMAL_FACTOR, tbl_Toyota2012.EK_NETTO_MANDANT AS EP, 
   tbl_Toyota2012.UPE, tbl_Toyota2012.RABATTSATZ, tbl_NWGW_Key.ZUTEILUNG, 
   tbl_Toyota2012.UMSATZ_2012_UNITS AS [Volumen 2012], 
   [tbl_Toyota2012]![UMSATZ_2012_UNITS]*[tbl_Toyota2012]![UPE]*(1-([tbl_Toyota2012]![RABATTSATZ]/100)) AS NettoUmsatz

FROM 
   (tbl_Toyota2012 
LEFT JOIN 
   tbl_NWGW_Key ON (tbl_Toyota2012.PC = tbl_NWGW_Key.ProdCode) AND (tbl_Toyota2012.PFC = tbl_NWGW_Key.PFC)) 
LEFT JOIN 
   tbl_PriceCodes ON (tbl_Toyota2012.MODELL_ID = tbl_PriceCodes.MODEL_ID) AND (tbl_Toyota2012.PC = tbl_PriceCodes.PROD_CODE) AND (tbl_Toyota2012.PFC = tbl_PriceCodes.PROD_FUNC_CODE);

Can anyone tell me why this might happen?

Upvotes: 1

Views: 2230

Answers (1)

GrandMasterFlush
GrandMasterFlush

Reputation: 6409

This will happen if there is more than a 1:1 relationship between tbl_Toyota2012 and the two tables you are joining on (PriceCodes and tbl_NWGW_Key) as there will be more rows.

i.e. If for each entry in tbl_Toyota2012 there are several entries in PriceCodes or tbl_NWGW_Key.

To answer your comments:

If you have a table 'Parent' that joins to table 'Children' a parent with two children will return more than one line.

SELECT Parent.Name, Child.Name FROM Parent INNER JOIN Child on Parent.ID = ChildID

This might return:

[Parent Name], [Child Name]

Bob, Susan

Bob, David

Upvotes: 4

Related Questions