Concatenate 2 rows in a complex SQL query

Question

I'm using MS-Access 2003 with the query creator. I select everything from one table (FaitsSaillants), then one specific row (WHERE VARIABLE='TitreMandat') from another table (tb_SOMMAIRE). I want to select another row from that second table and concatenate it.

The query

PARAMETERS
    [CurrAxe] Text ( 255 ), [CurrOTP] Text ( 255 ),
    [CurrClient] Text ( 255 ), [StartDate] DateTime, [EndDate] DateTime;
SELECT 
    tb_SOMMAIRE.Valeur AS Projet, tb_SOMMAIRE.VARIABLE, *
FROM
    (FaitsSaillants
     LEFT JOIN Employes
         ON FaitsSaillants.Utilisateur = Employes.CIP)
    INNER JOIN tb_SOMMAIRE
        ON FaitsSaillants.OTP = tb_SOMMAIRE.OTP
WHERE
    (((FaitsSaillants.OTP)=[CurrOTP]) AND 
     ((FaitsSaillants.Client) Like [CurrClient]) AND
     ((FaitsSaillants.Axe) Like [CurrAxe]) AND
     ((DateValue([DateInsertion]))>=[StartDate] AND
      (DateValue([DateInsertion]))<=[EndDate]) AND
     ((tb_SOMMAIRE.VARIABLE)='TitreMandat'))
ORDER BY
    FaitsSaillants.DateInsertion DESC;

This query does add the tb_SOMMAIRE.Valeur field where the IDs (OTP field) match and where tb_SOMMAIRE.VARIABLE='TitreMandat'. It works like a charm. However, I want to add another row to the tb_SOMMAIRE results. I would like to get the row where VARIABLE='TitreMandat' (that part is actually working) and the row where VARIABLE='NomInstallation'. I will get 2 rows and I want those 2 rows to be concatenated and displayed when I ask for Projet (tb_SOMMAIRE.Value as Projet). Both rows' OTP (IDs) are the same as the one selected in FaitsSaillants.

Sorry if it's in French.

Tables' structure

FaitsSaillants

Index AutoNumber
Projet Text
Axe Text
Client Text
OTP Text
FaitSaillant Memo
DateInsertion Date
Utilisateur Text

tb_SOMMAIRE

OTP Text
VARIABLE Text
Valeur Text

Data example

tb_SOMMAIRE

OTP   VARIABLE        Valeur
UGPSW NomInstallation PosteNemiscau
UGPSW TitreMandat     oscilloperturbographe
UGPSW RespIng         CU9656
GWIHK NomInstallation AnotherInstallation
GWIHK TitreMandat     Anytitle
GWIHK Responsable     ImportantPerson

Show some example data and example results

Laurence
– Laurence

2012-10-31 13:29:03 +00:00
Commented Oct 31, 2012 at 13:29 — Laurence
– Laurence, Commented Oct 31, 2012 at 13:29

Fionnuala · Accepted Answer · 2012-10-31 14:31:52Z

1

How about:

PARAMETERS [CurrAxe] TEXT ( 255 ), [CurrOTP] TEXT ( 255 ), [CurrClient] TEXT (
255 ), [StartDate] DATETIME, [EndDate] DATETIME;

SELECT q.Projet, *
FROM   (faitssaillants f
LEFT JOIN employes e
ON f.utilisateur = e.cip)
INNER JOIN (
SELECT s1.otp,
     [s1].[valeur] & "," & [s2].[valeur] AS Projet
FROM   (
  SELECT otp, valeur
  FROM   tb_sommaire
  WHERE  [variable] = 'TitreMandat') AS s1
INNER JOIN (
   SELECT otp, valeur
   FROM   tb_sommaire 
   WHERE  [variable] = 'NomInstallation') AS s2
ON s1.otp = s2.otp) q
ON f.otp = q.otp
WHERE f.otp  = [currotp] 
AND f.client LIKE [currclient] 
AND f.axe LIKE [curraxe] 
AND Datevalue([dateinsertion]) 
    Between [startdate] And [enddate] 
ORDER  BY f.dateinsertion DESC;

It is always best to avoid referencing all fields as *. Fields (columns) should be listed by name.

The above depends on creating a derived table that groups rows from tb_sommaire by Otp. You can cut and paste the derived table into a query design screen (sql view) to check that the rows returned are as expected.

edited Oct 31, 2012 at 14:31

answered Oct 31, 2012 at 13:29

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

dan Over a year ago

Even if there are only 5 parameters, I got an error saying I need to enter 6. The 6th parameter is q.value???

dan Over a year ago

Another error message. It's in French, but basically, it says the syntax is incorrect or too complex to be evaluated. It suggests to assign parts to variables. However, the query seems to render correctly in the design window, it's when it's executed that I get the error message. Thanks for your help by the way, really much appreciated.

Fionnuala Over a year ago

This is a different problem. Is faitssaillants a table or a query? If it is a query that is likely to be the problem and you will need to post the sql. If not, you will need to include more information about you table structures and data in your question.

Fionnuala Over a year ago

let us continue this discussion in chat

Fionnuala Over a year ago

No. At some stage you must move on, however, you can refer to a query in the query design window, so you could build it up with three queries created in the query design window. This is not the worst way to get an understanding of the SQL involved. Once yu have the three queries, you can start substituting the name of the query with the SQL to create a derived table.

|

Collectives™ on Stack Overflow

Concatenate 2 rows in a complex SQL query

The query

Tables' structure

Data example

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

The query

Tables' structure

Data example

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related