Stack Exchange
How to match column
I have made this sql from Acces database. What I wanted to create is the following:
Grootboekrek   omschrijving  jan    feb   maart   april  mei ......
1111111        debiteuren   10.000   500.......

I wanted to create in one qry in several column the data with period.
SELECT    qry_gb_lijst.grootboek,
      qry_gb_kmf_jan_2018.saldo   AS Jan,
      qry_gb_kmf_feb_2018.saldo   AS Feb,
      qry_gb_kmf_mrt_2018.saldo   AS Maart,
      qry_gb_kmf_april_2018.saldo AS April,
      qry_gb_kmf_mei_2018.saldo   AS Mei,
      qry_gb_kmf_juni_2018.saldo  AS Juni,
      qry_gb_kmf_juli_2018.saldo  AS Juli,
      qry_gb_kmf_aug_2018.saldo   AS Aug,
      qry_gb_kmf_sep_2018.saldo   AS Sep,
      qry_gb_kmf_okt_2018.saldo   AS Okt,
      qry_gb_kmf_nov_2018.saldo   AS Nov,
      qry_gb_kmf_dec_2018.saldo   AS [dec]
FROM      qry_gb_lijst
LEFT JOIN (((((((((((qry_gb_kmf_jan_2018
LEFT JOIN qry_gb_kmf_feb_2018
ON        qry_gb_kmf_jan_2018.grootboek = qry_gb_kmf_feb_2018.grootboek)
LEFT JOIN qry_gb_kmf_mrt_2018
ON        qry_gb_kmf_feb_2018.grootboek = qry_gb_kmf_mrt_2018.grootboek)
LEFT JOIN qry_gb_kmf_april_2018
ON        qry_gb_kmf_mrt_2018.grootboek = qry_gb_kmf_april_2018.grootboek)
LEFT JOIN qry_gb_kmf_mei_2018
ON        qry_gb_kmf_april_2018.grootboek = qry_gb_kmf_mei_2018.grootboek)
LEFT JOIN qry_gb_kmf_juni_2018
ON        qry_gb_kmf_mei_2018.grootboek = qry_gb_kmf_juni_2018.grootboek)
LEFT JOIN qry_gb_kmf_juli_2018
ON        qry_gb_kmf_juni_2018.grootboek = qry_gb_kmf_juli_2018.grootboek)
LEFT JOIN qry_gb_kmf_aug_2018
ON        qry_gb_kmf_juli_2018.grootboek = qry_gb_kmf_aug_2018.grootboek)
LEFT JOIN qry_gb_kmf_sep_2018
ON        qry_gb_kmf_aug_2018.grootboek = qry_gb_kmf_sep_2018.grootboek)
LEFT JOIN qry_gb_kmf_okt_2018
ON        qry_gb_kmf_sep_2018.grootboek = qry_gb_kmf_okt_2018.grootboek)
LEFT JOIN qry_gb_kmf_nov_2018
ON        qry_gb_kmf_okt_2018.grootboek = qry_gb_kmf_nov_2018.grootboek)
LEFT JOIN qry_gb_kmf_dec_2018
ON        qry_gb_kmf_nov_2018.grootboek = qry_gb_kmf_dec_2018.grootboek)
ON        qry_gb_lijst.grootboek = qry_gb_kmf_jan_2018.grootboek;

Can someone tell me why qry_gb_lijst.grootboek match wont work fully

Table definitions and sample data please. Also, can you better describe "match wont work fully"?

My first inclination was to union all 12 query results 1st and then cross tab the results; but then I saw this apperas to be build on a query.  So... Is the source data all in 1 table and would simply doing a cross tab query on it based on Month  with values from saldo work?

Please post error or undesired result as won't work fully is not helpful to us.

You are chaining the conditions.  Instead, you need to go back to the original table each time:
FROM  (((((((((((qry_gb_lijst as l LEFT JOIN
                 qry_gb_kmf_jan_2018
                 ON l.grootboek = qry_gb_kmf_jan_2018.grootboek
                ) LEFT JOIN
                qry_gb_kmf_feb_2018
                ON l.grootboek = qry_gb_kmf_feb_2018.grootboek
               ) LEFT JOIN
               qry_gb_kmf_mar_2018
               ON l.grootboek = qry_gb_kmf_mar_2018.grootboek
              . . .

You are left joining each month to the previous month.  So, if a value disappears in month X in won't appear in any subsequent month.

Hi Gordon,Thank you. How should I do this?

@Barendrecht . . . I thought the answer was quite clear.  You need to compare the keys to l.grootboek in each case.  That is what is shown in this answer.
