Countif Query - Finding Unique Entries

[jameswilkinsonfjs]

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 [UnavailableItems]; for
example if ABC2 was unavailable

<code <code UnavailableItems
...... .... 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 [UnavailableItems]
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]![UnavailableItems],[RefCode])

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

Any help appreciated,

Thanks, JW191

[CDMAPoster]

On May 19, 11:20*am, [e-mail] 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 [UnavailableItems]; for
example if ABC2 was unavailable

<code*<code* * * UnavailableItems
..... * * * *.... * * * * * * *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 [UnavailableItems]
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]![UnavailableItems],[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....7861ecfba44820

If that doesn't help, post back.

James A. Fortune
CD********@FortuneJames.com

[jameswilkinsonfjs]

On May 20, 4:28*pm, [e-mail] wrote:

On May 19, 11:20*am, [e-mail] 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 [UnavailableItems]; for
example if ABC2 was unavailable

<code*<code* * * UnavailableItems
..... * * * *.... * * * * * * *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 [UnavailableItems]
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]![UnavailableItems],[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
[e-mail]- 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 UnavailableItems
ABC1 0
ABC2 1
ABC3 0
ABC4 0

And Filtered (i.e. showing available):

RefCode Count UnavailableItems
ABC1 0
ABC3 0
ABC4 0

Thanks,
JW

[CenturionX]

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]

ja***************@googlemail.com wrote:

On May 20, 4:28 pm, [e-mail] wrote:

>>On May 19, 11:20 am, [e-mail] 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 [UnavailableItems]; for
example if ABC2 was unavailable

>>><code <code UnavailableItems
..... .... 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 [UnavailableItems]
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]![UnavailableItems],[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
[e-mail]- 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 UnavailableItems
ABC1 0
ABC2 1
ABC3 0
ABC4 0

And Filtered (i.e. showing available):

RefCode Count UnavailableItems
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!Refcnt,Query1!Refcnt,0)

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