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

Calculated Count field in Query

P: 25
I am trying to count the number of owners that show up in a query (conveyQuery). The query will produce a column OwnName that will contain names like John Smith, Mike Jones, Frank Vaugn. Each of these names may show up several times and the sorting is such that all the items that person owns will group together. There is a field callled SubParcelNo that has an ID that defines what the owner actually owns. When the query is run, it will return like this

Own Name, SubParcelNo

John Smith, 1
John Smith, 1E
John Smith, L
John Smith, ST
Mike Jones, 1
Mike Jones, 1E
Mike Jones, L
Frank Vaugn, 2
Frank Vaugn, 2E

I am trying to count the number of owners in this query, in the above example 3.
I have tried setting up sub queries, here are some examples:

IIf((Count([OwnName].[ownQuery])>1),True,False)
IIf((Count(OwnerCountQuery.OwName)>1),True,False) As MultOwn
Count([OwnerCountQuery].[OwName]) AS MultOwn
Count([conveyQuery].[OwnName]) As multOwn - this one has bad syntax

(these don't work they give me a message about adding a field that doesn't reference MailCity, the first column in the query, I don't really know what is going on)

Just getting a count would be useful, but the ideal would be adding a column displaying if they were owner 1, 2, or 3. Like this.

Own Name, SubParcelNo, Count

John Smith, 1 , 1
John Smith, 1E, 1
John Smith, L , 1
John Smith, ST, 1
Mike Jones, 1 , 2
Mike Jones, 1E, 2
Mike Jones, L , 2
Frank Vaugn, 2, 3
Frank Vaugn, 2E, 3

I would love to know what I am doing wrong, but really would love to know how to reach the ideal illustrated above (I realize a count doesn't really accomplish that).
Jun 15 '07 #1
Share this Question
Share on Google+
5 Replies


P: 25
I have gotten my subquery working so now my count field shows 3 for my three owners (for all records returned). I would really like to know however how to asign each their respective number (1, 2, or 3). Is this relatively simple?
Jun 15 '07 #2

P: 49
why not use a crosstab?
Jun 15 '07 #3

P: 25
why not use a crosstab?
I am really pretty new at this, and don't really understand the differences. The help files in access just seem to say it is for changing the format so columns in tables become rows. I am sorry to ask what feels like a dumb question, but could you please enlighten me?
Jun 18 '07 #4

JKing
Expert 100+
P: 1,206
I don't think you want to use the count function for what you're doing. Using count on (OwnName) will return 3 because it counts 3 different owners. Out of curiousity what is the purpose of assigning the count number to each owner?

Off the top of my head though a quick fix would be to add an autonumber into the owners table assuming you have a table that stores owner information i.e. firstName, lastName, address etc. For each owner record the table itself would add a new number so then when you query the table it would produce the pattern:
1 Frank
2 Joe
3 Bob
Jun 18 '07 #5

P: 25
I don't think you want to use the count function for what you're doing. Using count on (OwnName) will return 3 because it counts 3 different owners. Out of curiousity what is the purpose of assigning the count number to each owner?

Off the top of my head though a quick fix would be to add an autonumber into the owners table assuming you have a table that stores owner information i.e. firstName, lastName, address etc. For each owner record the table itself would add a new number so then when you query the table it would produce the pattern:
1 Frank
2 Joe
3 Bob
The purpose is for a file name that gets created, if this is the third owner than the file name has a 3 at the end. The problem with autonumber is that there is a many to many relationship between owners and the subparcels. Parcels can have more than one owner and owners can have more than one parcel. I only want to count the owners that apply to a specific parcel that is a parameter in my query.
Jun 18 '07 #6

Post your reply

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