Stuart:
1. You should be able to use conditional formatting for this, which will
enable you to change the ForeColor and/or BackColor of the control on the
basis of an expression which adds the duration of a qualification to the
qualification date and compares the result with the current date. Say you
have a field DateAchieved and field Duration, the latter being the time a
qualification is valid from the date achieved. Lets assume the latter is in
months for this example. The expression would be:
DateAdd("m",[Duration],[DateAchieved]) <= Date()
2. For this you can first create a query which is restricted to
qualifications which are still valid. This does depend on the structure of
the database being correct, however. This would mean having a table
Volunteers say, a table Qualifications and table, VolunteerQualifications say,
which models the many-to-many relationship between them, so the tables would
be along these lines:
Volunteers
….VolunteerID (primary key)
….Firstname
….LastName
Qualifications
….QualificationID (primary key)
….Qualification
….Duration
VolunteerQualifications
….VolunteerID
….QualificationID
….DateAchieved
The primary key of this table is a composite one of all three columns (what's
known as 'all key').
The query to return each volunteer's currently valid qualifications would be
like this:
SELECT Volunteers.VolunteerID, Qualification, DateAchieved
FROM Volunteers, VolunteerQualifications AS VQ1, Qualifications
WHERE VQ1.VolunteerID = Volunteers.VolunteerID
AND VQ1.QualificationID = Qualifications.QualificationID
AND DATEADD("m",Duration, DateAchieved) <= DATE()
AND VQ1.DateAchieved =
(SELECT MAX(DateAchieved)
FROM VolunteerQualifications AS VQ2
WHERE VQ2.VolunteerID = VQ1.VolunteerID
AND VQ2.QualificationID = VQ1.QualificationID);
Lets call this query qryVolunteerCurrentQualifications. The way it works is
that the outer query joins the three tables (which I've done in the WHERE
clause) and returns the rows returned per VolunteerID where:
1. The qualification is currently still valid, using the same expression as
for the conditional formatting.
2. The date achieved is the latest date at which the volunteer has qualified
for the qualification in question. This is done by comparing the
DateAchieved value to the date value returned by a subquery which is
correlated with the outer query on the VolunteerID and QualificationID
columns, using the aliases VQ1 and VQ2 to differentiate between the two
instances of the VolunteerQualifications table. This subquery returns the
latest (MAX) DateAchieved value for the volunteer/qualification in each row
of the outer query, so restricts its results to the latest per volunteer per
qualification.
We now have to establish whether for a particular one of your three service
areas a volunteer has the necessary currently valid qualifications. For this
a table ServiceAreas say is needed (with just three rows) and a table
ServiceAreaQualifications say which models the many to many relationship
between ServiceAreas and the Qualifications table. So the tables would be
like this:
ServiceAreas
….ServiceAreaID (primary key)
….ServiceArea
ServiceAreaQualifications
….ServiceAreaID
….QualificationID
Again this table is 'all key'.
To determine whether a volunteer has the requite qualifications for each
service area we can create another query. This time we can use the
qryVolunteerCurrentQualifications query created above as the starting point.
By joining this to the ServiceAreaQualifications table on QualificationID we
can establish which current qualifications each volunteer has for each
service area:
SELECT VolunteerID,
ServiceAreaQualifications.ServiceAreaID, ServiceArea,
ServiceAreaQualifications.QualificationID, Qualification
FROM ServiceAreas, ServiceAreaQualifications,
qryVolunteerCurrentQualifications
WHERE ServiceAreaQualifications.ServiceAreaID =
ServiceAreas.ServiceAreaID
AND ServiceAreaQualifications.QualificationID =
qryVolunteerCurrentQualifications.QualificationID;
Lets call this query qryVounteerServiceAreaQualifications.
All that's left to do now is to determine whether a volunteer has all the
current qualifications required for a service area. This can be done simply
by comparing a cont of the volunteer's qualifications per service area with
the count of the total number of qualifications required per service area:
SELECT VolunteerID, ServiceAreaID, ServiceArea
COUNT(*) AS QualificationCount
FROM qryVounteerServiceAreaQualifications
GROUP BY VolunteerID, ServiceAreaID, ServiceArea
HAVING COUNT(*) =
(SELECT COUNT(*)
FROM ServiceAreaQualifications
WHERE ServiceAreaQualifications.ServiceAreaID =
qryVounteerServiceAreaQualifications.ServiceAreaID);
Lets call this query this query qryQualifiedVolunteers.
It would be possible to combine some of the steps above and have fewer
queries, but these would then be more complex. Also each of the above
queries probably has potential uses individually as well as being a step in
determining if a volunteer is currently qualified for a particular service
area.
In any form or report which includes the VolunteerID and ServiceAreaID fields,
therefore, you could have a check box control which shows if the volunteer is
currently qualified for the service area by using the following expression as
it ControlSource property:
= Not IsNull(DLookup("VolunteerID", "qryQualifiedVolunteers", "VounteerID = "
& [VolunteerID] & " And ServiceAreaID = " & [ServiceAreaID]))
I must stress two things:
1. All of the above is written off the top of my head, so is untested, and
might well need some debugging.
2. It does rely on the database's underlying structure in terms of its
tables and the relationships between them ( its 'logical model') being
appropriately designed. Unfortunately this is not always the case with this
sort data as there is a great temptation for inexperienced users to design a
database table much as they would a spreadsheet, with separate columns for
the separate qualifications. This is known as 'encoding data as column
headings' or more colloquially as 'committing spreadsheet'. A fundamental
principle of the database relational model (the Information Principle) is
that data is stored as values at row positions in tables and in no other way.
Where this has been done its strongly advised that the database be recast
into a correct model. This is actually not as difficult as might be thought
as its really a question of firstly designing the necessary tables, and then
filling them in a specific order with a series of append and update queries.
This is tedious, but usually fairly straightforward.
I've given you a lot to digest, but when you've done so don't hesitate to
post back if you need further help.
Ken Sheridan
Stafford, England
I work as a volunteer for the British Red Cross, I am currently working on
developing a Public Events Database for my local office, I have started off
using a base that another volunteer built for a different office but it needs
to be customised to the needs of the office i am working for, I have done a
bit of customiation on it already using my existing skills in regards to
access but there are a few things i want to do with the database which i do
not know how to do just yet which i will list below.
1. in the Volunteers Form theres a list of 23 certificates a volunteer can
have acheived and you enter the date that the certificate was acheived in the
corresponding field for that qualification, now then these qualifications
last for so long then expire so my first question which is one of conditional
formatting is how to i set up conditional formating so that if the difference
between the date and the field and the date the information is being viewing
"=TODAY( )" is in excess of the period for which the qualification is valid
then i want the field in the form to be formated in a certain way so as to
alert the user reading it that the volunteer needs to redo the qualification
to be able to continue volunteering in there area of expertise.
2. at British Red Cross we have 3 main areas these are Emergency Response,
Event First Aid & Fire and Emergency Support Service (FESS) now anyone
volunteering for one of these sections must acheive a minimum set of core
competences (qualifications) in order to work in that area, so if the
database shows that the volunteer has acheived all the qualifications for a
specific area and all of those qualifications are up to date as per the rules
stated in query number 1 then its to tick the checkbox for the field that
covers that area so we know what areas that volunteer is qualified to work in.
i know these are pretty complex requests but any help that anyone can give
me in relation to this would be greatly appreciated.
--
Message posted via AccessMonster.com
.
