By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,742 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,742 IT Pros & Developers. It's quick & easy.

Count instances of values in a query

P: 52
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 [subtblCAssessments].

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
Table Name=tblClientLog
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. C_Key; AutoNumber; PK
  3. C_Location; Numeric; (looks up tblLocations)
  4. C_Client; String
  5. C_ClientDate; Date/Time
  6. C_StartTime; Date/Time
  7. C_Hours; Numeric
  8. C_CType; Numeric; (looks up tblcontacttype)
  9. C_Interaction; Numeric; (looks up tblinteracts)
  10. C_Gender; Numeric; (looks up tblgenders)
  11. C_Age; Numeric
  12. C_Race; Numeric; (looks up tblethnicity)
  13. C_Orient; Numeric; (looks up tblorientation)
  14. C_PhysOrth_Disability; Yes/No
  15. c_Blind_Disability; Yes/No
  16. C_Deaf_Disability; Yes/No
  17. C_Cognitive_Disability; Yes/No
  18. C_Developmental_Disability; Yes/No
  19. C_MentalIllness_Disability; Yes/No
the subform to the previous table is as follows
Expand|Select|Wrap|Line Numbers
  1. CA_Key; autonumer; PK
  2. CA_C_Key; numeric; FK from tblClientLog
  3. CA_Ass; numeric; (looks up tblAssessList)
the list of assessments is in tblAssessList is as follows
Expand|Select|Wrap|Line Numbers
  1. ASS_Key; autonumber; PK
  2. 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_ClientDate; 04/08/2007

CA_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!

May 28 '07 #1
Share this Question
Share on Google+
2 Replies

P: 49
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
May 28 '07 #2

P: 52
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!
May 28 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.