Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2010
    Posts
    36

    Unanswered: How seperate months from dates and show in query

    Dear Access guys;

    Just a question. Maybe it is not very professional but i need to apply it in my access project.

    I have a list of hospitals that is stored in table and the shows the start and end date of hospital activity. I need to make a query to seperate the months and list the months that the hospital was active.

    i have attached the file. please look at the file and you may understand will.

    do you know have can i achieve this.

    thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Use a crosstab query. Have Hospital and year as your rows and set these to group by then have the month as your column. Use the query wizard to create the query. There is one thing that could be annoying in that the months will be alphabetical order not calendar order.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    But you put then in order by adding the col heading "Jan,feb,mar,apl,......."

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Yes you set the headings to Jan Feb etc. But the user may require a rolling 12 month period which takes a bit of coding etc.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Yes but the first query could look at the last 12months then the cross tab them look at that query

  6. #6
    Join Date
    Dec 2010
    Posts
    36

    How to seperate months form date and show in query

    Quote Originally Posted by Poppa Smurf View Post
    Yes you set the headings to Jan Feb etc. But the user may require a rolling 12 month period which takes a bit of coding etc.
    Can you help me with the code!

  7. #7
    Join Date
    Nov 2010
    Posts
    84
    In the attached file you have an example of how you can solve your problem without programming.
    Attached Files Attached Files

  8. #8
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by eremija View Post
    In the attached file you have an example of how you can solve your problem without programming.
    i cannot open the zip file you attached. can you send to my email: hafizhafiz@gmail.com

  9. #9
    Join Date
    Nov 2010
    Posts
    84

  10. #10
    Join Date
    Nov 2010
    Posts
    84
    Now I hope that you can unpack.
    Attached Files Attached Files

Posting Permissions

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