Countif Query - Finding Unique Entries

[jameswilkinsonfjs@googlemail.com]

Hi All,

Ok I have a table [TblItems]- it lists items with a unique reference
code; lets say there are 4 items [RefCode]:

Item RefCode
1 ABC1
2 ABC2
3 ABC3
4 ABC4

I have a query [QryUnavail] that does some calculations on other
tables and tells me which items are currently unavialble - this puts
the RefCode of unavailable items into a column [UnavailableItem s]; for
example if ABC2 was unavailable

<code <code UnavailableItem s
...... .... ABC2

What I need is another query which list all items which are currently
available, so ABC1, ABC3 and ABC4 in this case.

I thought that if I have a query that lists all [RefCodes], except
where the countif of that [RefCode] in the column [UnavailableItem s]
is greater than 1, I could achive this but I can't work out how to
achieve this.

I'm an access newbie but in using what I know coupled with Excel type
formula structure this whould be something like:

Countif([QryUnavail]![UnavailableItem s],[RefCode])

I could then use the criteria ="0" to show only available items

Any help appreciated,

Thanks, JW191

[CDMAPoster@fortunejames.com]

Re: Countif Query - Finding Unique Entries

On May 19, 11:20 am, jameswilkinson. ..@googlemail.c om wrote:

Hi All,
>
Ok I have a table [TblItems]- it lists items with a unique reference
code; lets say there are 4 items [RefCode]:
>
Item           RefCode
1                ABC1
2                ABC2
3                ABC3
4                ABC4
>
I have a query [QryUnavail] that does some calculations on other
tables and tells me which items are currently unavialble - this puts
the RefCode of unavailable items into a column [UnavailableItem s]; for
example if ABC2 was unavailable
>
<code <code      UnavailableItem s
.....        ....              ABC2
>
What I need is another query which list all items which are currently
available, so ABC1, ABC3 and ABC4 in this case.
>
I thought that if I have a query that lists all [RefCodes], except
where the countif of that [RefCode] in the column [UnavailableItem s]
is greater than 1, I could achive this but I can't work out how to
achieve this.
>
I'm an access newbie but in using what I know coupled with Excel type
formula structure this whould be something like:
>
Countif([QryUnavail]![UnavailableItem s],[RefCode])
>
I could then use the criteria ="0" to show only available items
>
Any help appreciated,
>
Thanks, JW191

If an Unmatched Query won't work, take a look here:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/c67861ecfba44820

If that doesn't help, post back.

James A. Fortune
CDMAPoster@Fort uneJames.com

[jameswilkinsonfjs@googlemail.com]

Re: Countif Query - Finding Unique Entries

On May 20, 4:28 pm, CDMAPos...@fort unejames.com wrote:

On May 19, 11:20 am, jameswilkinson. ..@googlemail.c om wrote:
>
>
>
>
>

Hi All,

>

Ok I have a table [TblItems]- it lists items with a unique reference
code; lets say there are 4 items [RefCode]:

>

Item           RefCode
1                ABC1
2                ABC2
3                ABC3
4                ABC4

>

I have a query [QryUnavail] that does some calculations on other
tables and tells me which items are currently unavialble - this puts
the RefCode of unavailable items into a column [UnavailableItem s]; for
example if ABC2 was unavailable

>

<code <code      UnavailableItem s
.....        ....              ABC2

>

What I need is another query which list all items which are currently
available, so ABC1, ABC3 and ABC4 in this case.

>

I thought that if I have a query that lists all [RefCodes], except
where the countif of that [RefCode] in the column [UnavailableItem s]
is greater than 1, I could achive this but I can't work out how to
achieve this.

>

I'm an access newbie but in using what I know coupled with Excel type
formula structure this whould be something like:

>

Countif([QryUnavail]![UnavailableItem s],[RefCode])

>

I could then use the criteria ="0" to show only available items

>

Any help appreciated,

>

Thanks, JW191

