Query to count blank fields - Help for Homeless Shelter

[lk12]

Hi,

I am an admin volunteer in a homeless shelter and I’m in the process of creating an Access system for the office, I just need help with one final bit. Currently there is a table called Outreach, which stores all the records detailing staff encounters with people on the streets. Two of the columns in the table are named Staff_Driver and Staff_Passenger which record who is driving the outreach van and who the passenger is. These two columns are combo boxes which are populated from a table named Staff_Members, which contains all staff member names under the column Staff_Member (along with ID). Any name can appear as driver or passenger in a record, but obviously not in the same record.

What I am trying to get is a query to count how many times a staff name appears in either the Staff_Driver or Staff_Passenger column in a record in the Outreach table which contains a blank field in any of the other columns.
Eg:
Tom Smith 2
Joe Bloggs 3

The furthest I have managed to get is by using the following which returns a list of the staff names for both the Staff_Driver and Staff_Passenger columns which appear in a record that contain a blank field:

SELECT Outreach.[Staff Driver], Outreach.[Staff Passenger]
FROM Outreach
WHERE (((Outreach.Date) Is Null)) OR (((Outreach.Time) Is Null)) OR (((Outreach.Gender) Is Null)) OR (((Outreach.Nationality) Is Null)) OR (((Outreach.Location) Is Null)) OR (((Outreach.Type_of_Support) Is Null)) OR (((Outreach.Comment) Is Null))
GROUP BY Outreach.ID, Outreach.[Staff Driver], Outreach.[Staff Passenger], Outreach.Forename, Outreach.Surname;

Thanks very much,
Paul

 

[lk12]

Forgot to mention that the purpose of this is for staff training and to make staff aware of how much fields they are leaving blank, in order to try and reduce this and improve the quality of data. (I tried using the required property in the tables but soon realised that this was causing errors everywhere, as well as the fact that some fields are allowed to be blank).

 

[Alansidman]

Create a new query using the existing query as the record source. Add the table that contains the staff names used in the form combo box. Join the names from the table to the query.

Drag the name field from the table onto the grid.
In the grid, drag the staff driver and the staff passenger fields from the first query onto the grid.

Click on the Sigma to form an aggregate query. Change the Group by for the staff driver and staff passenger fields to Count. Run the query.

 

[lk12]

Thanks very much for your help Alan. I did as you said, however when I run the query I get the error: "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."
Any idea how to solve this?

 

[Alansidman]

Suggest you upload your db with dummy data, so that we can examine and analyze it and provide you with a viable solution.

I have no idea what the error message means. Sounds like you may have attempted an update or append query instead of an aggregate query.

 

[spikepl]

It seems to me you are trying to cure the symptoms instead of the disease. If you want to ensure quality of data, so that all required fields are filled in, then do not allow direct access to tables but use a form, and in that form run a check in the BeforeUpdate procedure of the form that all required data is present (i.e. validate your data) and if not then let the user know - and cancel the save.

 

[lk12]

Alan - It is definitely an aggregate query I am using, I have narrowed the problem down to the Staff_Driver column because when I remove the relationship with this column then there is no problem with the query and the Staff_Passenger column counts perfectly. I will have a look at it further and if I cannot find a solution I will post the database with test data.

spikepl - I understand exactly what you mean and did originally consider doing that (the input is currently through a form), but due to the amount of data that sometimes may be unknown about a homeless person, and sometimes the need to add new records to populate combo boxes in the table for things such as Type of Support and Staff Members (this can only be done in office, as outreach staff overused the addition of records), it seemed the most feasible solution was to do it the way I am doing it now and ensure that staff keep blanks to a minimum and to allow the staff member to add a note in the comment box.

Thanks.

 

[lk12]

I managed to get two seperate queries working - one with calculates the amount of blank fields each staff member has left in the Staff_Driver column, and another query that calculates how many blanks each staff member has left in the Staff_Passenger column. Can anybody suggest how i could combine these in a query or report to automatically calculate the total for each staff member?

Thanks

 

[Alansidman]

You could create a new query. Bring each of these queries into the new query. Join them on the name and bring the fields from each onto the grid as well as the names.

 

[Brianwarnock]

You don't need to drag all of the data into the grid of the third query , just the names from one of the queries then
Total blanks: qry1.countofblanks+qry2.countofblanks
For example

Brian