Art Exhibitions            Index           Links           Home             Feedback

Last                                                                   Next

Community Tute 25 - Automatically add records to the Progress Phase tables

1. Start Access and open the database we created in the last tute, select the 'Tables' tab and the 'Main Table' object then hit the 'Open' button. If you did not compete the last tute you can download the database click here to download.

I have entered all the people in the participates table at stagger time intervals. create a query that looks at the participates table and decides if its time they did the phase 1 survey, click new to create a new query.


2. Choose 'Find Unmatched Query Wizard', as you can see form the image below this will allow us to select all the participants who have not done the phase 1 survey.


3. Choose 'Participants' form the list of tables as this is the table we want to output records from in the final query.


4. Choose 'Progress Phase 1' as the table where access looks to see if the participants ID exists, if it is not in this table then it will be included in the query.


5. We much choose which field links the to tables together, choose the ID in both windows and press the '<=>' button.


6. This is the screen where we choose the field we want to output in the query, choose the 'ID' and 'StartDate' fields.


7. Save the query as the default name.


8. Preview the output, as there are currently no records in the 'Progress Phase 1' table all the records from the participants are included in the query.


9. Next step is to create a query that uses the results of the query we just created to append records to the 'Progress Phase 1' table when the time between now and when they were entered is greater than 2 months, create a new query.


10. Click of the Queries tab as we want to base this query on the results of the query we just created. Select 'Participants without matching Phase .." and press 'Add'..


11. Add the 'ID' field and 'StartDate' field and add a calculation field as the image shows, TimeDif: Now()-[startdate]. This calculates the difference between todays date (system date) and the the date field. Set the number of decimal places to zero.


12. Save the query as 'Upcoming Surveys'.


13. Create the query that appends the records to the 'Progress Phase 1' table, base the query on the 'Upcoming Surveys' query we just created.


14. Add the 'ID' and 'TimeDif' fields and limit the records to only include records that have a TimeDif that is greater than 59 days.


15. Change the query type to an append query, and choose 'Progree Phase 1' as the target table (where records will be appended)


16. Save the query as 'Append ID to Phase 1 when due' and close query.


17. Switch to the 'Macros' tab and press 'New' to create a new query.


18. Using the drop menu select 'OpenQuery' as the first action in the macro. Set the query name to 'Append ID to Phase 1 when due' and save the query as autoexec, this causes the query to automatically run each time the database is opened.


19. Insert a row and set the action to 'Setwarnings' this suppress the message 'would you like to append records to..." the macro just goes ahead and does it without any user intervention.





Copyright © 1999 R&D Robotics Pty Ltd, All rights reserved.
Any copyright subsisting in the above code and or literary works including source files vests with R&D Robotics Pty Ltd. ACN 086 345 137


Last                                                                   Next