Re: Need help with a query

thanks for the reply Douglas,

My database has a few fields:
the first is the ID (key) field increments by one for each new field
the main information fields are all YES, NO or text information,
the last field is a TimeStamp which i've named TSinfo

What i'm trying to do is, if i have a piece of equipment which i have to
test i submit the information to the database each time i test it, if i
repair the equipment or it tests ok then this would result in a NO, if i
replace it the result would be a YES, the replacement equipment would assume
the ITEM ID of the previous equipment. I do require all information from
previous equipment, however for this particular query i only need to know how
many times it has been tested since i last replaced it.

Item 1 No <---Item 1 = 1
Item 2 No <---Item 2 = 1
Item 1 No <---Item 1 = 2
Item 3 No <---Item 3 = 1
Item 2 Yes <---Item 2 = 0
Item 2 No <---Item 2 = 1 <<< should be result
Item 4 Yes <---Item 4 = 0 <<< should be result
Item 1 No <---Item 1 = 3 <<< should be result
Item 3 No <---Item 3 = 2 <<< should be result


Again please forgive me if i have not explained it properly.



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: Setting the data type of a new filed in a make table query
    ... I have created a small database that has ... wanted to populate the form with all of the equipment that needed an hour ... reading, display the LAST reading and add ... possible with a Select query, or if I can provide you with some additional ...
    (microsoft.public.access.queries)
  • RE: Query wont report
    ... How many records does the query return? ... I also have equipment table and department table linked from ... department table already existed from it's database. ... I built queries to bring in the department and calculate the minutes per ...
    (microsoft.public.access.gettingstarted)
  • RE: Query wont report
    ... The query takes a while to run and then populate with records. ... I also have equipment table and department table linked from ... department table already existed from it's database. ... I built queries to bring in the department and calculate the minutes per ...
    (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: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
Loading