Selasa, 05 April 2016

[MS_AccessPros] query: how do I this, please help

 

I have a file [a/r trans]. I  counting RT (round trip) and OW (one way), based on a start-end date range.

My query works fine, however there is an 'amount' column in [a/r trans], if it is negative then I need to deduct from RT and OW respectively.

This is my query:


SELECT Month([end date]) AS emonth, [a/r trans]!private AS private1, Format([end date],"mmmm yyyy") AS [month], nz([a/r trans].insrcd,"") AS insrcd, [a/r trans].cp AS transp, Sum([a/r trans].[days attended]) AS days, [a/r trans].type, IIf(([a/r trans].cp>10 And [a/r trans].type="I"),Sum([a/r trans]![end date]-[a/r trans]![start date]+1)) AS rt, IIf(([a/r trans].cp>0 And [a/r trans].cp<11 And [a/r trans].type="I"),Sum(([a/r trans]![end date]-[a/r trans]![start date]+1))) AS ow

FROM [a/r trans]

WHERE ((([a/r trans].[start date]) Between [Forms]![ar yearly]![pid from] And [Forms]![ar yearly]![pid to]) AND (([a/r trans].cp)>0) AND (([a/r trans].type)="I") AND (([a/r trans].amount)<>0))

GROUP BY Month([end date]), [a/r trans]!private, Format([end date],"mmmm yyyy"), [a/r trans].cp, [a/r trans].type, [a/r trans].insrcd

ORDER BY Month([end date]), [a/r trans].insrcd, [a/r trans].cp, [a/r trans].type;


Thanks for all your help in advance

Sarah


__._,_.___

Posted by: sarahk@schemesoftware.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? The Yahoo Mail app is fast, beautiful and intuitive. Try it today!


.

__,_._,___

Tidak ada komentar:

Poskan Komentar