Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

simple count is not so simple

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    simple count is not so simple

    Hello,
    I have a table of data that I would like to get some statistics from. I would never set up a table in this way - except it's data that I am required to send to the state in this format (as if it were a flat file).

    Each record contains client id, name, address, dob, and other data. One individual has more than one record...so all the demographic data is repeated in each record (clunky, I know...I import this data from an old flat file system and only need to deal with it once a year...otherwise, I would set it up in a set.)

    I will explain the simple stats that I would like to obtain from this table - but the formulaes are aluding me. I want a count of the number of people between ages of 0-5 years, 5-10 years, etc. I will also need to count the number of male/female within each age group, as well as count the number of other data in other fields...if I get the formula down for one, I'll be able to get the rest.

    I've tried using client id as a group break and filter the date criteria, but can not get an accurate count.

    What am I missing?
    Thanks for taking a stab at this.
    Wanda

    #2
    RE: simple count is not so simple

    Wanda,
    Add several calculated fields to your report like the following:
    calc->0to5 IF(AGE6to10 IF(AGE >5 .and AGE

    Comment


      #3
      RE: simple count is not so simple

      But this still counts one person several times, thus giving a false total.

      Comment


        #4
        RE: simple count is not so simple

        Can you run a delete duplicate record operation to delete all but one record of each person? Then run the report as above.

        Comment


          #5
          RE: simple count is not so simple

          You could append unique only to another table and then do the calculated fields

          Duane

          Comment


            #6
            RE: simple count is not so simple

            I suppose I could copy the table and manipulate it through deleting duplicates - keeping the original table intact for the state. It would, however, affect other counts, such as how many people without data in - say - the Primary Diagnosis field. So, I would need to duplicate the table several times, deleting duplicate records based on client id and another, different field for each duplicated table.

            I am playing with using a unique filter on client id field, then running a filter for DOB in the report detail. At least, I've managed to get a "head count" of how many in each age group.

            I think no matter how I get the statistics, it's going to be clunky.
            Thanks, Ohlen...nice to hear from you.
            And, thank you, too, John. If I stumble upon a miracle calculation, I'll post it ;-)
            TTFN!
            Wanda

            Comment


              #7
              RE: simple count is not so simple

              Wanda,
              If you append or delete duplicates you will also be deleting valid information, if I read this right. A better way might be to set up another flat file with name and a field for each possible other piece of information. Then do a post to this table to get all the info for each name on one row. Then you can summarize or do reports or whatever and it will be easier to work with.

              Russ

              Comment


                #8
                RE: simple count is not so simple

                Is there any reason why you couldn't mark duplicate records and then count only unmarked records? That way no information is lost by you wouldn't double count.

                Bill
                Bill Hanigsberg

                Comment


                  #9
                  RE: simple count is not so simple

                  Russ, Duane,
                  Great ideas, too! Gee - didn't realize I had so many options...now I just have to figure out which would be the least time consuming. I've been working on this for several hours, today (I should know better), and at this point am probably not seeing the forrest for the trees.

                  I'm shutting down soon...will get back on the board tomorrow if I dream up a solution in my slumber, tonight. (Honest! It's happened to me before...granted, only once...but it could happen, again!)
                  Thanks, again!

                  Comment


                    #10
                    RE: simple count is not so simple

                    That's basically what I'm working on, now. I created an index on client id and chose Unique, only. From there, I marked all records in the index, and am running calculations from that.
                    Thanks!
                    Wanda

                    Comment


                      #11
                      RE: simple count is not so simple

                      Wanda -
                      I am jumping in late after skimming the thread, but...

                      You could also run a summary operation first. You can tinker with the options and have 0-5, 6-10, or whatever, and possibly sift out the duplicates also (group by name or whatever you have). THEN run a report off that summary table.

                      Comment


                        #12
                        RE: simple count is not so simple

                        Hey, Stephen...another great idea. I'm checking out - so I'll play with it again, tomorrow.


                        This brings me to wanting to print all these messages/replies - without the text running off the page. Any of you know how to do that?

                        Gotta go.
                        TTFN!
                        Wanda

                        Comment


                          #13
                          RE: simple count is not so simple

                          Wanda,
                          If you group the report by let's say a CLIENT_NO, you could create a calculated field:
                          calc->runid = run_count(Client_No,GRP->Client_No)
                          This will have a value of 1 for the first record for each client. The next records will have a value of 2,3 etc.
                          Then you could have a calculated formulas like:
                          calc->6to10 IF(AGE >5 .and. AGE

                          Comment


                            #14
                            RE: simple count is not so simple

                            Create a group break on your report and just use both a Group and a Sub-group in your calculated field.

                            The syntax may not be perfect but the concept is:

                            total( IF( ageGrand,GRP->Client_ID)

                            Without the sub-group, the calculation will include all detail records. To set up the group/sub-group, go to the "Advanced" button when using the drag-and-drop list to add a "New/Calc Field". Your group break doesn't actually have to have anything in it, it just has to be there.

                            Comment


                              #15
                              RE: simple count is not so simple

                              Excellent! I worked for several hours while grouping the report on Client ID, to no avail. However, I have not used the "run_count" function, before. In fact, I've often wondered how to number line items in the detail section of other reports...looks like this "run_Count" would do the trick. Thanks! I will try your suggestion and let you know.

                              Thanks, again,
                              Wanda

                              Comment

                              Working...
                              X