Wrox Programmer Forums
|
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 June 9th, 2006, 11:19 AM
Authorized User
 
Join Date: May 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query Help

I wondered if someone could help.

I am running a query from a table which holds data in format:

Year Period Value
---- ------ -----
2006 0 15000
2006 1 2000
2006 2 300
2006 3 150
2006 4 250
2006 5 12000

I am trying to seperate Period 2006 0 from the remaining Periods so that I am running a cumulative total for Period 1-5 and another for period 0

Can anyone help?

Thanks

Paul



 
Old June 9th, 2006, 04:40 PM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Total for Period 0:
SELECT SUM([Value])
FROM tbl
WHERE Period = 0;

Total for all others:
SELECT SUM([Value])
FROM tbl
WHERE Period <> 0;

 
Old June 12th, 2006, 02:34 AM
Authorized User
 
Join Date: May 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have tried to input the above criteria into my select query, however the run does run but no results are shown.

Can anyone help?

 
Old June 12th, 2006, 04:19 AM
Authorized User
 
Join Date: May 2006
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

Value or Values is the reserve word in Jet. This might be one of the reasom. Try using other field name something like TxtValue.

Ashfaque

 
Old June 12th, 2006, 05:23 AM
Authorized User
 
Join Date: May 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Is there an easier way of showing results? Still having a problem running this query:

i.e in one column of my query

Period0: iif[period]=0,[value]

and

PeriodOthers: iif[period]=1-4, [value]

Have tried this format but reason an error: too complex to run report


 
Old June 12th, 2006, 05:59 AM
Authorized User
 
Join Date: May 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have run two columns:

Period20060: IIf([period]<>0,[valuehome])
Period2006: IIf([period]=0,[valuehome])

and receive restults for period=0 but not for <>0.

Can anyone help?

 
Old June 12th, 2006, 07:21 AM
Authorized User
 
Join Date: May 2006
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

Don't you think false part in your IIF statement is messing?

I have an example for you that I used in my query which gives me desired result. Here it is.

IIf([SumOfPAmount]-([RunningAmount]-[Amount])>0,[SumOfPAmount]-([RunningAmount]-[Amount]),0)) AS Adjusted,

This would give you an idea I hope.

Ashfaque
 
Old June 12th, 2006, 08:14 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just wrote up that table in a temp database and the queries I gave you work fine using both the default field names (Field1, Field2 ...) and Year/Period/Value. Double check that you wrote them correctly using the SQL view of the query design, and that the name of the table is correct (it's probably not 'tbl').






Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
how to make a query from an existing query raport SQL Language 3 November 13th, 2006 08:59 PM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





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