Select PP. Line, PP. Sfpp, PP. Summa, inn. Inn

SELECT PP.LINE, PP.SFPP, PP.SUMMA, INN.INN

FROM INN RIGHT JOIN PP ON INN.INN = PP.INN

GROUP BY PP.LINE, PP.SFPP, PP.SUMMA, INN.INN

HAVING (((Count(INN.INN))=0));

SELECT INN.NAME, INN.INN, INN.ADRESS, BIK.NAME

FROM (BIK INNER JOIN INN ON BIK.BIK=INN.BIK) LEFT JOIN SF ON INN.INN=SF.INN

WHERE (((INN.INN) Not Like "77*"))

GROUP BY INN.NAME, INN.INN, INN.ADRESS, BIK.NAME

HAVING (((Count(SF.INN))=0));

SELECT BIK.NAME, BIK.TOWN, BIK.KOR_SCHET, BIK.BIK

FROM BIK LEFT JOIN INN ON BIK.BIK=INN.BIK

GROUP BY BIK.NAME, BIK.TOWN, BIK.KOR_SCHET, BIK.BIK

HAVING (((Count(INN.INN))=0));

SELECT BIK.NAME, BIK.TOWN, BIK.BIK, Count(INN.INN) AS [Count-INN]

FROM BIK LEFT JOIN INN ON BIK.BIK=INN.BIK

GROUP BY BIK.NAME, BIK.TOWN, BIK.BIK

ORDER BY Count(INN.INN) DESC;

SELECT PP.INN, PP.NSF, PP.DSF, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM PP

GROUP BY PP.INN, PP.NSF, PP.DSF;

SELECT INN.NAME, SF.INN, Count(SF.INN) AS [Count-INN]

FROM (INN INNER JOIN SF ON INN.INN = SF.INN) LEFT JOIN 3_5_0 ON (SF.INN = [3_5_0].INN) AND (SF.NSF = [3_5_0].NSF) AND (SF.DSF = [3_5_0].DSF)

WHERE ((([SUMMA]-IIf(IsNull([3_5_0]![Sum-SUMMA]),0,[3_5_0]![Sum-SUMMA]))>0))

GROUP BY INN.NAME, SF.INN

ORDER BY Count(SF.INN) DESC;

SELECT PP.INN, PP.NSF, PP.DSF, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM PP

GROUP BY PP.INN, PP.NSF, PP.DSF;

SELECT INN.NAME, SF.INN, Sum([SUMMA]-IIf(IsNull([3_5_0]![Sum-SUMMA]),0,[3_5_0]![Sum-SUMMA])) AS Выражение1

FROM (INN LEFT JOIN SF ON INN.INN=SF.INN) LEFT JOIN 3_5_0 ON (SF.DSF=[3_5_0].DSF) AND (SF.NSF=[3_5_0].NSF) AND (SF.INN=[3_5_0].INN)

GROUP BY INN.NAME, SF.INN

ORDER BY Sum([SUMMA]-IIf(IsNull([3_5_0]![Sum-SUMMA]),0,[3_5_0]![Sum-SUMMA])) DESC;

SELECT INN.NAME, INN.INN, Format$([SFPP],"mmm-yyyy") AS Выражение1, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM INN INNER JOIN PP ON INN.INN=PP.INN

GROUP BY INN.NAME, INN.INN, Format$([SFPP],"mmm-yyyy");

SELECT INN.NAME, INN.INN, Format$([SFPP],"mmm-yyyy") AS Выражение1, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM INN INNER JOIN PP ON INN.INN = PP.INN

