Determine dates occupied

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Annette Massie
    #1

    Determine dates occupied

    I have a campsite database that tracks the campsite, date arrived and
    number of days stayed.

    I need to have statistics showing how many campers were at the
    campground on any given day or date range. For example, the month of
    July I have 2 campers on July 1, 7 on July 2, 28 on July 3, 29 on July
    4, etc.

    Should I be collecting the data differently that the date arrived and
    number of days stayed?

    If a camper arrives July 2 and stays for 10 days, how can I show that
    the campsite was occupied on July 2, 3, 4, 5, 6, 7, 8, 9, 10 and 11?

    Thanks,
    Confused/In need of help!
    Tags: None
    • Gary Floam
      #2
      Re: Determine dates occupied

      Hello Annette,

      Do see how many people were at your campsite on a given date, the query
      criteria is

      Between [ArrivalDate] and ([ArrivalDate] + [DaysStayed] - 1)

      The -1 is because, I assume, that if a person arrives July 1 and stays one
      day, you do not want to count them for July 2.

      Access date arithmetic has the nice feature that one is the same as the
      integer 1, so you can add a number of days to a date field easily.

      Hope this helps.

      Gary

      "Annette Massie" <annettem@co.sa int-croix.wi.us> wrote in message
      news:c67abda1.0 309260358.4a64e ce0@posting.goo gle.com...[color=blue]
      > I have a campsite database that tracks the campsite, date arrived and
      > number of days stayed.
      >
      > I need to have statistics showing how many campers were at the
      > campground on any given day or date range. For example, the month of
      > July I have 2 campers on July 1, 7 on July 2, 28 on July 3, 29 on July
      > 4, etc.
      >
      > Should I be collecting the data differently that the date arrived and
      > number of days stayed?
      >
      > If a camper arrives July 2 and stays for 10 days, how can I show that
      > the campsite was occupied on July 2, 3, 4, 5, 6, 7, 8, 9, 10 and 11?
      >
      > Thanks,
      > Confused/In need of help![/color]


        Comment

        • TC
          #3
          Re: Determine dates occupied

          Personally, I would store DateArrived and DateDeparted. This is because it
          is easier to get a date correct, than to get the # of days stayed correct.
          We all know today is 28 Sep 2003 (or whatever), but have we been here 13
          days, or was it 14?

          Either way, you then write queries to give you whatever you want, from that
          data. For example, storing DateArrived and DateDeparted, this would find the
          number of campers at site #123 on 1/1/2003:

          SELECT COUNT(*) FROM tblCampers
          WHERE CampsiteID = 123
          AND #1/1/2003# BETWEEN DateArrived AND DateDeparted

          HTH,
          TC


          Annette Massie <annettem@co.sa int-croix.wi.us> wrote in message
          news:c67abda1.0 309260358.4a64e ce0@posting.goo gle.com...[color=blue]
          > I have a campsite database that tracks the campsite, date arrived and
          > number of days stayed.
          >
          > I need to have statistics showing how many campers were at the
          > campground on any given day or date range. For example, the month of
          > July I have 2 campers on July 1, 7 on July 2, 28 on July 3, 29 on July
          > 4, etc.
          >
          > Should I be collecting the data differently that the date arrived and
          > number of days stayed?
          >
          > If a camper arrives July 2 and stays for 10 days, how can I show that
          > the campsite was occupied on July 2, 3, 4, 5, 6, 7, 8, 9, 10 and 11?
          >
          > Thanks,
          > Confused/In need of help![/color]


            Comment

            Previous template Next
            Working...