CODEWITHSUNDEEP
sqlms-accessconcatenation
dan
dan

Reputation: 3519

Concatenate 2 rows in a complex SQL query

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

Upvotes: 1

Views: 607

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 1

Related Questions