Criteria problems

[melvis]

Hi guys,

I have been given a table that was produced in Excel that I have imported into Access, it is a list of service users that have received an assessment from us.

There are 11 different assessments and not all users have had one of each. For example, one user has 4 different assessments and they are listed on the excel table 4 separate times (one for each different assessment).

What I am trying to do is find out who has not had a particular assessment.

Because Joe Bloggs has had 4 assessments (including this particular one), the query is returning his records for all of the assessments he has had, minus the particular assessment.

What I want is for the query to exclude him totally from the results, so that all I have left, is a list of people who have not had this particular assessment.

Hope this makes sense.

Any help would be greatly appreciated.

 

[Fizzio]

This depends on the data structure. After importing, how is the table structured and which field(s) are the asessments stored ie is there a field for each assessment?

 

[melvis]

Unfortunately, there is only one field for assessments and this contains all the different types.

It is set out like so:

ID (Primary key) - Name - Surname - Assessment

So if Joe Bloggs has had for different assessments, then he is listed 4 times, but the assessment details are different.

Any thoughts??

 

[Jon K]

Try this Totals query (type/paste in the SQL View of a new query, replacing with the correct table name):-

SELECT Name, Surname
FROM [tableName]
GROUP BY Name, Surname
HAVING Sum(IIf([Assessment]=[Enter a particular assessment],1,0))=0;


When the query is run, the user is asked to enter a particular assessment type. The query will then return the names of those who do not have the assessment entered.

You can also hardcode the assessment type in the query, e.g.
.... IIf([Assessment]="Assessment A",1,0)

 

[melvis]

Thanks Jon K

Worked a treat.

 

[melvis]

Jon K - Thanks for the advice, even the admin staff can do it - well chuffed.

However, I just thought about updating the table.

The table will need to updated every now and then with a more accurate list of users. Is there anyway I can update this table and still use the queries and reports I have set up already.

This is probably really easy but my mind has gone blank.

Any thoughts?

Cheers.

 

[EMP]

The query results returned by a Totals query is not updatable.

You can use the Form Wizard to create a form based on the table. The Wizard will add navigation buttons on the form for you to move to the first, last, next and previous records. It also has a button for adding a new record.

You can also customize the form by adding your own Search functionality. There are a lot of good stuff in these forums about adding search functionality to a form. Just click on the Search Button and type some words to search.