Hi there, all:
By way of background, I have ZERO knowledge of how to code/script, so please forgive me if I miss any details that would be helpful in the description below.
PROJECT GOAL: create a database of medical expert witnesses that will allow attorneys in my firm to easily:
- Input information concerning the medical experts that we use (initials of reviewing attorney; date of review; name of expert being reviewed; rating of expert on scale of 1 (worst) to 10 (best)); and
- Look up the reputation of a given expert at a given time by going to a search page, entering an expert's name and the date that the search was run, and receiving a single webpage that features [a] a table detailing the average of all ratings associated with that examiner over the past 36, 18, 12, 6, and 3 months; [b] a bar chart that features the range of possible ratings on the x-axis (i.e. 1, 2, 3, 4, 5, 6, 7, 8, 9, & 10) and the number of times the expert was assigned each possible rating over all time on the y-axis.
WHAT I HAVE: Thus far, I have generated two objects and two pages. The objects are called "NAMES" and "REPUTATIONS"; the pages are called "INPUT" and "OUTPUT." These interact as follows:
- Object #1: NAMES: the names of the medical experts that we come up against, stored in three columns (name [first last], title [e.g. MD, DO, Ph.D., etc.], and specialty [e.g. Chiropractic, orthopedic surgeon, pain medicine, etc.])
- Object #2: REPUTATIONS: the reviews of the medical experts that we deal with, stored in four columns (name [first, last], reputation [number on a scale of 1 to 10, where 1 is the least desirable/worst and 10 is the most desirable/best], reviewing attorney's initials [stored as three letters so that questions concerning a rating can be directed to the right party], and the date of the review).
- Page #1: INPUT: This page allows users to input the four pieces of information that are stored in the REPUTATIONS object. With regard the the "name" portion of each record, I have connected the NAMES object to the REPUTATIONS object so that users inputting information using the INPUT page/form can search for a given expert's name, which will auto-populate based on the information found in the NAMES object (the idea being that all reputation information can be associated with a given expert, allowing us to avoid losing data due to misspellings, miscapitalizations, etc.).
- Page #2: OUTPUT: This page currently performs a search of the REPUTATIONS object based on the information in the "name" field, displaying a table of all reviews of a given expert that have been submitted to date. It also allows users to run a report, that shows a bar chart featuring the range of possible reviews on the X-axis (i.e. 1,2,3,4,5,6,7,8,9,10) and the number of reviews for a given rating on the Y-axis. At present, this bar chart does not automatically filter based on the name that the user searched; it does, however, allow users to perform an additional search using the evaluator's name.
WHERE I AM HAVING PROBLEMS: At this point, I am having two problems.
- GENERATING AVERAGE REVIEWS: I would like to be able to dynamically generate the average reviews that have been submitted over given time periods. These averages would be based on [1] the information that users have input over time; [2] the name of the evaluator that a user enters into the search field of the OUTPUT page; and [3] the date on which the user submitted their search using the OUTPUT page.
- GENERATING A BAR CHART BASED ON A USER SEARCH: I would like to automatically generate a bar chart (run a report?) using the data that is stored in the REPUTATIONS object and automatically including a filter that equals the name the a given user searched for in the OUTPUT page.
Any help would be HUGELY appreciated, and, if you are truly interested, I may be able to finagle some money out of our office manager if I can show the bosses a blueprint for how we could bring this database to a point where it would be useable as intended.
Jason Knox