Count of a Value in Filtered Records

[KiwiKim25]

Thanks to help in this forum, I'm almost done creating this database! I'm so excited!

I've hit one last snag. Calculations/scripts are my downfall. Here is my dilemma:

I have a "Main table". On it is a portal to my "Social Table". I am assigning each record a value of either "Intro" "Single" or "Paired" (which is in a "Social Status" field).

Here's where I'm stuck.

I need to do a count of how many records have the "Intro" value, "Single" value or "Paired" value, AND I need it to only count the records that also have the field/value "ACTIVE" selected (since we don't want to count non-active records).

I made a field for the calculation, and have tried a variety of calculations, but am hitting the brick wall of my inability to do more than "Table::this = Table::that".

Thoughts? Formulas/calculations/scripts to try?

Thank you!!!!

[comment]

What is the "Main table" (what does a record in this table represent) and what is its relationship to "Social Table"?

we don't want to count non-active records

Do we want them to show in the portal?

[KiwiKim25]

Sorry! I knew I was missing some info!

The "Main Table" records are by the field "ID Number", like medical records.

The "Active" field is a part of the "Main Table" (Main Table::Active).

The "Main Table" and "Social Table" are related "ID Number = ID Number".

My portal to the "Social Table" has the field for "social status", and hopefully below it a summary/count of all active records for each of the "Intro" "Single" and "Paired" values.

I have the database set to sort on opening of "Main Table" to have the "Active" records shown first (and it works too!). We still need to access the non-active records from time to time but want to see the active ones first, so this works well for us.

[comment]

I am afraid this is still not clear enough. Let me suggest you follow the good practice of naming your tables by what does a record stand for. For example, a table of Patients (each record is a patient) or a table of Doctors (each record is... that's right).

That way, when you say 'I have a table of Doctors and a table of Patients, related by DoctorID' it is clear that one doctor has many patients and each patient has one doctor.

[KiwiKim25]

The main table is a table of patients, and each record is a patient. Each patient has one ID number that is unique to them.

The social table is a table of social information; one social history per patient/record (by ID number). The two tables are related directly by ID Number.

The data I want to count/summarize is the intro/single/paired value, which is a single value per patient. There is other data on that table that is multiple per patient, but I don't need that counted up.

Sorry I'm still figuring out how to explain the set up; Thank you for your patience with me!

[comment]

The data I want to count/summarize is the intro/single/paired value, which is a single value per patient.

Why isn't this value in the Patients table, then? Anything that "is a single value per patient" is an attribute of patient, and thus should be a field in the Patients table. I am also confused what does your portal show - since it appears there is only one related record in the "social table".

[KiwiKim25]

There is a lot of other data in the portal. Sorry to keep being so vague about the rest of the info, but it's a work-restriction thing.

After a lot of trial and error, it ended up working best for all involved to use the 2nd table.

The program has been approved, works very well now and is in use.

I just need to figure out this running tally.

Thanks again for your help!

[comment]

Sorry to keep being so vague about the rest of the info, but it's a work-restriction thing.

Why don't you post a mock-up file showing the problem using generic data. I sure cannot figure out what you have there.

[grumbachr]

While probably not the cleanest way. (Hopefully I'm understanding clearly also)

In the Social Table create a global or calc field with a value that matches your Active Status. ( isAcitve="Active" )

In your Main Table create global or calc fields with a value that matches your field contents ( isIntro, isSingle, isPaired ) three fields total as you describe.

Create three table occurrences in the relationship graph

Status = isActive and isIntro=SocialField

Then you can do your Count Calc fields based on those relationships.

There is mostly likely a must cleaner way to do this but as I unless i missed something this will get your numbers.