Get number or each value in query

[tagteam]

I would like to run a query that looks at an invoice and counts the number of each unique codes.



I have tried by adding the table, then the field that will be used as the search criteria and then using an iif statement to create the two fields.

Fields: invoice created fields: NumberOf7, NumberOf1

Numberof7:IIF([RespCode]=7,count([RespCode],0) and then to get the other number Numberof1:IIF([RespCode]=1,count([RespCode],0)

but when I run it i get the error "your query does not include the specified expression 'invoice' as part of an aggregate function.

I am trying to do this because in a form based on a summary query I want to be able to show the number of 7 an 1's but if i add it to the query it will just create another line.

[orange]

?? Suggest you show us the details of what you are trying to do and what you are working with.

[ranman256]

make Q1 to pull data to get unique codes, set property: UNIQUE VALUES = TRUE
usu: select distinct [code] from table

make Q2 on Q1 to count them.

[tagteam]

I am not really sure how to show you the details.

I have a continuous form that is populated from a totals query. I want to keep one line per invoice so if I add resp code to it (and there are 1's and 7's) it will show two lines and I don't want that. I just want to be able to notify the user if any of the detail records for a summary line ( which could be 2-50 detail lines) has a RepsCode of 7.

So the notification can come on the form some way , i am trying to get a dcount working right now, or maybe in a subquery that counts each of them individually

[Welshgasman]

Why can't you do it in the query?
Here I show the amount of records whose amount is less than 100 and 100 or more for various clients.

It appears to work correctly?

SELECT Transactions.Description, Sum(Transactions.Amount) AS SumOfAmount, Sum(IIf([Amount]<100,1,0)) AS Less100, Sum(IIf([Amount]>=100,1,0)) AS Greater100
FROM Transactions
GROUP BY Transactions.Description;

[tagteam]

I am trying to do it in the query using this

Resp7: DCount("tbl_Repairs","RespCode","InvoiceNumber = '" & [InvoiceNumber] & "' AND [UpdatedResponsibilityCode] = 7")

InvoiceNumber is a field in the query but UpdatedResponsibilityCode is not but that shouldn't matter because I am specifying the value right?

It gives me #Error

[Welshgasman]

Join the table tblrepairs with your table and try like I did?

[tagteam]

Sorry guys, it is Friday.

It does work but I switched the expression and the domain. I have set it right and it is now working.

Resp7: DCount("updatedresponsibilitycode","tbl_ImportedRe pairs","InvoiceNumber = '" & [InvoiceNumber] & "' AND [UpdatedResponsibilityCode] = 7")

Thanks for the help