Count Fields on a Form or SubForm

[Kat Q.]

I am attempting to track contacts in a database. I have multiple fields for
the same type of contact. For example, I have C1Type, C2Type, C3Type, etc, up
to say 80 fields. The "Type" fields have a drop down where you can pick the
type of contact, such as "Family", "Individual", or "Group".

I need to have a field on my form that counts the "Type" fields (C1Type all
the way through C80Type). I'll have a separate field that counts how many
times "Family" is selected, how many times "Individual" is selected, and so
forth.

I know how to do this in Excel, but can't figure it out in Access. I know
the code that will found the field and add it up, but the code I'm using, I'd
have to put all the CxType fields in indidually, and that makes the code too
long. Is there an easier way to make it count certain fields (like using a
wild card?)?

Also, I actually have to go up to 150, and therefore need to separate my
contacts into 2 tables. Can you count across different tables, or would I be
better off counting on each table, and then having a separate table add those
2 things together?

Thanks to anyone who can assist... I have a relative knowledge of Access,
but by no means do I understand all of the coding. Thank you,

 

[Dale Fye]

Kat,

Access is a relational database, not a spreadsheet. If you are trying to
create an Access application, then I suggest that you normalize your tables.
Generally, if you have fields that repeat themeselves with numerals to
identify the occurence of the field, this is a dead giveaway that your data
is not normalized.

You need to create another table (tbl_ContactTypes) that contains ContactID
and Contact_Type fields. You can fill this table in by creating a subform on
your main form and relating it to the main forms data by the ContactID field.
The advantages of this are:
1. You are not limited to the 80 contact types that you show in your
example. You can have as many (or as few) as you want.
2. It is extremely easy to "count" the number of Contact types using an
aggregation (Group By) query.

HTH
Dale

 

[Kat Q.]

Thank you for your response. I thought of this, but my question would be -
Can I have multiple contacts for 1 individual, where the subform shows the
contacts for that individual when you're on that matching record?

For example, PK1, I need to give this person 50 contacts (w/3 different
criteria).... and then if I go to PK2, I can give them 10 contacts, and it
associates those 10 contacts to PK2 specifically? Could I do it by matching
by a number that is not an autonumber (like a general ID number they input
themselves), so that it matches up?

Thank you,

 

[Kat Q.]

Sorry, follow up....

Ok, so I made the new table as suggested, with just the ID Number, an auto
ID, CType, CDate, and CTime.... So if I leave the record selector on the
subform, the person can scroll and add multiple contacts, and it associates
it with the ID Number (I had to use the ID Number, since the auto ID can only
have 1 association)...

If that tracks the contacts on the form, how would I create a Query to count
the different types of contact Types for a specific ID.... in other words, I
want that sum (total number of Family contacts, total number of Individual
contacts, etc), put on the form, so although you can't see all the contacts
spread out, it'll give you a running total.... in addition, I want to count
the sum of the time entered... but I would assume the process would be the
same, if I can figure out the contact types....

I can see in the Table, when I enter 3 contacts for ID 6534, that it simply
enters those in the table... but how would I make it count contact types for
specific IDs on a form?

Thanks...