Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Unanswered: querying by multiple from-to dates

    Hi gang - first off an apology.

    Ive posted this type of question before but, I still failed to solve it despite support from here.

    So - here we go again

    I have a table with 12 "from" "to" dates in. These are more or less monthly periods that I have to report data on.

    I need to be able to use these dates in a number of differing reports/queries.

    example: to find people by quantity who started training between each of the 12 period dates. Its seems simple enough but for the life of me I can't get there at all. As much as I hate to admit it I suppose I need an example to follow so that I can beat this issue. I'm not a SQL guy at all - never needed it (until now I think).

    Sorry to ask this again but Ive done the whole DB and have had to resort to 12 queries that is interigated by a 13th to at least start the process.

    regards
    a very stressed out Gareth (white hair not optional)

  2. #2
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    Hi.

    Just to clarify what you are trying to do: You want to find out how many people started training between the from and to dates or you want to find the names of people that started training between those two dates?

    Either case, I attached a db that contains 2 tables: datefromto and names
    There are also 2 queries: People (this lists the names of the people within each date group) and CountofPeople (this counts the people in each date group).

    I hope this answers your question.
    If not, please be more specific and I will try to help.
    Attached Files Attached Files

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi O D

    Great I think we are finally on the way here.

    Ok to be a little more specific.

    As you put in the example (which was pretty smack on the money)
    The search will be "equal to or greater than FROMDATE and equal to or less than TO DATE".

    Ill be using this query to
    find quantities of people starting on training / leaving training.
    what qualifications were achieved ("equal to or greater than FROMDATE and equal to or less than TO DATE")
    failed qualifications, resit qualifications etc etc.

    the report will look a little like

    Period No# Course level1 Course level 2 Course level 3
    1 12 18 6
    2 9 2 4
    3 15 19 3

    ETC The People count seems to be on the way. So thats great.
    ill look at it - do a little fiddle about and see where we go.

    In the SQL you have:
    SELECT d.description, Count(n.name) AS CountOfname
    FROM datefromto AS d, name AS n
    WHERE (((n.date) Between d.datefrom And d.dateto))
    GROUP BY d.description

    How do I change the BETWEEN to - greater or equal to and less than or equal to in the WHERE statement?

    Many, many thanks for your help,its been excellant

    Gareth ( hair going darker now)

  4. #4
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    Hi Gareth.

    Glad I could help.
    To answer the greater/less or equal to question:

    SELECT d.description, Count(n.name) AS CountOfname
    FROM datefromto AS d, name AS n
    WHERE (n.date >= d.datefrom And n.date <=d.dateto)
    GROUP BY d.description

    ODaniels

Posting Permissions

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