Re: Need help with a query

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

  • ignoring the falsepart in IIF Function
    ... can someone please help me with this IIF Function in a Query. ... to write an IIF statement that only performs an action if the truepart ...
    (microsoft.public.access.queries)
  • Re: Can an IIf Statement return more than one value
    ... I am new to VBA but would like to continue to learn more. ... best way to open the query on VBA I am guessng an "On Click" Or 'On Focus" ... appears as the RowSource for your combo box (with the RowSourceType set to ... saying that you want the IIf statement to select multiple rows from the ...
    (microsoft.public.access.formscoding)
  • Re: Find - replace automatic
    ... So there was a quote just before the first IIF statement? ... Microsoft Access MVP ... and ran an Update query against that field. ... you can handle the ApprovalStatus updates ...
    (microsoft.public.access.gettingstarted)
  • Re: Help! Trouble converting values with IIF statement
    ... I ended up trying a different variation within the IIF statement based on ... I'm assuming that "TimingCalc" is a table or query name. ... Time to Arrival (Date diff function) ... I would like to use the following calculation that references Query 1 and ...
    (microsoft.public.access.queries)
  • Re: IIf problem with <>
    ... Where is this IIF statement? ... Is it in the query that is the forms ... a simple filter wont do as there is two other bits of criteria acting apon ... To me this should show all the records if the showComplete tick box is true ...
    (microsoft.public.access.queries)