Re: Need help with a query

You should be able to create a subquery that returns the TimeStamp for the
last Yes response for each Item:

SELECT ItemName, Max(TSInfo) As MostRecent
FROM MyTable
WHERE ItemValue = True
GROUP BY ItemName

Join that query to your actual table using a Left or Right Join (it depends
on the order in which you specify the tables which you use...) so that you
have a list of each ItemName and either 0 (if there's never been a Yes) or a
value of TSInfo (representing the most recent TimeStamp) (NOTE: This is
untested air-code, so may need some tweaking...)

SELECT A.ItemName, Nz(B.MostRecent, 0) AS LastTSInfo
FROM MyTable AS A
RIGHT JOIN
(SELECT ItemName, Max(TSInfo) As MostRecent
FROM MyTable
WHERE ItemValue = True
GROUP BY ItemName) AS B
ON A.ItemName = B.ItemName

You should now be able to join that to your table, looking for the count of
all items >= LastTSInfo:

SELECT C.ItemName, Count(*) AS ItemCount
FROM MyTable AS C
INNER JOIN
(SELECT A.ItemName, Nz(B.MostRecent, 0) AS LastTSInfo
FROM MyTable AS A
RIGHT JOIN
(SELECT ItemName, Max(TSInfo) As MostRecent
FROM MyTable
WHERE ItemValue = True
GROUP BY ItemName) AS B
ON A.ItemName = B.ItemName) AS D
ON C.ItemName = D.ItemName
WHERE C.TSInfo > D.LastTSInfo


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


"Sproul" <Sproul@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DA007BD5-1950-4FBF-9D58-2598E73AA375@xxxxxxxxxxxxxxxx
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: 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
    ... field names are ItemName and ItemValue (and that ItemValue is a boolean ... SELECT ItemName, Sum) AS ItemValueCount ... difficult to do in SQL. ... Is this possible for a query. ...
    (microsoft.public.access.gettingstarted)
  • Re: Need help with a query
    ... SELECT ItemName, MaxAs MostRecent ... WHERE ItemValue = True ... SELECT DISTINCT A.ItemName, NzAS LastTSInfo ...
    (microsoft.public.access.gettingstarted)
  • Re: Need help with a query
    ... SELECT DISTINCT A.ItemName, NzAS LastTSInfo ... WHERE ItemValue = True ... GROUP BY ItemName) AS B ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.gettingstarted)
  • RE: help!... urgent and im new on this area
    ... An update query, as Klatuu is suggesting, will not help. ... INSERT INTO ItemHistory (ItemNo, ItemName, DateReceived, IncomingStocks) ...
    (microsoft.public.access.gettingstarted)
Loading