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

DCount + Query + Multi-Column

theaybaras
P: 52
I have two tables, "assessments" and "log"

Log has 20 comboboxes that lookup assessments.

User will select a number an assessment in 1 or more boxes.

How can I count the number of times each record in "assessments" appears in any column of my log table? I have to be able to deal with the data of each assessment for each record separately, that is why I used 20 columns.

So to simplify, say I have 5 comboboxes, and 5 assessments to pick from
(ASI, BDI, BSSI, STASSI3, STAXI) and I have the following rows in my log table

1 - ASI, BDI, STAXI, BSSI, STASSI3
2 - BDI
3 - STAXI, BDI, BSSI
4 - ASI, STASSI3
5 - STASSI3, ASI, BDI

I need a way to count the number of times that each of those records in my assessments table exists in my log table

ASI = 3
BDI = 4
BSSI = 2
STASSI3 = 3
STAXI = 2

Any suggestions?

Thanks so much for your help! Please let me know if this isn't clear!

theAybaras
May 24 '07 #1
Share this Question
Share on Google+
8 Replies

NeoPa
Expert Mod 15k+
P: 31,770
You've made life difficult for yourself here with the multiple columns (Normalisation and Table structures may explain that better).
What you need in your current situation is to have a Count(). No, even that won't work without getting very complicated and inefficient. You need to re-organise your table structure as recommended in the link.
May 25 '07 #2

theaybaras
P: 52
You've made life difficult for yourself here with the multiple columns (Normalisation and Table structures may explain that better).
What you need in your current situation is to have a Count(). No, even that won't work without getting very complicated and inefficient. You need to re-organise your table structure as recommended in the link.
I knew the multi column was a WRETCHED way to go... any suggestions on how to get all of the data in that I need and still be able to work with it, sort it, count individual parts of the data, etc.?

the Aybaras
May 25 '07 #3

NeoPa
Expert Mod 15k+
P: 31,770
If you can post your Table MetaData in here for me exactly as it is now, then I can look at redoing it for you.
Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Please try to use the same layout. If you reply to this post you'll see the tags used etc.
May 26 '07 #4

theaybaras
P: 52
C_ass01 is the first field of assessments... I knew this was a problem before I started with this table, but I had no idea how to do it otherwise.There are only going to the five disabilities that we need to note, and they don't go together at all... so I don't think these are an issue like the assessments are, but they could probably be fixed too... for all of the tables that are looked up, there is an autonumber primary key, and one string field. like
G_Key; Autonumber; PK
G_Type; String (values are Male, Female, Transgendered,... ... Other)

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
  20. C_ass01; Numeric; (looks up tblAssessments)
  21. C_ass02; Numeric; (looks up tblAssessments)
  22. C_ass03; Numeric; (looks up tblAssessments)
  23. C_ass04; Numeric; (looks up tblAssessments)
  24. C_ass05; Numeric; (looks up tblAssessments)
  25. C_ass06; Numeric; (looks up tblAssessments)
  26. C_ass07; Numeric; (looks up tblAssessments)
  27. C_ass08; Numeric; (looks up tblAssessments)
  28. C_ass09; Numeric; (looks up tblAssessments)
  29. C_ass10; Numeric; (looks up tblAssessments)
  30. C_ass11; Numeric; (looks up tblAssessments)
  31. C_ass12; Numeric; (looks up tblAssessments)
  32. C_ass13; Numeric; (looks up tblAssessments)
  33. C_ass14; Numeric; (looks up tblAssessments)
  34. C_ass15; Numeric; (looks up tblAssessments)
  35. C_ass16; Numeric; (looks up tblAssessments)
  36. C_ass17; Numeric; (looks up tblAssessments)
  37. C_ass18; Numeric; (looks up tblAssessments)
  38. C_ass19; Numeric; (looks up tblAssessments)
  39. C_ass20; Numeric; (looks up tblAssessments)
  40. C_notes; Memo
Thanks again, SO MUCH!
May 26 '07 #5

NeoPa
Expert Mod 15k+
P: 31,770
OK.
Firstly, congratulations on being one of the very few who can follow the instructions for the MetaData first time :)
Next, can you confirm for me that the C_Client text field (as well as the PK obviously) is a unique identifier for the record?
May 26 '07 #6

NeoPa
Expert Mod 15k+
P: 31,770
Actually, I don't need that really.
You should strip C_Ass01 thru C_ass20 from your tblClientLog table and add a new table tblClientAssessment (or tblClientAss) with the following structure :
Table Name=tblClientAssessment
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. CA_Key; AutoNumber; PK
  3. CA_C_Key; Numeric; FK (tblClientLog)
  4. CA_Assessment; Numeric
Optionally, you can add a CA_Order field to specify which order the [CA_Assessment]s come out in.
May 26 '07 #7

theaybaras
P: 52
Actually, I don't need that really.
You should strip C_Ass01 thru C_ass20 from your tblClientLog table and add a new table tblClientAssessment (or tblClientAss) with the following structure :
Table Name=tblClientAssessment
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. CA_Key; AutoNumber; PK
  3. CA_C_Key; Numeric; FK (tblClientLog)
  4. CA_Assessment; Numeric
Optionally, you can add a CA_Order field to specify which order the [CA_Assessment]s come out in.
So then I should just incorporate as a subform in the other table? That's easier than I thought! Also, C_Key is unique, name is a deidentified identification based on the location of the interaction... so it will be like V25J47, this will be unique 99.999999999999% of the time, but cannot be put as a no duplicates field.

Thanks a million! You've been so much help!! This will make my life SOOOO much easier! Oh, and thanks for the compliment on the metadata! I'm glad I got you the right thing! I'm a super-newbie here, and with Access... so I'm really happy I'm not a total forum spaz!

:) Thanks again, and always!

theAybaras
May 27 '07 #8

NeoPa
Expert Mod 15k+
P: 31,770
No problem. I'm glad it helped.
I would have struggled myself with coming up with the subform idea, as I almost never get to use them in real life. I only ever play around with them to answer questions in here :D
May 27 '07 #9

Post your reply

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