Alpha Video Training
Results 1 to 4 of 4

Thread: Total No. of occurences, values, filtered by date

  1. #1
    Scott
    Guest

    Default Total No. of occurences, values, filtered by date

    Hi all.

    I have been out of the picture for some time due to major surgery and have just unearthed a large A5 v 5 database I was working on middle of last year.

    It needs some slight modification, as follows:

    One large table has a series of fields I need to manipulate.

    I would like to:

    1. Display the total value.

    The field is a number to two decimal places but sometimes it may be a negative value. This total will be a sum total of all values in the table field column.

    2. Display the number of occurrences.
    Hhow many times this field has a value, whether positive or negative.

    3. Both of the above must be done in response to dates, by entering a start and end date to display the total value and number of occurrences that fall between the dates entered.

    In plain English the 'From' and 'To' dates will filter the total value and total number of occurrences displayed.

    I'll be able to get my head around it over the next few weeks once I'm up and running properly but being an impatient sort, not to mention being thoroughly frustrated by all this recuperation nonsense, I would like to get a head start on it. At the moment I seem to be staring blankly at the screen...

    Any thoughts anyone ?

  2. #2
    "Certified" Alphaholic Scott Emerick's Avatar
    Real Name
    Scott Emerick
    Join Date
    Dec 2000
    Location
    Central Virginia/ North Carolina Border
    Posts
    2,154

    Default RE: Total No. of occurences, values, filtered by d

    Scott,

    1. Display the total value.

    The field is a number to two decimal places but sometimes it may be a negative value. This total will be a sum total of all values in the table field column.


    TABLESUM() will handle this.

    2. Display the number of occurrences.
    Hhow many times this field has a value, whether positive or negative.


    TABLECOUNT() will handle this.

    3. Both of the above must be done in response to dates, by entering a start and end date to display the total value and number of occurrences that fall between the dates entered.

    Both TABLESUM() and TABLECOUNT() allow a filter expression. If you create start date and end date varialbes, then you can use them in your filter. I suspect your filter would use BETWEEN()for the date handling portion.

    Hope this helps, and hope your feeling better.

    Scott

  3. #3
    Scott
    Guest

    Default RE: Total No. of occurences, values, filtered by d

    Thanks for your reply.

    By the time I'd read it I was already some way through a solution using the methods you describe. My brain, it would appear, has not gone completely stale and after much coaxing and strong coffee it was persuaded to do its thing ;o)

    I'd be interested in any alternatives though. This one works well and is a simple solution (two excellent reasons for using it) but I always like to know two or more roads to my destination.

    Many thanks for taking the time to reply. It was greatly appreciated and came at just the right time to reaffirm that I am still meandering along the right track.

  4. #4
    Volunteer Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Lexington, KY
    Posts
    1,460

    Default RE: Total No. of occurences, values, filtered by d

    If you want to make this more difficult than the above solution, use X-Basic. You can query the records, then step through each of the filtered records, and with two variables, total the appropriate field and count the number of records where the field has a value.

    Hope this helps.

    Regards,

    Jay Talbott

Similar Threads

  1. Date Total on Report
    By spain246 in forum Alpha Five Version 6
    Replies: 18
    Last Post: 07-26-2005, 09:43 AM
  2. Strange Occurences around A5 Home
    By Graham Wickens in forum Alpha Five Version 5
    Replies: 0
    Last Post: 01-04-2005, 04:33 AM
  3. Total for a field, filtered
    By Howard G. Cornett in forum Alpha Five Version 5
    Replies: 2
    Last Post: 10-01-2003, 03:07 PM
  4. Filtered Indexes with a Date
    By Bradley Chesnut in forum Alpha Five Version 5
    Replies: 0
    Last Post: 08-23-2002, 07:15 PM
  5. Problem with counting occurences and calculations
    By Rodney Efird in forum Alpha Five Version 4
    Replies: 2
    Last Post: 01-23-2001, 05:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •