First and foremost, I hope you have tables set up for
each of the "constant" type of values. You should have
tables for region, office (office should have a region
ID) so you would only have to enter an office in your
table and you would know (through relational database
structure) anything you wished to know about a region
(given an office does not belong to more than one region,
but that too could be made to work), Risk, AuditType and
Department. I would even make a table to input Contract
Numbers and then relate the contract number ID. This
will keep typing of names and values to a minimum and
allow users to select from values "administrators" want
them to select from. I dont know what the "lookup" table
is about since it replicates the data you already have
stored in the All Audits table. Maybe I am not
understanding the jist of this thing.
With the table structure you sent me, a simple Query will
display both Performance and Finincial Audits in one
query while searching for Audit Dates that are "NULL"
Here is the query:
SELECT tbl_AllAudits.*
FROM tbl_AllAudits
WHERE (((tbl_AllAudits.audittype)="Performance") AND
((tbl_AllAudits.AuditDate) Is Null)) OR
(((tbl_AllAudits.audittype)="Financial") AND
((tbl_AllAudits.AuditDate) Is Null));
You can add a ORDER BY "fieldname" "DESC" (or blank for
ascend) to have the records sorted as you wished. You
may want them grouped by Office or by Audit type or by
both. Here is an example:
ORDER BY tbl_AllAudits.audittype, tbl_AllAudits.office;
This would first sort them by type and them sort them by
office.
You could also use a UNION Query, which is a joining of
two separate queries that give the same fields. This is
a bit more complicated in writing, but accomplishes the
very same thing. It breaks up the WHERE clause in the
above query and places them into their own SELECT
statements. This type of query does not really have to
be used for this case, but I thought I would include it
because there may be a need for something like this in
your future.
SELECT tbl_AllAudits.*
FROM tbl_AllAudits
WHERE (((tbl_AllAudits.audittype)="Performance") AND
((tbl_AllAudits.AuditDate) Is Null))
UNION SELECT tbl_AllAudits.*
FROM tbl_AllAudits
WHERE (((tbl_AllAudits.audittype)="Financial") AND
((tbl_AllAudits.AuditDate) Is Null));
I hope this helped. You could make a report based on
this query. Use the wizard and have the report Group
based on the different fields to allow you an easy view.
Probably group on type and on Office.
AS I read back through your original question, I am not
sure that your questions correspond to one another. If
you still need help on making sure there are not
duplicate entries, let me know. I still think you need
to restructure your tables and that would give you
assurity of no duplicates.
Drew
-----Original Message-----
Thanks for your help again Drew.
OK, this is how it goes:
I have a table which holds data on audits which have been carried out on
different offices. (I do not have any unique / primary keys keys set as
yet.)
There are 2 'types' of audit, 'Financial' and 'Performance' - the former
will have an associated 'contract' number (it is the contract that is
audited), the latter is recorded by the 'dept' whose performance has been
audited (it is the department that is audited).
The table structure is as follows:
tbl_AllAudits (region, office, contract, dept, auditType, risk, fundsAtRisk,
auditDate)
eg for a 'Financial' audit
("North", "Newcastle", "NTN001401", Null,
