Discussion :

[ALEX80800]

Bonjour,

Je suis aller sur ce tuto pour voir l'incrémentation d'un ID

J'ai essayé sur ma requête suivante qui est l'union d'une même requête mais dont un champ est changé à chaque select.

SELECT [Liste Etb & Antennes].Structure, [Base 2011].[N° Pièce], [Base 2011].[N° Ecriture], Month(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS Mois, IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2)))) AS [Date Comptable], [Base Transco].Nature, [Base 2011].Compte, [Base 2011].[Libellé Compte], [Base 2011].[Journal], [Base 2011].[Libellé Comptable], [Base 2011].Activité, [Base 2011].Antennes, [Base 2011].Débit, [Base 2011].Crédit, [Débit]-[Crédit] AS Solde, IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Left([DebutFin],8),4)),Val(Mid(Left([DebutFin],8),3,2)),Val(Left(Left([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Début], IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Right([DebutFin],8),4)),Val(Mid(Right([DebutFin],8),3,2)),Val(Left(Right([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Fin]
FROM ([Base Transco] INNER JOIN [Base 2011] ON [Base Transco].[NIV 5] = [Base 2011].Compte) INNER JOIN [Liste Etb & Antennes] ON [Base 2011].[N° Structure] = [Liste Etb & Antennes].Num
WHERE (((Year(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))))=2011))
UNION ALL
SELECT [Liste Etb & Antennes].Structure, [Base 2011].[N° Pièce], [Base 2011].[N° Ecriture], Month(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS Mois, IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2)))) AS [Date Comptable], [Base Transco].[NIV 3], [Base 2011].Compte, [Base 2011].[Libellé Compte], [Base 2011].[Journal], [Base 2011].[Libellé Comptable], [Base 2011].Activité, [Base 2011].Antennes, [Base 2011].Débit, [Base 2011].Crédit, [Débit]-[Crédit] AS Solde, IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Left([DebutFin],8),4)),Val(Mid(Left([DebutFin],8),3,2)),Val(Left(Left([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Début], IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Right([DebutFin],8),4)),Val(Mid(Right([DebutFin],8),3,2)),Val(Left(Right([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Fin]
FROM ([Base Transco] INNER JOIN [Base 2011] ON [Base Transco].[NIV 5] = [Base 2011].Compte) INNER JOIN [Liste Etb & Antennes] ON [Base 2011].[N° Structure] = [Liste Etb & Antennes].Num
WHERE (((Year(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))))=2011))
UNION ALL
SELECT [Liste Etb & Antennes].Structure, [Base 2011].[N° Pièce], [Base 2011].[N° Ecriture], Month(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS Mois, IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2)))) AS [Date Comptable], [Base Transco].[NIV 2], [Base 2011].Compte, [Base 2011].[Libellé Compte], [Base 2011].[Journal], [Base 2011].[Libellé Comptable], [Base 2011].Activité, [Base 2011].Antennes, [Base 2011].Débit, [Base 2011].Crédit, [Débit]-[Crédit] AS Solde, IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Left([DebutFin],8),4)),Val(Mid(Left([DebutFin],8),3,2)),Val(Left(Left([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Début], IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Right([DebutFin],8),4)),Val(Mid(Right([DebutFin],8),3,2)),Val(Left(Right([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Fin]
FROM ([Base Transco] INNER JOIN [Base 2011] ON [Base Transco].[NIV 5] = [Base 2011].Compte) INNER JOIN [Liste Etb & Antennes] ON [Base 2011].[N° Structure] = [Liste Etb & Antennes].Num
WHERE (((Year(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))))=2011))
UNION ALL
SELECT [Liste Etb & Antennes].Structure, [Base 2011].[N° Pièce], [Base 2011].[N° Ecriture], Month(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS Mois, IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2)))) AS [Date Comptable], [Base Transco].[NIV 1], [Base 2011].Compte, [Base 2011].[Libellé Compte], [Base 2011].[Journal], [Base 2011].[Libellé Comptable], [Base 2011].Activité, [Base 2011].Antennes, [Base 2011].Débit, [Base 2011].Crédit, [Débit]-[Crédit] AS Solde, IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Left([DebutFin],8),4)),Val(Mid(Left([DebutFin],8),3,2)),Val(Left(Left([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Début], IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Right([DebutFin],8),4)),Val(Mid(Right([DebutFin],8),3,2)),Val(Left(Right([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Fin]
FROM ([Base Transco] INNER JOIN [Base 2011] ON [Base Transco].[NIV 5] = [Base 2011].Compte) INNER JOIN [Liste Etb & Antennes] ON [Base 2011].[N° Structure] = [Liste Etb & Antennes].Num
WHERE (((Year(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))))=2011))
UNION
ALL SELECT [Liste Etb & Antennes].Structure, [Base 2011].[N° Pièce], [Base 2011].[N° Ecriture], Month(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS Mois, IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2)))) AS [Date Comptable], [Base Transco].[NIV 0], [Base 2011].Compte, [Base 2011].[Libellé Compte], [Base 2011].[Journal], [Base 2011].[Libellé Comptable], [Base 2011].Activité, [Base 2011].Antennes, [Base 2011].Débit, [Base 2011].Crédit, [Débit]-[Crédit] AS Solde, IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Left([DebutFin],8),4)),Val(Mid(Left([DebutFin],8),3,2)),Val(Left(Left([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Début], IIf([DebutFin] Like "*§*",DateSerial(Val(Right(Right([DebutFin],8),4)),Val(Mid(Right([DebutFin],8),3,2)),Val(Left(Right([DebutFin],8),2))),IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))) AS [Date Fin]
FROM ([Base Transco] INNER JOIN [Base 2011] ON [Base Transco].[NIV 5] = [Base 2011].Compte) INNER JOIN [Liste Etb & Antennes] ON [Base 2011].[N° Structure] = [Liste Etb & Antennes].Num
WHERE (((Year(IIf([Date] Is Not Null,DateSerial(Val(Left([Date],4)),Val(Mid([Date],5,2)),Val(Right([Date],2))),DateSerial(Val(Left(20110101,4)),Val(Mid(20110101,5,2)),Val(Right(20110101,2))))))=2011));

J'aurais voulu savoir s'il est possible d'affecter une numérotation automatique sur cette requête union.

[ALEX80800]

Cette demande est non avenue car c'est la source que je dois numéroter et non la requête