DCount + Query + Multi-Column

[theaybaras]

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

[NeoPa]

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.

[theaybaras]

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

[NeoPa]

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

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Please try to use the same layout. If you reply to this post you'll see the tags used etc.

[theaybaras]

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

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
C_ass01; Numeric; (looks up tblAssessments)
C_ass02; Numeric; (looks up tblAssessments)
C_ass03; Numeric; (looks up tblAssessments)
C_ass04; Numeric; (looks up tblAssessments)
C_ass05; Numeric; (looks up tblAssessments)
C_ass06; Numeric; (looks up tblAssessments)
C_ass07; Numeric; (looks up tblAssessments)
C_ass08; Numeric; (looks up tblAssessments)
C_ass09; Numeric; (looks up tblAssessments)
C_ass10; Numeric; (looks up tblAssessments)
C_ass11; Numeric; (looks up tblAssessments)
C_ass12; Numeric; (looks up tblAssessments)
C_ass13; Numeric; (looks up tblAssessments)
C_ass14; Numeric; (looks up tblAssessments)
C_ass15; Numeric; (looks up tblAssessments)
C_ass16; Numeric; (looks up tblAssessments)
C_ass17; Numeric; (looks up tblAssessments)
C_ass18; Numeric; (looks up tblAssessments)
C_ass19; Numeric; (looks up tblAssessments)
C_ass20; Numeric; (looks up tblAssessments)
C_notes; Memo

Thanks again, SO MUCH!

[NeoPa]

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?

[NeoPa]

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

Field; Type; IndexInfo
CA_Key; AutoNumber; PK
CA_C_Key; Numeric; FK (tblClientLog)
CA_Assessment; Numeric

Optionally, you can add a CA_Order field to specify which order the [CA_Assessment]s come out in.

[theaybaras]

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

Field; Type; IndexInfo
CA_Key; AutoNumber; PK
CA_C_Key; Numeric; FK (tblClientLog)
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

[NeoPa]

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