Count Occurances of a Record? (1 Viewer)

[Capo]

I am working through a lot of data and this is basically what I'm trying to do.

Let's say we have billed occurances for ABC Pet Store. What I need to do, is to see how many "billed lines" appear in the data. So if there are 3 billing occurances (let's say bill amount greater than zero) - in the data I want the query to populate a "billing occurances" field with "3."

How would I do this?

 

[dcobau]

create a group query with something like the following

CountBill: iif([FieldName]>0,CInt(1),CInt(0))

when you group the query ensure you replace Group with Sum in the total field.

Dave

 

[Capo]

Thanks for the help! But, I'm still a bit confused as to how to set this up. If you, or anyone else, can please assist, it would be appreciated!

Here is a bit more info on how things are setup:

I have two tables:

tbl1 has a location ID number and some additional demographics. I have also added a "billing occurance" field.

tbl2 is the larger, billing data file with various billing occurances. There is a field for paid amount.

What I want to do, is to populate the billing occurance field in tbl1 from the occurances of the "paid amount" in tbl2 being >0 . I understand the logic in the formula above, but am unsure of how to do this in an access query.

Can you (or anyone) provide a more detailed description of how to do this?

Thanks so much!