Newbie question

[wyverll]

What I need to do is have a field that counts up the times a name appears acrros serveral records. Basically what I have is 2 files one which contains software infomation and another that contains computer infomation. I need a field in the software (product) file that counts the amount of times a certain piece of software is listed in the records in the computer (master) file.

What I have is

If(Product = master::software 2, 1, 0) + If(Product = master::software 3, 1, 0) + If(Product = master::software 4, 1, 0) + If(Product = master::software 5, 1, 0) + If(Product = master::software 6, 1, 0)

where product is a field in one file, and software (2-6) are fields in a related (master) file (these fields are a value list drawn from the product field).

With the formula that I have it only draws data from the first record to contain data and ignores the rest of the records, I need it to draw the data from all the records and give me a total number of times that a perticular software title appears across all the records.

Thanks for any help.

[CobaltSky]

There are several ways to approach this, depending on how and when you want to use the information.

However, as a starting point, what I suggest you consider doing is to create separate calculation fields for each of your five If statements, then create five summary fields - one to total each of the calculating fields.

The five summary fields will then tell you how many entries of each type of software are present in the current found set of records.

[wyverll]

Unfortantly that doesn't work since each field has a selection of options (the value list) that the user can pick from. The item, eg. Office, Photoshop, Illustrator, etc. might appear in any one of the fields. I need to be able to count the total amount of times that a piece of software is entered across all the fields and in all the records. Doing it the summary-count way just tells me how many pieces of software are on total, not how many of each piece of software is in all the records.

I'm trying to create a Database that keeps track of how many licencies of each title we are using. Since we have over 150 different titles, but only 30 or so on each machine, they can't all be listed in seperate fields, hense having a value list (field - product) of the titles that can be put in to the fields (software 2-35). What we have is a file that lists the computers, and each software title on the machine, the software fields are picked from the value list in a serial number file. I need to have the serial number file have a field that counts and keeps current the total number of times a given title is placed on a machine (fields, software 2-35). I already have a script that will let us know if we need to purchase more lic. but I need to know the total number of each lic. being used for it to work.

Thanks.

[CobaltSky]

OK, I see.

So here is an alternative way to approach it.

Create five relationships from the product to the master, which match the Product Field with each of the five software fields. So you'll have five relationships - let's call them SoftSum1 through SoftSum5.

Then create a new unstored calculating field in your products file with the formula:

Count(SoftSum1::software 2) + Count(SoftSum2::software 3) + Count(SoftSum3::software 4) + Count(SoftSum4::software 5) + Count(SoftSum5::software 6)

That should do it.

[wyverll]

That works great, just what I was looking for.

Thank you very much.