Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 10th, 2007, 09:30 AM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default access query -plz help

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


 
Old October 10th, 2007, 11:02 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 10th, 2007, 12:37 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



 
Old October 10th, 2007, 01:09 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 10th, 2007, 01:28 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


 
Old October 10th, 2007, 01:33 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 10th, 2007, 01:55 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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).


 
Old October 10th, 2007, 02:10 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old October 10th, 2007, 02:14 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 10th, 2007, 02:16 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok.

Thanks for all the help.






Similar Threads
Thread Thread Starter Forum Replies Last Post
PLZ SEND ME THE SIMPLE AND HARD JOIN QUERY RCMDINESH SQL Language 2 September 25th, 2007 03:24 PM
plz reply 2m query,its very urgent.... preetham.sarojavenkatesh ASP.NET 1.x and 2.0 Application Design 2 August 1st, 2007 08:26 AM
complex query... help plz hello MySQL 1 April 28th, 2007 05:48 AM
Ms access database in asp-urgent plz netfresher Classic ASP Basics 1 June 30th, 2006 03:35 PM
Complex Query-Plz Help rivinjose SQL Server 2000 10 June 27th, 2003 09:25 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.