I have a table [tblClientLog] and a subtable [tblCAssessments].
CAssessments is a combobox that looks up a table called [tblAssessList]
AssessList has 2 columns, an autonumber PK and a string for the title of the Assessment. When the user meets with a client, they input the valid demographic details and time spent with client, and then in the subtable CAssessments they input any assessments they ran with their client. I need to have a report of the total number of hours spent between Date 1 and Date 2. Also, I need to have a report on the number of times each assessment in [tblAssessList] exists in [subtblCAssessme nts].
I am not sure where to begin to have this do what I need it to do. The other thing is that I need it to count each instance of a unique assessment, but if someone adds an assessment title to the list, I need a count for that one too. I really don't know where to begin! Any suggestions? the metadata for the three tables is as follows
[code]
Table Name=tblClientL og
Expand|Select|Wrap|Line Numbers
- Field; Type; IndexInfo
- C_Key; AutoNumber; PK
- C_Location; Numeric; (looks up tblLocations)
- C_Client; String
- C_ClientDate; Date/Time
- C_StartTime; Date/Time
- C_Hours; Numeric
- C_CType; Numeric; (looks up tblcontacttype)
- C_Interaction; Numeric; (looks up tblinteracts)
- C_Gender; Numeric; (looks up tblgenders)
- C_Age; Numeric
- C_Race; Numeric; (looks up tblethnicity)
- C_Orient; Numeric; (looks up tblorientation)
- C_PhysOrth_Disability; Yes/No
- c_Blind_Disability; Yes/No
- C_Deaf_Disability; Yes/No
- C_Cognitive_Disability; Yes/No
- C_Developmental_Disability; Yes/No
- C_MentalIllness_Disability; Yes/No
Expand|Select|Wrap|Line Numbers
- CA_Key; autonumer; PK
- CA_C_Key; numeric; FK from tblClientLog
- CA_Ass; numeric; (looks up tblAssessList)
Expand|Select|Wrap|Line Numbers
- ASS_Key; autonumber; PK
- ASS_Name; string; (title of assessment)
01; Addiction Severity Index
02; Beck Depression Inventory
03; Outcome Questionnaire 45.2
tblClientLog (filtered to a few pertinent fields)
C_Key; 01
C_Client; A1B37C
C_Hours; 3
C_ClientDate; 05/19/2007
C_Key; 02
C_Client; A2B37D
C_Hours;1
C_ClientDate; 04/08/2007
tblCAssessments
CA_Key; 01
CA_C_Key;01
CA_Ass; Addiction Severity Index
CA_Key; 02
CA_C_Key; 01
CA_Ass; Outcome Questionnaire 45.2
CA_Key; 03
CA_C_Key; 02
CA_Ass; Outcome Questionnaire 45.2
CA_Key; 04
CA_C_Key; 02
CA_Ass; Beck Depression Inventory
I need to have something that counts these instances and reports:
Addiction Severity Index = 1
Outcome Questionnaire 45.2 = 2
Beck Depression Inventory = 1
Total Client hours from date 1 to date 2 = ?
(i.e. hours between 04/25/07 and 05/25/07 = 3)
(i.e. hours between 03/25/07 and 05/25/07 = 4)
If that wasn't as clear as I'm hoping, please let me know! I'd appreciate any guidance you can share!
Thanks so much!
theAybaras