Count,Dcount function

[AccessAmateur]

Example:I have a table with 3 fields: Name,Book,NumBooks
Each Name can have more than one book. I want to count the number of books each name has and put it in the NumBooks field.
ie: Update 'NumBooks' to equal count 'Book' for 'Name' = 'Name'. Setting up a Query Update Name to Count(Book) criteria Name = Name gives the very odd error "You tried to execute a query that doesn't include the specified expression NumBooks in an aggregate function". I also tried it using two tables figuring that Name=Name maybe wouldn't work but same result.

Using the Dcount function
Dcount([TableName!Book],"TableName",Name=Name]
the query runs but then wont' update: 'xxx records not updated due to a type conversion error'. Dcount is supposed to be a integer result & NumBooks is LongInteger.
The count function will work in a report: group on Name then count books but how do I get then result back into tha table?

 

[dcx693]

I've got to say, this sounds like a very badly structured table. First, "Name" is a reserved VBA word, so you might want to name it differently. 2nd, sounds like you have a one-to-many relationship between "Name" and "Book". Next, why do you want to store calculated data in the table (in the NumBooks column)?

If it's possible, restructure the database so it's more like this:

Names table
-----------
NameField
other info fields

Books table
-----------
Book
other book info fields

Then create a totals query that counts the number of books found per unique name.

 

[AccessAmateur]

Actually this table is just a simplified example to illustrate/test the problem, the 'real' table is much more complex and doesn't have any questionable field names. I am sure this is not the problem.
My work involves inspecting certain inspectable items (analogy:books) which are located in facilities (analogy:name). The table is actually a database of the inspectable items which is exerpted read-only from a very large system database so I'm not able to change any of the data. I am trying to do some data analysis which generates, among other things, a listing of how many inspectables are in each facility. Thus the name/books/numbooks analogy.
It is possible to generate a report using the count function which will output (count) the number of inspectibles in each facility but this has limitations, for instance it doesn't seem possible to sort the output by number of inspectibles since it is a generated function in the report not a field. I also need to use this number for some further analysis...equivalent to finding the total 'size' of the items in each facility so I need to use the 'sum' function' in the same way as I am trying to do with the 'count' function. I can't get it to work either. (Genrates the same error).

 

[dcx693]

First thing I would do is get a unique list of facilities. You can do that using a query where you choose "Unique Values" from the Query Properties sheet.

Next, I would join that query to the data table holding the "inspectable items" in a new totals query and group the results by facility, counting the "inspectable items". You will then have the counts you need.

 

[AccessAmateur]

>> using a query where you choose "Unique Values" from the Query Properties sheet.

You seem to have functions/properties I don't have. I'm still using Access 97 which maybe doesn't have these or they are well hidden.
However, I think I may have found a workaround: Created a query which will count the number of inspectables grouped by facility name. It appears the only way to do this in Access97 is to use the query wizard which I noramlly don't use and even then the options for grouping don't appear unless you include two sources for the query. Anyway, this query seems to work so converted it into a 'make-table' query making a temporary table which I can then use to update the main table.
Can't update the main table directly, it generates an error "must use an updateable query". It's a roundabout workaround but it makes it possible.