Hi! I'm a newbie I’m afraid :)
I work for a charity which cares for ex-offenders inside and outside of prison. I've built a basic access 2003 data base to keep track of our clients, their status, record the meetings we have with them and record a needs assessment which we conduct on each visit. Its form driven and split, the tables stored separately to the forms queries and reports.
The needs assessment questionnaire is conducted through a form which does the following
• Populates a ‘Client ID’ text box with a client ID from a client search form
• Auto populates a random number as the ‘Assessment Number’ which Is the primary key
• Auto populates the current date and time
• Allows the user to enter a score of 1-5 for 16 different needs assessment categories
I have another form which allows me to view all needs assessments for a particular client (the client ID being populated from a client search on a previous form.
I also have 2 queries which return the specific needs assessment scores on either form, the issue is that they appear as 16 different fields and aren’t chartable in the manner I require.
Here is my problem. I need to display the results of the needs assessment questionnaire on the forms ideally, or as a report I guess, and as a radar chart (i.e. 16 data points in a radar chart).
I’ve tried
• using the chart wizard but it only allows 6 fields
• embedding excel charts and sheets and linking the form/query data, but I can’t work out how to link the data from a form text box (or a query) to specific cells in an embedded excel sheet
• Exporting to excel, creating a macro to transpose and chart the data, and re-importing – but I can’t seem to make that work with. The data base is used by multiple users and the tables are stored on a shared drive but the export/import location for the excel file varies for each user
• Searching through the forums available on the web on transposing columns to rows in a query
I’m sure it’s not hard – seems like a reasonably straightforward task – but it’s been frustrating me for weeks!!
Hope someone can help… and thanks in advance
Ross
I work for a charity which cares for ex-offenders inside and outside of prison. I've built a basic access 2003 data base to keep track of our clients, their status, record the meetings we have with them and record a needs assessment which we conduct on each visit. Its form driven and split, the tables stored separately to the forms queries and reports.
The needs assessment questionnaire is conducted through a form which does the following
• Populates a ‘Client ID’ text box with a client ID from a client search form
• Auto populates a random number as the ‘Assessment Number’ which Is the primary key
• Auto populates the current date and time
• Allows the user to enter a score of 1-5 for 16 different needs assessment categories
I have another form which allows me to view all needs assessments for a particular client (the client ID being populated from a client search on a previous form.
I also have 2 queries which return the specific needs assessment scores on either form, the issue is that they appear as 16 different fields and aren’t chartable in the manner I require.
Here is my problem. I need to display the results of the needs assessment questionnaire on the forms ideally, or as a report I guess, and as a radar chart (i.e. 16 data points in a radar chart).
I’ve tried
• using the chart wizard but it only allows 6 fields
• embedding excel charts and sheets and linking the form/query data, but I can’t work out how to link the data from a form text box (or a query) to specific cells in an embedded excel sheet
• Exporting to excel, creating a macro to transpose and chart the data, and re-importing – but I can’t seem to make that work with. The data base is used by multiple users and the tables are stored on a shared drive but the export/import location for the excel file varies for each user
• Searching through the forums available on the web on transposing columns to rows in a query
I’m sure it’s not hard – seems like a reasonably straightforward task – but it’s been frustrating me for weeks!!
Hope someone can help… and thanks in advance
Ross
Comment