I want to count the number of entries retrieved from a specific field only if it meets certain criteria. For example, if the field is "Maryland," then another field counts the number of "Maryland" entries the field has, even if there are different entries in that field, i.e. if it has "Virginia," then "Virginia" gets counted in a different field. Any thoughts on how to do this?

I have a layout element (say, Maryland, Virginia, Utah for each state).  I also have the one layout element where I  where the states are imported. 

There is another layout element that separates the "state" layout element into the appropriate locations so that each state gets counted according to the results, just how you (TJCajun) have listed.  Any suggestions?

Sans souci, monsieur.

I'm still trying to get a feel for your application, so you may need to correct me, and/or offer more explanation.  It sounds like you have the single layout element that you import the "State" data into, and then separate LE's (one for each state)

This statement is exactly correct. 

There are other LE that have other pieces of information that I import into the database.  You asked what the purpose of the database is.  My company has assigned me to build a database to compile a huge amount of information in relation to our client contracts.  The database includes their name, client number, address, our costs with them, the amount of materials we received from them, and lots of other miscellaneous pieces of information that we need to track.

The LE in question is actually not a state--I just used that as an easier way to state my problem.  The LE is really imported from a Microsoft Excel spreadsheet.  I used to keep track of everything in Excel, but it was ridiculously time consuming, so I starting building this database to take the raw data and sort it into the appropriate fields and perform certain calculations along the way.  Everything is completed with the database except for this and one other issue.

I have programmed one LE to give me a total calculation--that's the easy part.  I import the data into the various parts of the database, and one of them tracks the errors that were made.  There are certain key words in that LE that I want to count in a different LE based off a key word.  For example, if the word "dry ice" is found in the LE, then there is another LE that counts that key word with the previous records that also contained the word "dry ice" in the retrieved records.  There are about twelve different key words that I have in the LE that need to be sorted.  I need to sort them because of the legal or regulatory issues involved.  I hope this helps.

"Sans souci, monsieur" means "without a care, sir."

Are the keywords in the form of a semicolon separated list?
i.e.: Maryland;Virginia;Virigina;Vermont;New Hampshire

The keywords are separated in an Excel spreadsheet and saved as a comma spliced version.  When they are imported into the database in the specific layout element, they are not separated with semicolons.  Instead, each new state is added to a new record.

If you give me an e-mail address, I can send you the .dsr/.dat files as an attachment.  Thanks.

Quote:


Okay, I think I see what you are trying to do here. Each record has a single violation code on the Violations tab. The Summary Tab is meant to contain a running total from all "previous" records. Is this right?

If so, the problem is with the concept of "previous". Databases store records in a randomized order. Just because a record is "before" another does not mean it always will be. You can resort your records at will, thus blowing your running totals. This is the kind of information that should be generated on the fly, as needed, using a Report or a Mass Update. Would you like an example of this? 

If so, do you want it to print to a printer or display on screen?
These report cards appear to summarize violations by state. Is this correct?

Quote:

The Inside Sesame article I mentioned has examples of doing exactly this. See the example of using GlobalValue. Also see the example of using date ranges for how to set the date element in the Retrieve Spec.

Blightman,

I was trying to make you an example, but I can't find any field on your Form that shows the State. I can't summarize by State without a State value. In addition, most of your sample records are empty of everything but a code. Those that are filled in reference Santiago, Chile and Nowheresville, USA. I'm going to need some guidance regarding what I'm meant to be summarizing by.

Actually, at closer look, you talk about the Violations subform, which does exist, but your Main Report Card form does not display this subform. Instead, it has it's own separate set of Violations elements. You appear to have many of your fields and elements repeated three times in various databases.

This is just a suggestion, but I think you may need to finalize and clarify your design before we worry about these programming routines.

Quote:


Having real data to work with will be very helpful, both in terms of producing the examples, and also in terms of you understanding what was done.