Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Brussels, EU
    Posts
    2

    Exclamation Unanswered: dynamic query of date periods

    I have a table with persons in different groups and with unlimited different membership entries.

    example:
    PID GID MemberFrom MemberTo
    635 1017 02.03.2000
    635 1017 01.04.2003
    635 1017 01.01.1999
    635 1017 17.07.2001 10.09.2002

    Now the question is:
    How can I build automatically the SQL criteria for my query?

    Just to give a better idea; you wanna know on how many weekly session the person had to participate into his membership period.

    In my first attempt I make a union-query in order to get all dates sorted in one column and to check and build over VBA the string. but it seems tricky, until now too. .-(

    Many txs,
    w.

  2. #2
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15

    Re: dynamic query of date periods

    Where is this query being built? For example, does a user hit a button on a form to open a report?

    And maybe you could rephrase the query question a little bit. I'm not too clear on what it is exactly that you're asking the table to give to you.

  3. #3
    Join Date
    Oct 2003
    Location
    Brussels, EU
    Posts
    2

    Re: dynamic query of date periods

    Just make it comprehensible. Imagine you subscribe to a membership of any organisation. This gives you now the possibility to participate to meetings.
    Now the organisation wants to make a roll call, i.e. know the rate of partecipation of any member: (number of real attendances) / (number of possible meeting) => rate% BUT, to not forget to take into consideration the membership periods of every person.

    Actually i made in a VBA module some queries and code (function) what calculates the theoretical possible numbers of precences and the realistical number precences of the person, always inside his membership periods. Results are be written into a table, in order to make it easily and quikly accessible for forms and reports.
    The user can start the calculation in a form what shows data about the person, limited to this person, but all groups he does belong to.

    The main problem is still, that the person could have membership entries like this:

    MemberFrom MemberTo
    01.01.1999 02.03.2000
    17.07.2001 10.09.2002
    01.04.2003

    At the end of here the chronology is clear and so I should get a date criterion like:
    >=01.01.1999 and <=02.03.2000 or >=17.07.2001 and <=10.09.2002 or >=01.04.2003

    Anyway, this seems me still quite comlicate and gives may to much space for errors they the user cannot be track anymore.

    Another idea I got today:
    Look on every day since the first date ever for the status of the person and the status of the group. Something similar to the home calendar you use to sign appointments.
    In fact I will need a graphical representation in the future to give a better overview.

Posting Permissions

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