Need help with a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sproul - 10 Mar 2006 08:31 GMT

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....

Reply to this Message

Deborah Jean - 10 Mar 2006 11:58 GMT

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.

> I've been using Access 2003 for about a year now but would still consider
> myself a new user.
[quoted text clipped - 12 lines]
>
> Al....

Reply to this Message

Sproul - 13 Mar 2006 12:46 GMT

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....

> 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.
[quoted text clipped - 15 lines]
> >
> > Al....

Reply to this Message

Douglas J Steele - 13 Mar 2006 14:33 GMT

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.

Signature

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

> 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.
[quoted text clipped - 51 lines]
> > >
> > > Al....

Reply to this Message

Sproul - 13 Mar 2006 16:26 GMT

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....

> You can use the IIf statement in queries: that's an "Immediate If", and has
> the form:
[quoted text clipped - 99 lines]
> > > >
> > > > Al....

Reply to this Message

Douglas J Steele - 13 Mar 2006 18:02 GMT

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

Signature

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

> thanks for the reply Douglas,
>
[quoted text clipped - 128 lines]
> > > > >
> > > > > Al....

Reply to this Message

Sproul - 14 Mar 2006 17:26 GMT

Thanks for the reply, not sure if i fully understand it all, Anyway the first
query works fine

The second Query is returning all entries on the itemname as the last yes
entry.
i.e.
item 1 NO 12/07/2005
item 1 YES 15/08/2005
item 1 NO 19/08/2005
item 1 NO 25/11/2005

is being returned as
item 1 15/08/2005
item 1 15/08/2005
item 1 15/08/2005
item 1 15/08/2005

which i don't think is quite right.

I'll have another look at it tomorrow when i have a bit more time (fingers
crossed)
Just really a courtesy reply to let you know that i appreciate your help.

Al....

> You should be able to create a subquery that returns the TimeStamp for the
> last Yes response for each Item:
[quoted text clipped - 184 lines]
> > > > > >
> > > > > > Al....

Reply to this Message

Douglas J Steele - 14 Mar 2006 17:52 GMT

Oops. Sorry about that. You only want 1 row per Item, so use the DISTINCT
keyword:

SELECT DISTINCT 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

(I did warn you it was air-code!)

Signature

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

> Thanks for the reply, not sure if i fully understand it all, Anyway the first
> query works fine
[quoted text clipped - 209 lines]
> > > > > > >
> > > > > > > Al....

Reply to this Message

Sproul - 15 Mar 2006 17:09 GMT

It works fine, i think, both

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

and

SELECT DISTINCT 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

give the exact same results. which would be the record where the Last "Yes"
appears for that ItemName

Also the last Query just gives errors.

I have asked another friend of mine who writes SQL query's to look at it, he
does have a working version unfortunately not one that would work in Access,
something to do with a cursor.

Thanks

Al....

> Oops. Sorry about that. You only want 1 row per Item, so use the DISTINCT
> keyword:
[quoted text clipped - 248 lines]
> > > > > > > > the database has and incremantal ID field for each record and
> also

Reply to this Message

Sproul - 13 Mar 2006 16:49 GMT

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....

> You can use the IIf statement in queries: that's an "Immediate If", and has
> the form:
[quoted text clipped - 99 lines]
> > > >
> > > > Al....

Reply to this Message