Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a query (2002)

    Hi,
    I have a database which tracks water orders and would like to create a monthly usage report based off the orders.

    An example of the info in the order is shown below
    Date for Delivery Points On/Off/Inc/Dec

    4/1/2007 10 On
    4/3/2007 13 Inc
    4/5/2007 10 Dec
    4/7/2007 0 Off

    From this I would like to create a query/report that does this

    Date Points
    4/1/2007 10
    4/2/2007 10
    4/3/2007 13
    4/4/2007 13
    4/5/2007 10
    4/6/2007 10
    4/7/2007 0
    4/8/2007 0

    Then from this I can calculate monthly usage from the daily info.
    Any suggestions and thanks, Lynne

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

    Re: Creating a query (2002)

    You'd have to create a table containing all dates. You can create it in Excel (where filling down dates is very easy) and import it into Excel, or use VBA code to populate it.
    You can then create a query to determine the most recent date in the delivery table for each date in the dates table, and another query that uses this query to generate the list you want.
    See the attached zipped sample database.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Location
    Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a query (2002)

    Thanks, this is exactly what I was looking for, just didn't think about a date table.

    Thanks again, Lynne

  4. #4
    New Lounger
    Join Date
    Apr 2003
    Location
    Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a query (2002)

    Edited by HansV to present data in table format

    Hi,
    I know this was posted a month ago but I'm finally working on this again. I'm having issues....since I have multiple Delivery location (each has a unique identifier) I added the seq no to the date query so it would populate the consecutive dates for each delivery location (example in the intial post). But it is listing all changes in the delivery location instead as the points change see the following example

    PointsQuery

    <table border=1><td>TO_Gate</td><td>Wier Size</td><td>Acres</td><td>Alloted(AF)</td><td>On/Off/Inc/Dec</td><td>ReportDate</td><td>Daily Points(FT)</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/20/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/21/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/22/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/23/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/24/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>Decrease</td><td align=right>4/25/2007</td><td align=right>0.09</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/25/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>Decrease</td><td align=right>4/26/2007</td><td align=right>0.09</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/26/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>Decrease</td><td align=right>4/27/2007</td><td align=right>0.09</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/27/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>Decrease</td><td align=right>4/28/2007</td><td align=right>0.09</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/28/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>Decrease</td><td align=right>4/29/2007</td><td align=right>0.09</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/29/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>Decrease</td><td align=right>4/30/2007</td><td align=right>0.09</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/30/2007</td><td align=right>0.11</td><td>0.1BW</td><td align=right>24</td><td align=right>51.01</td><td align=right>178.535</td><td>On</td><td align=right>4/17/2007</td><td align=right>0.14</td><td>0.1BW</td><td align=right>24</td><td align=right>51.01</td><td align=right>178.535</td><td>On</td><td align=right>4/18/2007</td><td align=right>0.14</td><td>0.1BW</td><td align=right>24</td><td align=right>51.01</td><td align=right>178.535</td><td>On</td><td align=right>4/19/2007</td><td align=right>0.14</td><td>0.1BW</td><td align=right>24</td><td align=right>51.01</td><td align=right>178.535</td><td>Increase</td><td align=right>4/20/2007</td><td align=right>0.17</td><td>0.1BW</td><td align=right>24</td><td align=right>51.01</td><td align=right>178.535</td><td>On</td><td align=right>4/20/2007</td><td align=right>0.14</td></table>
    If I don't include the seq no (TO_Gate) in the date it just lists the orders as shown below

    PointsQuery

    <table border=1><td>TO_Gate</td><td>Wier Size</td><td>Acres</td><td>Alloted(AF)</td><td>On/Off/Inc/Dec</td><td>ReportDate</td><td>Daily Points(FT)</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>On</td><td align=right>4/20/2007</td><td align=right>0.11</td><td>0.1BE</td><td align=right>24</td><td align=right>35.00</td><td align=right>122.5</td><td>Decrease</td><td align=right>4/25/2007</td><td align=right>0.09</td><td>0.1BW</td><td align=right>24</td><td align=right>51.01</td><td align=right>178.535</td><td>On</td><td align=right>4/12/2007</td><td align=right>0.14</td><td>0.1BW</td><td align=right>24</td><td align=right>51.01</td><td align=right>178.535</td><td>Increase</td><td align=right>4/20/2007</td><td align=right>0.17</td><td>0.1MC</td><td align=right>36</td><td align=right>77.60</td><td align=right>271.6</td><td>On</td><td align=right>4/20/2007</td><td align=right>0.1</td><td>0.1MC</td><td align=right>36</td><td align=right>77.60</td><td align=right>271.6</td><td>Off</td><td align=right>4/24/2007</td><td align=right>0</td></table>
    Any suggestions and thanks,
    Lynne

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

    Re: Creating a query (2002)

    The queries become more complicated because you have to involve the delivery location. See the attached modified version of the database I attached last month.
    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
  •