Count instances of values in a query

[theaybaras]

Hi all,

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

[i]Field; Type; IndexInfo[/i]
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

the subform to the previous table is as follows

CA_Key; autonumer; PK
CA_C_Key; numeric; FK from tblClientLog
CA_Ass; numeric; (looks up tblAssessList)

the list of assessments is in tblAssessList is as follows

ASS_Key; autonumber; PK
ASS_Name; string; (title of assessment)

An example of what I need is as follows. The data in my tblAssessList is:
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

[garethfx]

Ok in reverse order. The issue of counting against unique assessment - use a crosstab query. If you try and build a report directly licked to the crosstab it will not pick up on newer assessment types added so build a report on the fly for now.

building a query based on the client will give to the consultations between 2 dates ( use the criteria to request date input) then a calculation in the same query to sum the hours taken

[theaybaras]

Hi there!

Thanks so much for your response. I've got my crosstab query, and it's doing exactly what I was hoping for! What I'm not sure of is what you are talking about for my report. How do I create my report 'on the fly?' Also, for the second part, when I make my report and query for client hours between two dates, how do I get it to pop up a form for the user to enter the dates into?

Thanks so much! Already you've been so much help!