Results 1 to 10 of 10
  1. #1
    Bogusnews is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Auckland
    Posts
    4

    Automatically updating a date

    Hello all, my first query here )).



    I run a window cleaning business I'm using the services template with Access to produce invoices and quotes.

    Because it's window cleaning, a lot of clients want work done on a regular basis. I've added some additional fields in the "customers" table. They are for: date of the last job done, how often they want the work done, and when they want it done again.

    Whenever I send an invoice, I update the date of the last job field. The field that shows when they want it done again is a calculated field. It automatically calculates when the job will be done again based on how often it is to be done per year.

    What I want to know is this:

    Is it possible to get the "date of the last job" field to automatically update by watching the invoice table. As soon as the customer name is spotted, it will automatically enter the date of the latest invoice in the customer table.

    I did this Excel, but don't know how to do it with Access.

    Thanks everyone. Really appreciate it if you could help.

    Cheers

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Date of last job should not even be saved in Customers table. This value can be found by query or DMax() calc.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you have the date in the invoice table, no need to store it in the customer table - that is excel thinking, not database thinking

  4. #4
    Bogusnews is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Auckland
    Posts
    4
    Thanks. I didn't make it clear enough. The date has to go somewhere. At the moment I'm entering it manually in the customer table after an invoice is generated. Perhaps there is a better way to generate the next work date directly from the invoice table?

    All I want is a way for the date of the next job with a specific client to be automatically generated.

    Cheers

  5. #5
    Bogusnews is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Auckland
    Posts
    4
    Ahhh.... cool! thank you. I'll try that.

    Now please excuse a silly question, I'm new to Access... but is the last work date only generated when the query is run?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Aren't you saving date value into Invoices table? Can set DefaultValue property so current date is automatically entered when new record is created.

    A query is 'run' whenever it is used. This can be by reference in another query or in domain aggregate function or in VBA to populate a recordset.

    Don't actually have to open query object to pull data from it. Same as with a table.

    Next job date should also be calculated in query that pulls last job date.

    If you stick with last date field in table, that will require code (macro or VBA) to automatically update that value. Can get complicated as real trick is figuring out what event(s) to put code into.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Bogusnews is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Auckland
    Posts
    4
    Yes. I am saving the date in the invoices table. It is automatically set to today's date.

    So, what I want, is to have the database automatically generate the next date for work to be done, based on the previous date and how frequently the work is to be done. So if I say the frequency is 6 times, then the next job would be the date of the last job plus two months. So at the beginning of each month, I want to run a query for say... "July" I get a list of all jobs to be done this month.

    If I'm hearing you correctly, this should be done with a query. The query would check the dates of the invoices, compare that to the list of customer names, and then calculate which jobs have to be done this month. It would have to look at the frequency because some clients get their windows cleaned every two weeks... others every three months.

    Thanks again for your help. I really appreciate it.

    Cheers

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Since you are admittedly new to database and Access and are having difficulty understanding some of the information/concepts being provided by June7, I recommend that you work through a tutorial on database design. It appears that you may not have your tables and relationships defined to meet your business requirement.
    Have you written a clear description of your business?
    A Customer may have 1 or Many Jobs.
    A Job is for a 1 or more Services.
    A Job may be 1 time or based on some Frequency (weeks, months...)
    etc.
    Review and workthrough this tutorial from RogersAccessLibrary. It will take less than an hour and you will learn a process that can be used with any database.
    More info in the Database Planning and Design link in my signature.

    See this for info on database Query.

    Good luck with your project.

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Can you upload a zipped copy of the database so we can see the table structures?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    think you need to consider some other questions around how your scheduling needs to work

    what happens to your schedule when you have planned breaks (holiday/vehicle in for service)
    what happens to your schedule if you have unplanned events (weather too bad to clean windows/illness/vehicle breaks down)

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

Similar Threads

  1. Updating multiple fields automatically
    By j1976er in forum Access
    Replies: 1
    Last Post: 06-04-2015, 12:25 PM
  2. Navigation Pane not automatically updating
    By thebigthing313 in forum Access
    Replies: 4
    Last Post: 02-05-2014, 09:04 AM
  3. Replies: 7
    Last Post: 07-12-2012, 02:35 PM
  4. Automatically updating dates
    By cinciphantom in forum Programming
    Replies: 0
    Last Post: 02-15-2011, 04:33 PM
  5. Automatically updating matrix
    By reuip in forum Access
    Replies: 2
    Last Post: 06-08-2010, 08:04 AM

Tags for this Thread

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