Query Conundrum (1 Viewer)

[marvelousme]

I have created a database for our club members. Whenever a member calls in to order something or ask a question, I have a table called Member Issues where the conversation is documented. I now want to audit these "member issues" to make sure that my representatives are doing a good job. I now have an Audit table that is joined to the Member Issues table so that every issue is able to have an audit. Here's my problem.....

How can I created a query to find all of the Member Issues that DO NOT have an audit yet?

My audit table consists of the following:
ID (primary key)
Member Issue ID (foreign key)
Accuracy (a Correct/Error combo box)
Audit Comments (memo field)
Audit Complete (a Yes/No checkbox)

I tried to query every Member Issue where the Audit Complete was "false" or "is null" but it doesn't return anything. I understand that technically there is no Audit record until I create one, so that may be why my query is failing. I just can't figure out how to find what I'm looking for. PLEASE HELP!!!!

 

[Uncle Gizmo]

You say:- >>>How can I created a query to find all of the Member Issues that DO NOT have an audit yet?<<<

Unless I'm missing something I think you are using the wrong term. My understanding of "audit" with regard to an MS Access database is recording the actions of a user as they are using the database. Therefore the statement "finding issues that do not have an audit yet" does not make sense to me. Please could you explain what you mean bearing in mind my comment here.

 

[marvelousme]

I'm sorry that I wasn't clear. When I say "audit", I mean that I want to personally review the data that was originally entered and check it for completeness and accuracy. So the original table "Member Issues" that is used by my representatives has fields for name, date, time, comments, and other details. I want to be able to go into each record and review the data entered and critique the original user of the Member Issues table--that is what my Audit table is for. Each issue will have an accompanying audit record. So each day, I want to go in by database, run a query for all Member Issue Records that do not have an accompanying Audit record. That will let me know which Member Issue Records need to be reviewed.

Does this help?

 

[Uncle Gizmo]

Assuming you are using a sub-form within your "Member Issues form" to house the fields where you want to enter the audit data, as you quite rightly say; until you enter data there won't be any data to query against. However as soon as you enter some data then a number "Member Issues ID" from the "Member Issues table" will be added to your field "Member Issue ID (foreign key)" in your Audit Table. So that tells you what to query:- You need to check for records where a member issue ID from the member issue table does not appear in the member issue ID of the audit table.

 

[marvelousme]

Brilliant! Thank you so much...I would have never come up with the answer on my own. So now I am stuck with getting the results from the query. I have the Member Issue ID from my Issues table and the Member Issue ID from my Audit table....when put "is null" under the criteria for my Audit table...I don't get any results.
I know I'm missing something but I can't put my finger on it.....:banghead:

 

[Uncle Gizmo]

Open the Query Wizard and then the "Unmatched Query Wizard" this wizard will help you create the query you need.

 

[marvelousme]

I didn't even know about that feature! Thank you SO MUCH for you help....you're a true lifesaver!

 

[Uncle Gizmo]

If you wanted to add a checklist so that you could easily check off items that you have inspected then you might find this YouTube playlist I created a while back useful:-

Automatically add a Checklist to MS Access: http://www.youtube.com/playlist?list=PLhf4YcS5AjdqzkQ5egOuO1myweo6hNgG2