By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,954 Members | 1,592 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,954 IT Pros & Developers. It's quick & easy.

query based on 4 queries - plz help

P: 1
Hi all,

this is my first post here although i've always referred this forum for help.

I am trying to design a query based on 4 queries but i'm unable to get the result i want. Please help me-

I have this data in a table console –

Cust_id Date Amt Category
1111 10/10/07 10 1
1111 10/10/07 20 1
1111 10/10/07 -11 2
1111 10/10/07 -22 1
1111 10/10/07 -33 1
2222 10/08/07 -55 1

I need to build a summary report with the foll column-

cust_id; date; sum of + values of amt as (Col1); sum of –values of amt as (Col2); sum of +amt where category=1 as (Col3); sum of -amt where category=1 as (Col4); number of records with +amt and category=1 as (Col5); number of records with -amt and category=1 (Col6)

so the output would be-
cust_id date col1 col2 col3 col4 col5 col6
1111 10/10/07 30 -66 30 -55 2 2
2222 10/08/07 .... -55 .... -55 ... 1

I started by building 4 queries to get col1, col2, col3, col4, col5 and col6 and then tried to build a query based on those 4 queries….. but I’m not able to get the result I want.

Can anybody please help me… I can post my queries if anybody wants.
Thanks
Oct 10 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
This can be done using IIF() statements like:
Expand|Select|Wrap|Line Numbers
  1. select cust_id,  date,  Sum(IIF([Amt]>0;[amt];0) as SumPos,  Sum(IIF([Amt]<0;[amt];0) as SumMin, Sum(IIF([Amt]>0 and [Category]=1;[amt];0) as SumPosCat1,Sum(IIF([Amt]<0 and [Category]=1;[amt];0) as SumMinCat1,Sum(IIF([Amt]>0 and [Category]=1;1;0) as CountPosCat1,Sum(IIF([Amt]<0 and [Category]=1;1;0) as CountMinCat1 from Console;
  2.  
BTW, the ";" in the IIF might be needed to change into a comma "," depending on the Access version and regionalsettings of your computer.

Getting the idea ?

Nic;o)
Oct 11 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.