Hi all-

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

I was trying to do this in a single query with nested IIf statements, and got this far:

SELECT Cust_id, Date, Sum(IIf([Amt]>0,[Amt],0)) As Col1, Sum(IIf([Amt]<0,[Amt],0)) As Col2, Sum(IIf([Category]=1,IIf([Amt]>0,[Amt],0),0) As Col3...

and of course I got a data type mismatch. Perhaps I need to split this into two queries.

Well, I don't have anymore time. Here are the first two queries:

SELECT Console.Cust_id, Console.Date, Sum((IIf([Amt]>0,[Amt],0))) AS Col1, Sum((IIf([Amt]<0,[Amt],0))) AS Col2
FROM Console
GROUP BY Console.Cust_id, Console.Date

SELECT Console.Cust_id, Console.Date, Sum((IIf([Amt]>0,[Amt],0))) AS Col3, Sum((IIf([Amt]<0,[Amt],0))) AS Col4, Console.Category
FROM Console
GROUP BY Console.Cust_id, Console.Date, Console.Category
HAVING (((Console.Category)="1"))

At the very worst, you can create the queries you need to generate these values, then create a local table with the necessary columns to hold the values, and then do a delete query to empty the local table, then run an append and two update queries to push you values over and then base your report on that static table.

Do this:

DoCmd.SetWarnings False
DoCmd.OpenQuery - delete
DoCmd.OpenQuery - append
etc
DoCmd.SetWarnings True

DoCmd.OpenReport ...

Did that help any?





mmcdonal

Hi,
is it possible to design the report with unbound text boxes and then do all the calculations there ?

Yes, you can use the conditionals If Then or IIF() in the Detail section's On Format event. Do you need help with that?

mmcdonal

Hi,

I'm trying to get it work with the queries. If this does not work then i'll try coding it on the report.

I have the 3rd query (for the count).

SELECT Console.Cust_id, Console.Date, count(IIf([Console.Amt]>0,0)) as col5, count(IIf([Console.Amt]<0,0)) as col6
FROM Console
group by Console.Cust_id, Console.Date,Console.Category
having (((Consolide.category)="1"))

Now, how do I join the 3 queries ?

Thanks for the help.

SELECT Query3.Cust_id, Query3.Date, Query3.Col1, Query3.Col2, Query4.Col3, Query4.Col4, Query5.col5, Query5.col6
FROM (Query3 INNER JOIN Query4 ON (Query3.Date = Query4.Date) AND (Query3.Cust_id = Query4.Cust_id)) INNER JOIN Query5 ON (Query3.Date = Query5.Date) AND (Query3.Cust_id = Query5.Cust_id);

That should do it.

My queries start with Query3 as the first query, Query4 as the second, and Query5 as the third.

To do the joins, I dragged the Cust_id and date fields from query3 to query4, and from query3 to query5.

Did that do it? Here are my results:

Cust_id Date Col1 Col2 Col3 Col4 col5 col6
1111 10/10/2007 30 -66 30 -55 2 2
2222 10/8/2007 0 -55 0 -55 0 1



mmcdonal

Hi,

Thanks for the help but how can inner join work here ?

In the table try changing the category of cust_id '2222' to '2'.
Then the output should have be -

Cust_id Date Col1 Col2 Col3 Col4 col5 col6
1111 10/10/2007 30 -66 30 -55 2 2
2222 10/8/2007 0 -55 0 0 0 0

but because of the inner join we don't see '2222' in the output.
Do we not want something like an outer join (which access does not have).

Hi,

So a left join gives the right result here.

In the count query, i don't want to display a '0' when the count is 0.
How to do that ?

Thanks

Do that in your report.

In the detail section on format event, add this code:

If Me.Col1 = 0 Then
   Me.Col1.Visible = False
Else
   Me.Col1.Visible = True
End If
If Me.Col2 = 0 Then
   Me.Col2.Visible = False
Else
   Me.Col2.Visible = True
End If
...


Do that for all the controls you want to be invisible when the value is 0.


mmcdonal

Ok.

Thanks for all the help.