By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Count if query in access 2003

P: 6
I realise there isn't a direct replacement of countif for excel in access but I am struggling to get aggregate functions to work for me in access 2003 queries. I am creating a stock management db, where I scan stock in (scan in table) and scan it out (scan out table) as it is used. Each product has a unique prefix which i want to use as a criteria to search and count both tables and then a simple calculation is required to find current stock.

I have tried:
Count(iif("[Scan in Barcode]"="HA2*",1,0)) which gives me the correct no but when I add an additional column to the query to get a value for a different product it gives me the value of the first column??

Please help

Mark
Dec 14 '08 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
@longmanm
Change Count to Sum and see what happens.
Dec 14 '08 #2

P: 6
Have changed expression to include sum and still get duplication from the first column.

Query Example:

Column 1:
RHP: Sum(Iif("[Scan in]"="RHP*",1,0)) returns a value of 0 which is correct

Column 2:
HAS: Sum(Iif("[Scan in]"="HA2*",1,0)) returns a value of 0, should be 3

Please help!
Dec 14 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
@longmanm
Don't put [Scan in] in quotes

Expand|Select|Wrap|Line Numbers
  1. Column 1:
  2. RHP: Sum(IIF([Scan in]="RHP*",1,0))
  3.  
  4. Column 2:
  5. HAS: Sum(IIF([Scan in]="HA2*",1,0))
  6.  
I think you are getting 0 by default
Dec 14 '08 #4

P: 6
quotes don't appear to make a difference i still get 0 for both queries.
Dec 15 '08 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Change the query view to SQL and copy and paste the full SQL code in here.
Dec 15 '08 #6

P: 6
SELECT Sum(IIf([Scan in Barcode]="HA2*",1,0)) AS Expr2, Sum(IIf([Scan in Barcode]="ABR*",1,0)) AS Expr4
FROM [Tran Scan In];
Dec 15 '08 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
@longmanm
OK I've figured it out, should have realised it before. The statements need to use the Like operator rather than the = operator. Try this:

Expand|Select|Wrap|Line Numbers
  1. Sum(IIf([Scan in Barcode] Like "HA2*",1,0))
  2.  
  3. and
  4.  
  5. Sum(IIf([Scan in Barcode] Like "ABR*",1,0))
  6.  
Dec 15 '08 #8

P: 6
Thats it,

thankyou so much msquared i was beginning to tear my hair out thought i had tried every conceivable option. I had tried using the like operator previously in the criteria of the query.
thanks again
Dec 15 '08 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
@longmanm
Always a problem when there is more than one thing wrong. Happy you got it sorted :)
Dec 15 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.