Strange query behavior

[KACJR]

I come again to the well of knowledge...

I have an anomaly happening with a query that is functioning properly except for one record.

I have a table with a list of supervisors and a second table with a list of staff. Each staff record contains a supervisor code that refers back to the list of supervisors.

As part of the startup steps in my front end, I update the supervisor's table with a count of how many staff each supervisor has, sine a supervisor's direct reports can change daily. It works fine for all supervisors except one which gets calculated as zero, even though I know that the supervisor has 17 staff reporting to her.

Here's the query I run:

UPDATE tblPeopleStaffSupervisors SET tblPeopleStaffSupervisors.STAFFCOUNT = DCount('SUPERVISORCODE_I','tblStaff','SUPERVISORCODE_I=""' & [tblPeopleStaffSupervisors].[SUPERVISORCODE_I] & '""'

This updates the staff count for each supervisor in the supervisor table (tblPeopleStaffSupervisors) based on the count of records in the staff table (tblStaff) where the staff person's supervisor code matches the supervisor code in the supervisors table.

I have looked for anomalies in the spelling of the supervisor code versus what's in the staff table and they appear to be a perfect match.

It's baffling because it works for 81 other supervisors but not for this one.

I'm looking for clues as to why this is happening.

Thanks,
Ken

 

[Gasman]

And what does a supervisor code look like?

 

[tvanstiphout]

In the immediate window (Ctrl+G) execute this:
?DCount("SUPERVISORCODE_I", "tblStaff", "SUPERVISORCODE_I='HER-CODE'")
If it returns 0, "HER-CODE" is not her code. Maybe it is "HER-CODE " or "HER-CODE" followed by Cr or Lf or CrLf.

You could:
?"X" & DLookup(...) & "Y"
to print her code.

 

[KACJR]

And what does a supervisor code look like?

Six character alpha. In this example, the code is "CONWAY".

 

[GPGeorge]

I come again to the well of knowledge...

I have an anomaly happening with a query that is functioning properly except for one record.

I have a table with a list of supervisors and a second table with a list of staff. Each staff record contains a supervisor code that refers back to the list of supervisors.

As part of the startup steps in my front end, I update the supervisor's table with a count of how many staff each supervisor has, sine a supervisor's direct reports can change daily. It works fine for all supervisors except one which gets calculated as zero, even though I know that the supervisor has 17 staff reporting to her.

Here's the query I run:

UPDATE tblPeopleStaffSupervisors SET tblPeopleStaffSupervisors.STAFFCOUNT = DCount('SUPERVISORCODE_I','tblStaff','SUPERVISORCODE_I=""' & [tblPeopleStaffSupervisors].[SUPERVISORCODE_I] & '""'

This updates the staff count for each supervisor in the supervisor table (tblPeopleStaffSupervisors) based on the count of records in the staff table (tblStaff) where the staff person's supervisor code matches the supervisor code in the supervisors table.

I have looked for anomalies in the spelling of the supervisor code versus what's in the staff table and they appear to be a perfect match.

It's baffling because it works for 81 other supervisors but not for this one.

I'm looking for clues as to why this is happening.

Thanks,
Ken

Can you share the data so we can try to identify potential problems?

Also, the description of the fields leads to the conclusion that there is a problem in the way supervisor codes are defined. We need more detail about that.

Also, storing the calculated value is a less than ideal approach and should be reconsidered anyway.

You should be able to create the current counts using an aggregate query as needed.

There is yet another possible design issue with having separate tables for Staff and Supervisors, but that is lower priority that sorting out this calcluation.

 

[KACJR]

Can you share the data so we can try to identify potential problems?

Also, the description of the fields leads to the conclusion that there is a problem in the way supervisor codes are defined. We need more detail about that.

Also, storing the calculated value is a less than ideal approach and should be reconsidered anyway.

You should be able to create the current counts using an aggregate query as needed.

There is yet another possible design issue with having separate tables for Staff and Supervisors, but that is lower priority that sorting out this calcluation.

I can as long as the data remains secure and private. It has employee data, obviously.

 

[tvanstiphout]

I would suggest to hold off for now. Let's see if my suggestions in response #3 reveal the issue.
If you have to upload, make a copy of the database and only include the tables and fields necessary for this issue. Sensitive data should be anonymized, e.g. FirstName1 rather than Suzy.

 

[Gasman]

Is Conway the one that does not update?

You can use this to obfuscate the data.
Do it on a copy of the DB. We only need enough to see the issue.

 

[plog]

You're asking us how to use duct tape to fix the wood putty that you used to fix your cracked foundation. Actually, that's not a great analogy because your root issue isn't as bad as a cracked foundation. It's actually really trivial to fix.

Don't store this value, calculate it. You make one query and reference that query when you want the total staff of each supervisor:

SELECT SUPERVISORCODE_I, COUNT('SUPERVISORCODE_I) AS StaffCount
FROM tblStaff
GROUP BY SUPERVISORCODE_I

It's actually less work than what you are doing now.

Also, 10-1 odds you're SUPERVISORCODE_I isn't what it appears. It has a blank space somewhere, perhaps a return character, maybe the letter O is a 0. You're eyes are deceiving you.

 

[Ken Sheridan]

Why are you storing the staff count per supervisor, rather than computing it with a simple query like this:

SELECT
    tblPeopleStaffSupervisors.SuperVisorCode_I,
    tblPeopleStaffSupervisors.FirstName,
    tblPeopleStaffSupervisors.LastName,
    COUNT(*) AS StaffCount
FROM
    tblPeopleStaffSupervisors INNER JOIN tblStaff
ON
    tblPeopleStaffSupervisors.SuperVisorCode_I = tblStaff.SuperVisorCode_I
GROUP BY
    tblPeopleStaffSupervisors.SuperVisorCode_I, tblPeopleStaffSupervisors.FirstName, tblPeopleStaffSupervisors.LastName;

This is just a slightly extended version of Plog's reply of course, in this case returning each supervisor's name rather than just their code.

 

[KACJR]

I would suggest to hold off for now. Let's see if my suggestions in response #3 reveal the issue.
If you have to upload, make a copy of the database and only include the tables and fields necessary for this issue. Sensitive data should be anonymized, e.g. FirstName1 rather than Suzy.

I will try that first.

Why are you storing the staff count per supervisor, rather than computing it with a simple query like this:

SELECT
    tblPeopleStaffSupervisors.SuperVisorCode_I,
    tblPeopleStaffSupervisors.FirstName,
    tblPeopleStaffSupervisors.LastName,
    COUNT(*) AS StaffCount
FROM
    tblPeopleStaffSupervisors INNER JOIN tblStaff
ON
    tblPeopleStaffSupervisors.SuperVisorCode_I = tblStaff.SuperVisorCode_I
GROUP BY
    tblPeopleStaffSupervisors.SuperVisorCode_I, tblPeopleStaffSupervisors.FirstName, tblPeopleStaffSupervisors.LastName;

This is just a slightly extended version of Plog's reply of course, in this case returning each supervisor's name rather than just their code.

My SQL knowedge is rudimentary.

But this query did the trick. I now have a correct count for CONWAY. So thank you!

 

[Mark_]

For normalization, instead of saving "SUPERVISORCODE_I", you would normally save the AutoNumber ID for the record.

 

[Ken Sheridan]

For normalization, instead of saving "SUPERVISORCODE_I", you would normally save the AutoNumber ID for the record.

That's not a matter of normalization. SUPERVISORCODE_I is a candidate key, and it's perfectly legitimate to make it the primary key of the referenced table, and the foreign key of the referencing table. In some contexts there are advantages in using a 'natural' key rather than a 'surrogate' key. When correlating combo boxes in a form in continuous forms view, for instance, the use of natural keys avoids the need to use 'hybrid' controls. Simple combo boxes can be used, without the values in rows other than the current one being invisible due to the hidden bound column of the combo box being unable to map to a non-key column in the restricted RowSource.

Natural keys can also simplify queries, as there is no need to include the referenced table in a query.

Most of the time I will use surrogate keys, but where a natural key is advantageous I will use that. In some cases a surrogate key is necessary of course, e.g in a Cities table, as city names can legitimately be duplicated. Even here, a composite 'natural' key could be used, e.g one made up of City and County columns, but I can't think of any cogent reason for doing so.

Where a 'surrogate' key is used it is of course imperative that any other candidate keys be indexed uniquely. I often see tables which model a binary relationship type being given an autonumber key, but without the two foreign key columns (sometimes plus one or more other columns) being included in a unique index. The integrity of the database is prejudiced where this is the case.