>
If an Unmatched Query won't work, take a look here:
>
http://groups.google.com/group/comp..../browse_frm/th...
>
If that doesn't help, post back.
>
James A. Fortune
CDMAPos...@Fort uneJames.com- Hide quoted text -
>
- Show quoted text -

Unfortunately Im still stuck here. Is there an easy way of showing all
refcodes in one column, and the count of thos RefCodes from a column
in another query. The I can simply filter where the count is zero? So
for above example unfiltered:

RefCode Count UnavailableItem s
ABC1 0
ABC2 1
ABC3 0
ABC4 0

And Filtered (i.e. showing available):

RefCode Count UnavailableItem s
ABC1 0
ABC3 0
ABC4 0

Thanks,
JW

[CenturionX]

Re: Countif Query - Finding Unique Entries

Hello,

There is a useful sentence that you can use in your queries: ‘HAVING’
The way to use it is:

SELECT Field1, Field2
FROM Table1
GROUP BY Field1
HAVING Field1 0

Try that, maybe you will resolve your problem.

[Salad]

Re: Countif Query - Finding Unique Entries

jameswilkinsonf js@googlemail.c om wrote:

On May 20, 4:28 pm, CDMAPos...@fort unejames.com wrote:
>

>>On May 19, 11:20 am, jameswilkinson. ..@googlemail.c om wrote:
>>
>>
>>
>>
>>
>>

>>>Hi All,

>>

>>>Ok I have a table [TblItems]- it lists items with a unique reference
>>>code; lets say there are 4 items [RefCode]:

>>

>>>Item RefCode
>>>1 ABC1
>>>2 ABC2
>>>3 ABC3
>>>4 ABC4

>>

>>>I have a query [QryUnavail] that does some calculations on other
>>>tables and tells me which items are currently unavialble - this puts
>>>the RefCode of unavailable items into a column [UnavailableItem s]; for
>>>example if ABC2 was unavailable

>>

>>><code <code UnavailableItem s
>>>..... .... ABC2

>>

>>>What I need is another query which list all items which are currently
>>>available, so ABC1, ABC3 and ABC4 in this case.

>>

>>>I thought that if I have a query that lists all [RefCodes], except
>>>where the countif of that [RefCode] in the column [UnavailableItem s]
>>>is greater than 1, I could achive this but I can't work out how to
>>>achieve this.

>>

>>>I'm an access newbie but in using what I know coupled with Excel type
>>>formula structure this whould be something like:

>>

>>>Countif([QryUnavail]![UnavailableItem s],[RefCode])

>>

>>>I could then use the criteria ="0" to show only available items

>>

>>>Any help appreciated,

>>

>>>Thanks, JW191

>>
>>If an Unmatched Query won't work, take a look here:
>>
>>http://groups.google.com/group/comp..../browse_frm/th...
>>
>>If that doesn't help, post back.
>>
>>James A. Fortune
>>CDMAPos...@Fo rtuneJames.com- Hide quoted text -
>>
>>- Show quoted text -

>
>
Unfortunately Im still stuck here. Is there an easy way of showing all
refcodes in one column, and the count of thos RefCodes from a column
in another query. The I can simply filter where the count is zero? So
for above example unfiltered:
>
RefCode Count UnavailableItem s
ABC1 0
ABC2 1
ABC3 0
ABC4 0
>
And Filtered (i.e. showing available):
>
RefCode Count UnavailableItem s
ABC1 0
ABC3 0
ABC4 0
>
Thanks,
JW

Let's say you have a table of RefCodes and a query of RefCodes with
Counts. Open up the querybuilder (Query/Design/New) and add both
tables. Create a relationship between the two by dragging Refcode from
Refcodes table to the query's Refcode. Now DlbClick on the line and set
it to All in Refcodes and matching in Query. Then drag the Refcode from
Refcodes into 1 column and the count from the query. You'll get a Null
from those without a count. You can create a calculated column to
display a 0 if needed. Something like
QCnt : IIF(Not IsNull(Query1!R efcnt,Query1!Re fcnt,0)

Monkey Dance

http://www.youtube.com/watch?v=cLM5-2vZbgo