Results 1 to 5 of 5
  1. #1
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202

    Question Is weekly / monthly automation possible?

    I am curious to know this because the way the database I am working on is set up was never intended to hold the values that I am now being requested to add, which isn't a big deal, I can knock out the tables for the new fields easily. It comes down to the information that the new table(s) are going to store.

    It will be a weekly and monthly breakdown of mileage usage for our assets. What I would like to know is if it's possible to have Access when opened perform a calculation each week and each month.



    I need to have the operation compute the weekly & monthly usage based on the weekly total mileage entered by the user, and store this data in it's own table.

    If this can be done, can you please give me some links where I can read up on how to do this or give me some examples on how this works so I can play with it.

    TIA!

  2. #2
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Ok so apparently the automation part isn't so important. Moreover he would just like a button he can press that will make the calculation. (or I can set an afterupdate event to take care of it)

    So I guess my question then becomes how do you have a calculation export the result to a different table?

    Would an Update Query work? If so, do I enter the equation in "Criteria" or "or"?

    And then what functions would be used in the equation to subtract the new entry from the previous entry?

  3. #3
    KathyL is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    94
    Quote Originally Posted by 10 Gauge View Post

    I need to have the operation compute the weekly & monthly usage based on the weekly total mileage entered by the user, and store this data in it's own table.

    TIA!
    A query which summarized records based on date ranges would generate your results. Then, turn it into a make table query.

  4. #4
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by KathyL View Post
    A query which summarized records based on date ranges would generate your results. Then, turn it into a make table query.
    I'm unfamiliar with the make table query. From the sound of it though when the query runs it makes a brand new table every time to hold the output data? If that's the case that's not what I need. I've already got the table in place now for the data. Would it be an update query to do this, opposed to a make table query?

    I am also having trouble figuring out how to pass what to search for based on the forms AssetID number.

    This type of query is new to me, I've only done pretty basic querying in the past. I think I have my criteria for the calculation correct, or at least close enough to correct that I would be able to figure it out if it didn't work.

    This query is going to be run on a subform and will need to reference a control on the mainform, or a field on the table being queried.

    The one I am working on first is like this: The latest entry minus the previous entry for a specific AssetID and then output the calculated number to a different table. I will also need to have some other information transferred as well such as the AssetID # and a date, but that's all simple, no calculations involved.

  5. #5
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    After reading on query types I've decided that an Append Query seems appropriate for what I am trying to do.

    Here's what I've started with for my query, does this even look slightly correct?

    Edit: I'm going to start a new thread since the topic of this thread doesn't necessarily correct anymore.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query + monthly report
    By tareksul in forum Reports
    Replies: 3
    Last Post: 12-19-2010, 01:09 PM
  2. Weekly reporting comparisons in query
    By Schon731 in forum Queries
    Replies: 3
    Last Post: 10-21-2010, 06:59 PM
  3. Excel Import/Append Data (weekly)
    By MartinL in forum Import/Export Data
    Replies: 1
    Last Post: 08-12-2010, 06:14 PM
  4. Weekly calculation query?
    By katie_88 in forum Queries
    Replies: 1
    Last Post: 07-13-2010, 10:54 AM
  5. weekly total
    By nkuebelbeck in forum Queries
    Replies: 2
    Last Post: 03-24-2010, 02:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums