finding field data in records with similar names

[smcorptrs]

We use our database to input and print workorders and invoices. Each record has a name, address, city with zip, & phone# fields. I want to keep a running total of invoice totals by city, but several cities have multiple zips. How do I sort, find and display data?

[CobaltSky]

Hello Jamie,

If I understand you correctly, your problem stems from the fact that the city name and zip code are both in the same field. Otherwise you would be able to summarize based on the City field and get the running totals you want.

If that's the case, one solution would, of course, be to create an additional field and separate the City and Zip data. But alternatively, you could create a calculation field with a formula along the lines of:

LeftWords(City, WordCount(City) - 1)

Which will drop the zip code off the contents of the field. The you will be able to use the calculation field to generate your summaries.

NB The calc field need not appear on any layouts - or perhaps only on a sub-summary report layout that presents the sub totals. Basically it will work 'behind the scenes'.

[smcorptrs]

This may sound dumb, but how does this calc field allow me to find a running total of each city? My city & zip on invoice is pulled from a index lookup off a sepeerate data base that stores city, zip & tax rate. Can you explain more on how to apply the calc field you suggested so I can gather city by city Total sales?

Thanks

[CobaltSky]

The two most frequently used methods for obtaining summary data are the use of summary fields - generally arranged within sub-summary parts on special report layouts, or by using aggregate functions such as Sum( ), Count( ) and Max( ) etc to target appropriate fields via a relationship.

In either case, you will require a field which has a value which is unique to (and general to) each group of records you wish to summarize.

It was my understanding from your earlier post that you are unable to do this with your current data structure because the cities have multiple zip codes, - meaning that if you sumarise by either of the afforementioned methods - or any other for that matter, your data will be sub-grouped by city/zip values rather than for ciry records as a whole. I therefore suggested a method by which you could isolate the city values so that these could be used as the basis of your summaries.

So for instance, you could set up a summary field, then place it on a report layout within a sub-summary part which is defined to appear when the database is sorted by city. Then you would be able to call up a listing of summary data by city by simply sorting the file and switching to the report layout (both steps could be stored in a script for instant access).

Similarly, the calc I suggested would enable you to establish a relationship (eg a self-join relationship - one which matches the file to itself) based on the calc field and to then create a series of unstored calculation fields which will return summary data about each city via the relationship. Eg the formula Count(YourRelationship::City) would tell you how many records there were in all for each city. Similarly the formula Sum(YourRelationship::TotalSaleAmount) would return the gross dollar amount sold for each city.

There are various other methods which might also be worth considering, howver which is appropriate depends on a range of factors including file size and how and when (and to whom) you want the information displayed, how frequently it is to be updated, whether a history is required or only current data etc etc

I'm afraid that in my earlier reply I only answered the question I understood you to be asking...

[smcorptrs]

Duh.... I see now. It really helps when someone else paints the picture for you. Thanks for the help!!!