Counting how many fields have a specific value (1 Viewer)

[fat controller]

I have 7 combos, which for the most part set the value in another field - however, there are two values in the combo that do not have a value attached - one is AH, the other is SK. I have two textboxes (one for AH, one for SK) that I wish to have a count (preferably running) of how many times each of these values appears in the seven combo boxes.

Is this do-able?

 

[burrina]

Here is an Example:

SomeField = DCount("[AH]", "YourTableNameHere")

 

[fat controller]

I need it to be a bit more specific than that - potentially, in any one record there are up to 28 fields that may contain AH, and I need to break those down into four groups of seven showing how many fields within the current record show AH (specifying which seven fields to look at for each count)

 

[spikepl]

If you are looking for which field contains a given value, this means that you have multiple fields holding the same type of information and that, in 90 % of the cases, is a normalizaion error. Show a screenshot of your relations window and exaplain what this is about.

 

[fat controller]

There is no relations to show in this case. It is a declaration of work for 28 days - in place of a duty number, on each given day an employee can select AH or SK (Holiday or Sick), and if they appear I need to count them in seven day blocks.

The payment differential is simply looked up from another table, multiplied by the number of days to give a total pay adjustment; none of these values are stored.

To give a simplifed example, a user will input their duty number or SK/AH, so for a week, the record could potentially show:

(FieldName - Data)

SatDuty - 22
SunDuty - 21
MonDuty - 22
TueDuty - AH
WedDuty - AH
ThurDuty - Rest
FriDuty - Rest

So, for the total number of days AH I would need the count to show 2 for this week

Only the duty number is held - the duty information (start time, end time, shift allowance payment etc) are looked up from the duties table.

I did wonder if I could handle this by an after update event on the combo where the duty is selected, to add '1' to the week value in the totals field if AH was the value, but how would I then remove that if someone changed AH to a duty number (if they are called back from a holiday etc)?

 

[fat controller]

Cracked it.

On the After Update event on the combo, if the value is AH or SK, I have it set to +1 to either the AHTotals or the SKTotals as appropriate, and then lock the combo. The On Got Focus for the combo, if the value is AH or SK, I have it so that it -1 from the appropriate totals field, unlocks the combo and sets its value null. Works just dandy