development of query

S

sverre

Hi,

I would like to add a few conditions for below query but I dont know how I
write it. Could someone help me:

Rows that have the below combination should not be shown:
Account=1462 and Asset_Liability = L,
Account=1462 and Asset_Liability = L
Account=1462 and Asset_Liability = A
Account=2462 and Asset_Liability = A

The sql looks like this:

SELECT [bokföringen mxg].ACCOUNT, [bokföringen mxg].MX_TRADE_NUMBER,
Sum([bokföringen mxg].AMOUNT) AS SummaförAMOUNT, Sesam.ASSET_LIABILITY,
Sesam.ACQ_ACCRUED_INT, [bokföringen mxg].COMPANY_NUMBER, Sesam.TRN_GROUP,
Sesam.ACQ_AV_NOMINAL, Sesam.ACQ_AV_PREMIUM, Sesam.ACQ_AMORT_PREMIUM

FROM Sesam INNER JOIN [bokföringen mxg] ON Sesam.TRN_NUMBER = [bokföringen
mxg].MX_TRADE_NUMBER

WHERE (((Sesam.LEGAL_ENTITY) Not Like "JK" Or (Sesam.LEGAL_ENTITY) Is Null)
AND ((1)=IIf([ACCOUNT] In (1401,2602,2603,1402,2601,2606) And
[TRN_UNSTARTED]='N',2,1)) AND (([bokföringen mxg].ACCOUNT) Not In
(1040,1042,2440,3000) And ([bokföringen mxg].ACCOUNT)<3000)) OR
(((Sesam.LEGAL_ENTITY) Not Like "JK" Or (Sesam.LEGAL_ENTITY) Is Null) AND
((1)=IIf([ACCOUNT] In (1401,2602,2603,1402,2601,2606) And
[TRN_UNSTARTED]='N',2,1)) AND (([bokföringen mxg].ACCOUNT) Not In
(1040,1042,2440,3000) And ([bokföringen mxg].ACCOUNT)<3000))

GROUP BY [bokföringen mxg].ACCOUNT, [bokföringen mxg].MX_TRADE_NUMBER,
Sesam.ASSET_LIABILITY, Sesam.ACQ_ACCRUED_INT, [bokföringen
mxg].COMPANY_NUMBER, Sesam.TRN_GROUP, Sesam.ACQ_AV_NOMINAL,
Sesam.ACQ_AV_PREMIUM, Sesam.ACQ_AMORT_PREMIUM

HAVING (((Sum([bokföringen mxg].AMOUNT)) Between -10 And 10) AND
(([bokföringen mxg].COMPANY_NUMBER)="8803")) OR (((Sum([bokföringen
mxg].AMOUNT)) Between -10 And 10) AND (([bokföringen
mxg].COMPANY_NUMBER)="8803"));

Best regards

Sverker
 
J

John Spencer

I would try

SELECT [bokföringen mxg].ACCOUNT, [bokföringen mxg].MX_TRADE_NUMBER,
Sum([bokföringen mxg].AMOUNT) AS SummaförAMOUNT, Sesam.ASSET_LIABILITY,
Sesam.ACQ_ACCRUED_INT, [bokföringen mxg].COMPANY_NUMBER, Sesam.TRN_GROUP,
Sesam.ACQ_AV_NOMINAL, Sesam.ACQ_AV_PREMIUM, Sesam.ACQ_AMORT_PREMIUM

FROM Sesam INNER JOIN [bokföringen mxg] ON Sesam.TRN_NUMBER = [bokföringen
mxg].MX_TRADE_NUMBER


WHERE NOT (Account=1462 And Asset_Liability In("A","L","L,")
OR Account=2462 and Asset_Liability = "A")
AND


(((Sesam.LEGAL_ENTITY) Not Like "JK" Or (Sesam.LEGAL_ENTITY) Is Null)
AND ((1)=IIf([ACCOUNT] In (1401,2602,2603,1402,2601,2606) And
[TRN_UNSTARTED]='N',2,1)) AND (([bokföringen mxg].ACCOUNT) Not In
(1040,1042,2440,3000) And ([bokföringen mxg].ACCOUNT)<3000)) OR
(((Sesam.LEGAL_ENTITY) Not Like "JK" Or (Sesam.LEGAL_ENTITY) Is Null) AND
((1)=IIf([ACCOUNT] In (1401,2602,2603,1402,2601,2606) And
[TRN_UNSTARTED]='N',2,1)) AND (([bokföringen mxg].ACCOUNT) Not In
(1040,1042,2440,3000) And ([bokföringen mxg].ACCOUNT)<3000))

