Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SetUp Advice (Access 2000)

    <img src=/S/question.gif border=0 alt=question width=15 height=15> Hello All,
    I am in mist of updating a large database so I am able to retrieve data based on dates. My question is, how should I design the database so I can track actions that happen throughout the year. Example: I have a project. It is given a number to identify this project. There are actions that happen and these actions are noted with dates. I need to be able to query the different actions based on date time frame. Based on the query of previous, I need a count of each type of action.
    I am updating this database so the Supervisor can go to the database and click a few buttons and have all the information she needs for a monthly report.
    Thanks for any advice or links to other information.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SetUp Advice (Access 2000)

    You could create a form frmDate with text boxes txtStart and txtEnd. Set the Format property of these text boxes to Short Date or whatever date format you prefer.

    Create a query based on one (or more) of your tables.
    Select View | Totals.
    Add the action fields you need/
    Leave its Total option set to Group By.
    Add the action field a second time.
    Set its Total option to Count.
    In the first empty column in the query grid, enter

    [Forms]![frmDate]![txtStart]

    Set the Total option for this column to Where.
    In the Criteria line, enter

    <=[DateField] Or Is Null

    where DateField is the name of the date field.
    In the next empty column, enter

    [Forms]![frmDate]![txtEnd]

    Clear its Show check box.
    In the Criteria line, enter

    >=[DateField] Or Is Null

    Save the query. You can use this query as record source for a report.
    You can open the report from frmDate, for example from the On Click event procedure of a command button.
    The user can enter start and end dates, or leave one or both of them blank.

    You can add other controls on the form if needed, and add criteria to the query.

Posting Permissions

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