I have created a report in Microsoft Access 2000 which will display the total number of clients that meet the below criteria.
1. Number of clients progressing to part-time or full-time work
2. Number of clients sustaining employment or increasing hours of work
3. Number of clients entered or completed Education or Training
4. Number of clients progressing to self employment or social enterprise
5. Number of clients entered voluntary work (of at least 16 hours per week)
6. Number of clients completing voluntary activity of 3 hrs or more
7. Number of clients completing work placement 20 hours or more
8. Number of clients completing 20 hours or more accumulative activity
9. Number of clients who have achieved a major transition and or completed an intermediate activity with a minimum of 20 hours.
The problem that I am having is that I only want a client to appear a maximum of once in item 1,2,3,4,5,6,7,8 above.
For example if a client has completed a work placement 20 hours or more and progressed to full-time work then they can't be counted in item 1 and item 7. They will only be counted once. This is the part which I am having problems with.
If the client has progressed to full-time work on [url removed, login to view] and completed a work placement of 20 hours or more on the [url removed, login to view] then the report will count the client in item 7 as its the most recent.
There is information in the database which will tell you the outcome date so you can work out the most recent date.
Information for Item's 1,2,3,4,5 is stored in the Outcomes table and the OutcomeDate field will be used to work out the most recent date. Information for Item's 6,7,8 will be stored in the OutcomesInt table and the end date will be used to work out the most recent date, if the end date is null then it will use the start end.
OAchieved --- This table is linked to the Outcomes table
OutcomeInt--- This table contains which activity a client is completing. This table will be used for item 6,7,8
Outcomes --- Information about what outcome each client has achieved is stored in this table. This table will be used for item 1,2,3,4,5
Trans --- This table is linked to the OutcomeInt table.
WFF ---- Information about the client is stored in this table.
Please see the attached database file.
If you have any questions about this project please post on the pmb and I will get back to you asap.