Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Annual reporting (once a year) (A2k)

    I have a database of people, each assigned a pay grade. Depending on their pay grade, they receive a report once a year on their performance (called an AN report (annual). Reports are different months for each pay grade, the due dates are stored in a table, example below). What I would like to do is create a bit of code that will add a record to a table if their report due date has passed, but I don't want a new record to be added EVERY TIME the code is ran (I only want this person on the table once for this reporting occasion. I cannot use a PK because if the person performs adversely during the year, they will receive another report, with a different report occasion). If the record is created in the table (indicating their report is due), I can use other columns in that table to track the location of the report, etc. Once a final box is checked, the record will be appended to an archive table, indicating that that individual's report for THIS YEAR is complete. I also need to figure out how to prevent the record from being added.


    <table border=4><td>[b]Grade</td><td>Due Date</td><td>E5</td><td>31-Mar</td><td>E6</td><td>31-Dec</td><td>O3</td><td>31-May</td></table>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Annual reporting (once a year) (A2k)

    Do you want to do this for a specific person, for example the person currently selected in a form, or would you like to do it for all persons in the personnel table, for example when the database is opened, or when the operator clicks a command button on a form?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Annual reporting (once a year) (A2k)

    Jeremy

    Why don't you create a query, add a new field from that called Expiry and add this:

    Expiry: iif([Due_Date) >format(now(),"dd-MM"),"OK","Expired")

    You can then create a report of all the expired or overdue performance records

    Would this be easier than adding repetitive and redundant date in the form of new records?
    Jerry

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annual reporting (once a year) (A2k)

    Hans, I would prefer the code ran automatically on the personnel table, (it would only have to run on the due dates, actually, and only once). I would actually prefer it to only run once, and only for those specified as "due". I.e. because E5 is due 31-Mar, I am trying to get this so that on 31-Mar, all personnel with the pay grade of E5 are added to my table., on 31-Dec all E6, etc... I think to make this code run once a year I may have to add another field on my due date table, so when the code is run and the records are added, the current year is added in the field on my table (the example below would allow the code to check and see if the records have been appended this year, and since the year is 2004, it would know that these need to be added again).


    <table border=1><td>Grade</td><td>Due Date</td><td>Last Appended</td><td>E5</td><td>31-Mar</td><td>2004</td></table>


    Jerry - thanks for your advice. I will look into how I could make that work. I still want to create an archive table so that once a performance evaluation is completed, the fields are reset to null (in preparation for next year), and I can know who were the officials on the report, as well as when the report was finally submitted.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Annual reporting (once a year) (A2k)

    OK this is not the full solution but what I have in the back of my mind is an append query to get this done without looking at the table structure I can't really build it but

    append queries

    This should get you some ideas
    Jerry

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

    Re: Annual reporting (once a year) (A2k)

    Like Jerry says, an append query is what you need. I have attached a simple demo with three tables:

    - tblPersonnel holds personnel info
    - tblGrades specifies the due date for each pay grade
    - tblReport is the table you want to append to.

    There is one query, qryAppend that selects all records from tblPersonnel for which the due date has passed and that haven't been appended to tblReport yet, and appends them. You can run this query whenever you want.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Annual reporting (once a year) (A2k)

    Nice example Hans <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annual reporting (once a year) (A2k)

    Hans, you're amazing, the example is perfect! Thank you so much.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annual reporting (once a year) (A2k)

    Okay, I plan on changing the append query a little to use WHERE the report date = a combo box on my form...I'm trying to figure out a way to prevent the query from being run more than once a year for each date. Any suggestions? (not solutions, just suggestions)?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Annual reporting (once a year) (A2k)

    OK thinking off the top of my head...could this be controlled by an Admin form in an administration area? The form can have a security layer to it to allow specific people. Could have some type of control that shows last time report was run say a text box and some VBA that switches a command button off if the date, now, is less that 365 days, if it is >365 a hidden text box appears stating REPORT OVERDUE and the command button becomes active.
    Jerry

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Annual reporting (once a year) (A2k)

    Looking at the question again have you thought of using the Calendar Control in you Toolbox in form design. I can help you with the code but if I remember rightly there are some good examples already on the board. Rudi had this one a few months ago <!post=Calendar Combo Control,449506>Calendar Combo Control<!/post>
    Jerry

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

    Re: Annual reporting (once a year) (A2k)

    The query won't do anything if it has been run already, so there is no real need to prevent it from running more than once.

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annual reporting (once a year) (A2k)

    Hans,
    I understand the query won't do anything if the record is already in the table, however once the report is complete, it will be removed from the table. (I guess the most simple work-around is to not remove them from the table). What I don't want to happen (Because different people do multiple jobs, including each others') is for one person to run the query, get some reports completed and removed, and then click to run the query, inputting those records *again*. I think I'll take what you gave me and make that work (by leaving the records on the table). Thanks again! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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