0

I have created a query in Access 2010 that maps a number in the right table into the left table. when i run the query i get less results than the total number of records in the left table and i wonder why? it seems that some records are left out. Does somebody have a clue on what i am doing wrong?

edit: i cant post pictures so i´ll draw this here in a simplified way:

-table 1....-........-table 2...-..........-table 3... -

-number 1-.<->.-number 1-.........-xxxxxxxx...-

-xxxxxxxx...-.......-number 2-..<->..-number 2-

Query:

SELECT [2007].[Analyse (Nummer)], 
       [2007].[Analyse (Name)], 
       [2007].Faktura, BKPF2007.Referenz, 
       BSET.St 
FROM BSET 
INNER JOIN (2007 INNER JOIN BKPF2007 ON [2007].[Faktura] = BKPF2007.[Belegnr]) 
       ON BSET.[Belegnr] = BKPF2007.[Referenz] 
GROUP BY [2007].[Analyse (Nummer)], 
         [2007].[Analyse (Name)], 
         [2007].Faktura,
         BKPF2007.Art, 
         BKPF2007.Referenz, 
         BSET.St;
Community
  • 1
  • 1
chrnit
  • 17
  • 2
  • 8
  • 2
    Can you please provide your query? – Apothis Aug 21 '14 at 21:01
  • Inner join means if the row is not found in both tables, drop it. Left join (outer joins) will preserve all rows on the left table. If that doesn't answer your question, post the query plz – Twelfth Aug 21 '14 at 21:02
  • "Maps a number"? Can you explain this? Post your SQL and sample data; it will help everyone involved, including yourself. – Mark C. Aug 21 '14 at 21:02
  • SELECT [2007].[Analyse (Nummer)], [2007].[Analyse (Name)], [2007].Faktura, BKPF2007.Referenz, BSET.St FROM BSET INNER JOIN (2007 INNER JOIN BKPF2007 ON [2007].[Faktura] = BKPF2007.[Belegnr]) ON BSET.[Belegnr] = BKPF2007.[Referenz] GROUP BY [2007].[Analyse (Nummer)], [2007].[Analyse (Name)], [2007].Faktura, BKPF2007.Art, BKPF2007.Referenz, BSET.St, – chrnit Aug 21 '14 at 21:07
  • You ended that code w/ a comma which would be a syntax error, is that the whole query? – crthompson Aug 21 '14 at 21:13
  • Well, you are right, i left out a few fields, but they are unnecessary here. all the logic is included. all of it comes from access, so it should be ok. – chrnit Aug 21 '14 at 21:21
  • @chrnit, thats fine, please edit the code so it both removes the unnecessary and is valid sql. It goes a long way to help us help you. – crthompson Aug 21 '14 at 21:23

2 Answers2

1

That's one of the functions of JOIN - it does not show records that don't match. There are a few things I can suggest without seeing specifics.

  1. Remove one of the joins, so your query only shows Table 1 and Table 2. Make sure you get the expected results before moving on to joining Table 3
  2. Replace your joins with LEFT JOINS which show all records even if there is no match in the right-hand table. Even if this doesn't give you the solution you're hoping for, it may help you diagnose the problem.
Scott
  • 3,663
  • 8
  • 33
  • 56
  • While the title of that question seems to fit this one perfectly, the actual question and the answers do not. – crthompson Aug 21 '14 at 21:04
  • I felt the solution presented was appropriate for diagnosing and correcting a join problem. I added the relevant quote to my answer. – Scott Aug 21 '14 at 21:05
  • If you read the comments to that answer, the OP has a cartesian product if he does that and gets duplicate rows. [ypercube](http://stackoverflow.com/a/10303332/2589202)'s answer is the right one for THAT question. – crthompson Aug 21 '14 at 21:08
  • My apologies. I'll modify my answer. – Scott Aug 21 '14 at 21:09
  • thanks, so looking at the code i provided above, i´ll just remove the joins one by one and see if it helps? i´m asking because there are only two of them. – chrnit Aug 21 '14 at 21:13
  • Try writing a query joining just 2007 and BKPF2007 and see if you're missing data. That will help you determine where the cause is before you add in BSET. *OR* replace INNER JOIN with LEFT JOIN and you'll get null results but a full recordset. – Scott Aug 21 '14 at 21:19
  • Yes, the second option is what i need. Although, when i replace INNER JOIN with LEFT JOIN, Access tells me that this JOIN expression is not supported.. – chrnit Aug 21 '14 at 21:31
  • @chrnit Access definitely supports an inner join. You have some weird join syntax going on, that needs to be fixed before you can just replace `left join` with `inner join` – crthompson Aug 21 '14 at 21:34
  • `LEFT OUTER JOIN` throws the same error. Though i can do a `RIGHT JOIN`, why not left, `RIGHT JOIN (2007 LEFT JOIN BKPF2007 ON [2007].[Faktura] = BKPF2007.[Belegnr])` works somehow but i am getting more results than in the base table. gosh, this is frustrating. – chrnit Aug 21 '14 at 21:37
  • Somewhere else i read this: This doesn't work because it is documented Left cannot be nested outside inner join. Can somebody write the code differently? – chrnit Aug 21 '14 at 21:43
0

This is the reason for your results being filtered:

INNER JOIN

You will need to make this a LEFT JOIN to keep all results where results do not exist on the right tables(s).

Apothis
  • 406
  • 3
  • 7