Sounds like you need to add some relationships and talbes to your database.
How many tables do you have?
Hi, thanks for the reply.
I have all the data in the database related to the respective serial numbers. I have a separate file that is linked to a parent file via a portal. The user enters the data in the parent file with repsect to the appropriate serial number. Is this what you mean by having some relationships set up, or did you have something else in mind?
When you ask how many tables do I have, what exactly do you mean? I mean, I have a table per layout, but I am sure that is not what you mean. Sorry for the newbness :)
Here's some basic info that might help you understand:
A Table is used to store Unique Entities: Contacts, Items, Invoices, etc. Each Record in the table would have it's own unique Id, commonly known as a Primary Key.
In your case, it sounds like you'd need to create the following tables: Items, Machinists, Comments_List.
Each Item and Each Machinist would have a unique value/primary key. The Comments_List table would allow you to create some Many to Many relationships.
Many Machinists might make comments on Many Items.
so your relationship would look something like this; (Tables are in Bold, with the related fields below each table)
Items ====> Comments_List <===== Machinists
pk_Item_id fk_Item_id pk_Machinist_id
By establishing these tables and relationships and through the use of portals, you'll then be able to see all the comments for an Item while in the Items layout, and a list of all the Machinists who've made comments for an item. In the Machinists Layout, you could see all the Items a machinist has made comments on.
You would also be able to perform Counts of related records in the Comments_List table based on the comment selected.
And all this would be under one "roof", one database, instead of multiple databases and files.
That's definitely an improved database structure. However, if you want a "quick and dirty" count like you describe, you might try something like this:
Define a calculation field such as.
If ( commentfield = "Bad" ; 1 ; 0 )
Define a summary field as "total of" this new calculation field and you'll have a count of all the items where the comment field = "Bad". There are variations possible where you can do the same thing for cases where commentfield contains the word bad and you can also set up additional pairs of fields to count other entries.
rdowler: Thank you so much for such an indepth answer. What I think I might do, since I have something that is not organized at all like you have suggested, is to go ahead with the quick and dirty method that Phil had mentioned for now, and then, once I have a better understanding of what is going on, try to make a new database from the ground up using your method. I don't think it is very easy to try to retrofit the old one no?
Phil: Thank you for your advice on this post, I will definately give this a try and come back to let you know how it worked.