Re: Need help with a query

Sorry forgot to add,

Item 1 No
Item 1 No
Item 1 Yes
Item 1 No

Item 1 No
Item 1 Yes
Item 1 No
Item 1 No

should return 1 for the first and 2 for the second which means a YES
restarts the count.

Al....




"Douglas J Steele" wrote:

You can use the IIf statement in queries: that's an "Immediate If", and has
the form:

IIf(expr, truepart, falsepart)

If expr is true, the function returns truepart. If it's false, it returns
falsepart.

Your example is incorrect, though. You've got 3 Item 2s, not 2.

What exactly do you want: to ignore the Yes values, or to restart the count?

In other words, should the following both return 3, or should the first
return 1 and the second 2?

Item 1 No
Item 1 No
Item 1 Yes
Item 1 No

Item 1 No
Item 1 Yes
Item 1 No
Item 1 No

If both should return 3, it's relatively simple. Use the IIf function to
return 1 for Nos, and 0 for Yeses, and sum, rather than count. Assuming your
field names are ItemName and ItemValue (and that ItemValue is a boolean
field), the SQL would be:

SELECT ItemName, Sum(IIf(ItemValue, 0, 1)) AS ItemValueCount
FROM MyTable
GROUP BY ItemName.

For the second option (resetting the count at each Yes), you haven't given
enough information. You can't assume anything about the order of records in
tables, so you need some way of being able to ensure that the records are
returned in the order you want. Even with that, though, it's going to be
difficult to do in SQL.



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Sproul" <Sproul@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2F11CDE2-AA9F-4505-B2B7-B27EE1B0F0D8@xxxxxxxxxxxxxxxx
thanks for the reply, but i don't know how to write the criteria to set
the
count to ZERO if a yes appears in the column.

come to think of it i don't think that you can use an if statement in a
Query in Access 2003.

Item 1 No
Item 2 No
Item 1 No
Item 3 No
Item 2 Yes
Item 2 No
Item 4 Yes
Item 1 No
Item 3 No

A count statement for above would read

Item 1 3
Item 2 2
Item 3 2
Item 4 1

I would like to reset the count to 0 (ZERO) when a yes is found, which
would
return

Item 1 3
Item 2 1
Item 3 2
Item 4 0

Is this possible for a query.
P.S. SQL would be easier.


Al....




"Deborah Jean" wrote:

In the Query design window type both criteria on the AND row. In other
word
on the same line even if you have to include the field twice.

"Sproul" wrote:

I've been using Access 2003 for about a year now but would still
consider
myself a new user.
I'm having a problem trying to create a query, if indeed the query is
possible to create.

I want to be able to create a count for individual item i.e. there
could be
15 instances if one item 10 of another and so on, however i only want
it to
count the instance if a certain field = NO, (here comes the hard part)
if the
filed with no is YES i want it to reset the count for that item to
ZERO.

the database has and incremantal ID field for each record and also a
Timestamp field which i've renamed TSinfo for obvious reasons.


Can anyone give me a few pointers?


Al....



.

Relevant Pages

  • Re: Need help with a query
    ... The second Query is returning all entries on the itemname as the last yes ... SELECT ItemName, MaxAs MostRecent ... WHERE ItemValue = True ... What i'm trying to do is, if i have a piece of equipment which i have to ...
    (microsoft.public.access.gettingstarted)
  • Re: Need help with a query
    ... SELECT ItemName, MaxAs MostRecent ... WHERE ItemValue = True ... Join that query to your actual table using a Left or Right Join (it depends ...
    (microsoft.public.access.gettingstarted)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)