GROUP BY [bokföringen mxg].ACCOUNT, [bokföringen mxg].MX_TRADE_NUMBER,
Sesam.ASSET_LIABILITY, Sesam.ACQ_ACCRUED_INT, [bokföringen
mxg].COMPANY_NUMBER, Sesam.TRN_GROUP, Sesam.ACQ_AV_NOMINAL,
Sesam.ACQ_AV_PREMIUM, Sesam.ACQ_AMORT_PREMIUM

HAVING (((Sum([bokföringen mxg].AMOUNT)) Between -10 And 10) AND
(([bokföringen mxg].COMPANY_NUMBER)="8803")) OR (((Sum([bokföringen
mxg].AMOUNT)) Between -10 And 10) AND (([bokföringen
mxg].COMPANY_NUMBER)="8803"));

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,

I would like to add a few conditions for below query but I dont know how I
write it. Could someone help me:

Rows that have the below combination should not be shown:
Account=1462 and Asset_Liability = L,
Account=1462 and Asset_Liability = L
Account=1462 and Asset_Liability = A
Account=2462 and Asset_Liability = A

The sql looks like this:

SELECT [bokföringen mxg].ACCOUNT, [bokföringen mxg].MX_TRADE_NUMBER,
Sum([bokföringen mxg].AMOUNT) AS SummaförAMOUNT, Sesam.ASSET_LIABILITY,
Sesam.ACQ_ACCRUED_INT, [bokföringen mxg].COMPANY_NUMBER, Sesam.TRN_GROUP,
Sesam.ACQ_AV_NOMINAL, Sesam.ACQ_AV_PREMIUM, Sesam.ACQ_AMORT_PREMIUM

FROM Sesam INNER JOIN [bokföringen mxg] ON Sesam.TRN_NUMBER = [bokföringen
mxg].MX_TRADE_NUMBER

WHERE (((Sesam.LEGAL_ENTITY) Not Like "JK" Or (Sesam.LEGAL_ENTITY) Is Null)
AND ((1)=IIf([ACCOUNT] In (1401,2602,2603,1402,2601,2606) And
[TRN_UNSTARTED]='N',2,1)) AND (([bokföringen mxg].ACCOUNT) Not In
(1040,1042,2440,3000) And ([bokföringen mxg].ACCOUNT)<3000)) OR
(((Sesam.LEGAL_ENTITY) Not Like "JK" Or (Sesam.LEGAL_ENTITY) Is Null) AND
((1)=IIf([ACCOUNT] In (1401,2602,2603,1402,2601,2606) And
[TRN_UNSTARTED]='N',2,1)) AND (([bokföringen mxg].ACCOUNT) Not In
(1040,1042,2440,3000) And ([bokföringen mxg].ACCOUNT)<3000))

GROUP BY [bokföringen mxg].ACCOUNT, [bokföringen mxg].MX_TRADE_NUMBER,
Sesam.ASSET_LIABILITY, Sesam.ACQ_ACCRUED_INT, [bokföringen
mxg].COMPANY_NUMBER, Sesam.TRN_GROUP, Sesam.ACQ_AV_NOMINAL,
Sesam.ACQ_AV_PREMIUM, Sesam.ACQ_AMORT_PREMIUM

HAVING (((Sum([bokföringen mxg].AMOUNT)) Between -10 And 10) AND
(([bokföringen mxg].COMPANY_NUMBER)="8803")) OR (((Sum([bokföringen
mxg].AMOUNT)) Between -10 And 10) AND (([bokföringen
mxg].COMPANY_NUMBER)="8803"));

Best regards

Sverker
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query issue 3
Syntax Problem 2
Subtotal and Grand Total in Query 2
Return repeats info in "8s" 14
Help on Crosstab query 4
UNION Query with Criteria 1
need help with query - please. 2
Select Query By Month. 6

Top