[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!

---

Visit Your Group

• New Members 5
• New Links 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___