Tool Count in individul records with condition

[rkalapura]

Hai,

I have a form in Access 2010 where we define the tools using for assembling products. We have more than 2500 products and each of them need different tools for assembling components together, named as "Tool Kit". There are 20 standard tools in the form, named separately. Technicians define "Tool Kit" needed for each product with tool number and its location. In most cases, 10 to 14 tools will need to assemble a product. Tools which are not required for a product is defined as "N/A" in the form. Very rare products need all 20 tools for assembly.

My question is, how do I get a count of tools defined for each product? I mean, I need a count of fields which are not defined as "N/A" of each record and keep the value in the field "ToolCount" in the same record. Is there any query, macro etc. which I can get the tool count easily, other than counting tools in each record and enter the value manually.

I spent a lot of time using If -- then---Elseif --- etc. Nothing worked out. Appreciate your valuable help.



Thank you and God Bless You.

[amrut]

See Dcount http://www.techonthenet.com/access/f...ain/dcount.php
Use [YourFieldName]<>'N/A' for criteria ( Single quotes around N/A required if it is a text field)

[rkalapura]

Thank you Amrut for spending some time, trying to solve my problem. Dcount function, as far as I know, counts all records in the given domain (table/query), within the specified criteria. Here I need to count the tools of each record, not the table. Just like how many field in a form has data other than "N/A". Any function or provision available in Access 2010?

Thank you for helping. God Bless You.

[orange]

We have more than 2500 products and each of them need different tools for assembling components together, named as "Tool Kit". There are 20 standard tools in the form, named separately.

Have you considered a basic 3 table approach?

Product
ProductId PK
ProductName
other product specific info

Tools
ToolId PK
ToolName
other Tool specific info

ProductRequiresTools (identifies which tools are required for which product)

PRTId PK
ProductID FK to product
ToolId FK to tool
any other info specific to this Product and this Tool combination

To get the Tools required for a specific product

Select ToolID from ProductRequiresTools
WHERE  ProductID = [Enter the Product Id ]