WHERE (((PP.SFPP) Between #1/1/2004# And #6/1/2004#))

GROUP BY INN.NAME, INN.INN, Format$([SFPP],"mmm-yyyy");

SELECT PP.INN, Format$([SFPP],"mmm-yyyy") AS Выражение1, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM PP

WHERE (((PP.SFPP) Between #1/1/2004# And #6/1/2004#))

GROUP BY PP.INN, Format$([SFPP],"mmm-yyyy");

SELECT INN.NAME, INN.INN, [3_9_0].Выражение1, [3_9_0].[Sum-SUMMA]

FROM INN LEFT JOIN 3_9_0 ON INN.INN = [3_9_0].INN

GROUP BY INN.NAME, INN.INN, [3_9_0].Выражение1, [3_9_0].[Sum-SUMMA];

SELECT BIK.NAME, BIK.BIK, BIK.TOWN, Count(INN.INN) AS [Count-INN], Count(PP.INN) AS [Count-INN1]

FROM BIK LEFT JOIN (INN LEFT JOIN PP ON INN.INN=PP.INN) ON BIK.BIK=INN.BIK

GROUP BY BIK.NAME, BIK.BIK, BIK.TOWN

ORDER BY Count(INN.INN) DESC, Count(PP.INN) DESC;

SELECT INN.NAME, INN.INN, SF.NSF, SF.DSF, SF.SUMMA, PP.SUMMA

FROM (INN INNER JOIN SF ON INN.INN = SF.INN) LEFT JOIN PP ON (SF.DSF = PP.DSF) AND (SF.NSF = PP.NSF) AND (SF.INN = PP.INN)

GROUP BY INN.NAME, INN.INN, SF.NSF, SF.DSF, SF.SUMMA, PP.SUMMA;

SELECT PP.INN, PP.NSF, PP.DSF, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM PP

GROUP BY PP.INN, PP.NSF, PP.DSF;

SELECT INN.NAME, INN.INN, SF.NSF, SF.DSF, Sum(SF.SUMMA) AS [Sum-SUMMA1], Sum(IIf(IsNull([3_12_0]![Sum-SUMMA]),0,[3_12_0]![Sum-SUMMA])) AS Выражение2

FROM (INN INNER JOIN SF ON INN.INN=SF.INN) LEFT JOIN 3_12_0 ON (SF.DSF=[3_12_0].DSF) AND (SF.NSF=[3_12_0].NSF) AND (SF.INN=[3_12_0].INN)

GROUP BY INN.NAME, INN.INN, SF.NSF, SF.DSF, SF!SUMMA-IIf(IsNull([3_12_0]![Sum-SUMMA]),0,[3_12_0]![Sum-SUMMA])

ORDER BY SF!SUMMA-IIf(IsNull([3_12_0]![Sum-SUMMA]),0,[3_12_0]![Sum-SUMMA]) DESC;

SELECT PP.INN, PP.NSF, PP.DSF, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM PP

WHERE (((PP.SFPP)<#11/1/2004#+14))

GROUP BY PP.INN, PP.NSF, PP.DSF;

SELECT INN.NAME, INN.INN, INN.ADRESS, Sum([SUMMA]-IIf(IsNull([3_13_0]![Sum-SUMMA]),0,[3_13_0]![Sum-SUMMA])) AS Выражение1

FROM (INN LEFT JOIN SF ON INN.INN = SF.INN) LEFT JOIN 3_13_0 ON (SF.DSF = [3_13_0].DSF) AND (SF.NSF = [3_13_0].NSF) AND (SF.INN = [3_13_0].INN)

WHERE (((SF.DSF)<#11/1/2004#))

GROUP BY INN.NAME, INN.INN, INN.ADRESS

HAVING (((Sum([SUMMA]-IIf(IsNull([3_13_0]![Sum-SUMMA]),0,[3_13_0]![Sum-SUMMA])))>0))

ORDER BY Sum([SUMMA]-IIf(IsNull([3_13_0]![Sum-SUMMA]),0,[3_13_0]![Sum-SUMMA]));

SELECT PP.INN, PP.NSF, PP.DSF, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM PP

WHERE (((PP.SFPP)<#11/1/2004#+14))

GROUP BY PP.INN, PP.NSF, PP.DSF;

SELECT SF.INN, Sum([SUMMA]-IIf(IsNull([3_13_0]![Sum-SUMMA]),0,[3_13_0]![Sum-SUMMA])) AS Выражение1

FROM SF LEFT JOIN 3_13_0 ON (SF.DSF=[3_13_0].DSF) AND (SF.NSF=[3_13_0].NSF) AND (SF.INN=[3_13_0].INN)

WHERE (((SF.DSF)<#11/1/2004#))

GROUP BY SF.INN

HAVING (((Sum([SUMMA]-IIf(IsNull([3_13_0]![Sum-SUMMA]),0,[3_13_0]![Sum-SUMMA])))>0));

SELECT INN.INN, INN.NAME, [3_14_0].Выражение1

FROM INN LEFT JOIN 3_14_0 ON INN.INN = [3_14_0].INN

GROUP BY INN.INN, INN.NAME, [3_14_0].Выражение1

ORDER BY [3_14_0].Выражение1 DESC;

SELECT INN.NAME, INN.INN, SF.NSF, SF.DSF, SF.SUMMA, PP.SUMMA

FROM (INN INNER JOIN SF ON INN.INN = SF.INN) INNER JOIN PP ON (SF.INN = PP.INN) AND (SF.NSF = PP.NSF) AND (SF.DSF = PP.DSF)

WHERE (((PP.SFPP)>[SF].[DSF]+14))

GROUP BY INN.NAME, INN.INN, SF.NSF, SF.DSF, SF.SUMMA, PP.SUMMA;

TRANSFORM Sum(PP.SUMMA) AS [Сумма погашения]

SELECT INN.NAME, INN.INN, SF.NSF, SF.SUMMA AS [Сумма СФ]

FROM (INN INNER JOIN SF ON INN.INN=SF.INN) INNER JOIN PP ON (SF.DSF=PP.DSF) AND (SF.NSF=PP.NSF) AND (SF.INN=PP.INN)

WHERE (((PP.SFPP)>PP!DSF+14))

GROUP BY INN.NAME, INN.INN, SF.NSF, SF.SUMMA

PIVOT PP.SFPP;

TRANSFORM Count(INN.NAME) AS [Count-NAME]

SELECT BIK.NAME, BIK.BIK, Count(INN.INN) AS [Count-INN]

FROM BIK LEFT JOIN INN ON BIK.BIK = INN.BIK

GROUP BY BIK.NAME, BIK.BIK

ORDER BY BIK.NAME

PIVOT INN.NAME;

TRANSFORM Sum(PP.SUMMA) AS [Sum-SUMMA1]

SELECT INN.NAME, INN.INN, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM (BIK LEFT JOIN INN ON BIK.BIK = INN.BIK) LEFT JOIN PP ON INN.INN = PP.INN

WHERE (((PP.SFPP) Between #1/1/2004# And #12/31/2004#))

GROUP BY INN.NAME, INN.INN

PIVOT BIK.NAME;

PARAMETERS [first] DateTime, [last] DateTime;

TRANSFORM Count(PP.INN) AS [Count-INN1]

SELECT BIK.NAME, BIK.BIK, Count(PP.INN) AS [Count-INN]

FROM (BIK LEFT JOIN INN ON BIK.BIK=INN.BIK) LEFT JOIN PP ON INN.INN=PP.INN

WHERE (((PP.SFPP) Between [first] And [last]))

GROUP BY BIK.NAME, BIK.BIK

PIVOT Format$([SFPP],"yyyy-mmm");

SELECT PP.INN, PP.SFPP

FROM PP

WHERE (((PP.SFPP) Between #1/1/2003# And #12/31/2004#))

GROUP BY PP.INN, PP.SFPP;

TRANSFORM Count([4_5_0].INN) AS [Count-INN1]

SELECT INN.NAME, INN.INN, Count([4_5_0].INN) AS [Count-INN]

FROM INN LEFT JOIN 4_5_0 ON INN.INN = [4_5_0].INN

GROUP BY INN.NAME, INN.INN

PIVOT Format$([SFPP],"yyyy-q");

SELECT PP.INN, Sum(PP.SUMMA) AS [Sum-SUMMA], PP.DSF, PP.SFPP, PP.NSF

FROM PP

WHERE (((PP.DSF)<#6/1/2004#) AND ((PP.SFPP)<#6/1/2004#))

GROUP BY PP.INN, PP.DSF, PP.SFPP, PP.NSF;

TRANSFORM Sum([4_6_0].[Sum-SUMMA]) AS [Sum-Sum-SUMMA]

SELECT INN.NAME, INN.INN

FROM (INN LEFT JOIN (SF LEFT JOIN 4_6_0 ON (SF.INN = [4_6_0].INN) AND (SF.NSF = [4_6_0].NSF) AND (SF.DSF = [4_6_0].DSF)) ON INN.INN = SF.INN) LEFT JOIN BIK ON INN.BIK = BIK.BIK

WHERE (((SF.DSF)<#6/1/2004#))

GROUP BY INN.NAME, INN.INN

PIVOT BIK.NAME;

SELECT PP.INN, PP.NSF, PP.DSF, Sum(PP.SUMMA) AS [Sum-SUMMA]

FROM PP

GROUP BY PP.INN, PP.NSF, PP.DSF;

TRANSFORM Sum(SF!SUMMA-[4_8_0]![Sum-SUMMA]) AS Выражение1

SELECT INN.NAME, INN.INN, Sum(SF!SUMMA-[4_8_0]![Sum-SUMMA]) AS Выражение2

FROM BIK RIGHT JOIN ((INN LEFT JOIN SF ON INN.INN = SF.INN) LEFT JOIN 4_8_0 ON (SF.INN = [4_8_0].INN) AND (SF.NSF = [4_8_0].NSF) AND (SF.DSF = [4_8_0].DSF)) ON BIK.BIK = INN.BIK

GROUP BY INN.NAME, INN.INN

PIVOT BIK.NAME;

Дата добавления: 2015-09-29