Strange query behavior (1 Viewer)

[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!