Thanks for your help and sorry to be dumb, but I'm not sure how to select a
field even when it's null. At the moment my query will list all appraisals
that have taken place for each year (eg 2001/2, 2002/3, 2003/4 etc) but, if
none took place in a particular year it isn't selected, so the report skips
to the next year. I want the report to show a blank field for that year.
b) In the same query, for those people who were hired after 2001 there will
not be any appraisals before they arrived and I only want to show those years
since their arrival, whether or not they're null.
My current query is:
SELECT tblConsultants.*, tblConsultants.MainSite,
tblConsultants.ConsultantStatus, [Surname] & ", " & [Title] & " " &
[Initials] AS Name, tblAppraisals.AppraisalDate,
tblAppraisers.AppraiserSurname, tblAppraisers.AppraiserInitials,
tblAppraisers.AppraiserTitle, [AppraiserTitle] & " " &
Trim([AppraiserInitials]) & " " & [AppraiserSurname] AS AppName,
tblAppraisals.AppraisalYr, tblAppraisals.FormsReceived
FROM (tblAppraisals INNER JOIN tblAppraisers ON tblAppraisals.AppraiserID =
tblAppraisers.AppraiserID) INNER JOIN tblConsultants ON
tblAppraisals.ConsultantID = tblConsultants.ConsultantID
WHERE (((tblConsultants.ConsultantStatus)="Current"))
ORDER BY tblConsultants.MainSite, [Surname] & ", " & [Title] & " " &
[Initials];
Thanks again for your help.
Sandy
NetworkTrade said:
ensure that a) blank
records are printed even when, for example, no appraisal has taken place one
year
***blank 'fields' are fine but blank 'records' are somewhat a contradiction
: as long as you have search-able field in all records of these tables you
should have no problem with some or many blank fields
b) for more recently appointed employees, how can I only print records
(including blanks) for years since they started?
*** well more than one way to skin the cat but ultimately you need their
hire date in their record ; then your query can have a criteria that this
date field as being >0 or maybe that current year is > than hire year
.....or something along these lines
--
NTC
:
I have designed a report printing out appraisal dates for employees by year
and appraiser in the form of a table. Some employees have been in the system
for some years and others only started recently.
The report is based on a query from 3 tables. How can I ensure that a) blank
records are printed even when, for example, no appraisal has taken place one
year b) for more recently appointed employees, how can I only print records
(including blanks) for years since they started?
Any help greatly appreciated.
Sandy