Printing blank records

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

not knowing your table structure or query design I'm kind of flying blind and
maybe my advice is off base - so take with a grain of salt....

I scanned briefly the query code you selected....I do not see any reference
to date in it but I see the WHERE clause defined by the field
"Current"....this maybe what is determining what records are returning to you

You can always create a new query as an experiment.....if I have a simple 3
record table like this:

2004, Sandy, PeanutButter
2005, Sandy,
2006, Sandy, Jelly

Then I can search on dates &/or Sandy and the search will return all three
records....with your food choice in the second being blank....

I'm not searching for a null....am searching on the other two fields....

or this table
2003, NTC
2004, Sandy
, Sandy
2005, Sandy
2005, NTC

And a search on all 'Sandy' as the Criteria in the query design would return
3 records
--
NTC

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

ensure that a) blank
***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
*** 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

 

[Guest]

Got that. Thanks very much. Can I ask a related question? How do I retrieve
records so that, on my report, year, date, appraiser etc are tabulated for
each appraisal, but that it also lists people who haven't been appraised (ie
there is no record in the appraisal table but their names are in the
tblConsultants)? My current query and report do most of that but don't
retrieve the last group. Thanks once again. Sandy

not knowing your table structure or query design I'm kind of flying blind and
maybe my advice is off base - so take with a grain of salt....

I scanned briefly the query code you selected....I do not see any reference
to date in it but I see the WHERE clause defined by the field
"Current"....this maybe what is determining what records are returning to you

You can always create a new query as an experiment.....if I have a simple 3
record table like this:

2004, Sandy, PeanutButter
2005, Sandy,
2006, Sandy, Jelly

Then I can search on dates &/or Sandy and the search will return all three
records....with your food choice in the second being blank....

I'm not searching for a null....am searching on the other two fields....

or this table
2003, NTC
2004, Sandy
, Sandy
2005, Sandy
2005, NTC

And a search on all 'Sandy' as the Criteria in the query design would return
3 records
--
NTC

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

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

 

[Larry Linson]

Chances are the Query on which your report is based uses the "equijoin" Join
Type (only where the field is in both records) when it should be a Join Type
of (all records from xxx table and only those that match from yyy table).

Larry Linson
Microsoft Access MVP

Got that. Thanks very much. Can I ask a related question? How do I
retrieve
records so that, on my report, year, date, appraiser etc are tabulated for
each appraisal, but that it also lists people who haven't been appraised
(ie
there is no record in the appraisal table but their names are in the
tblConsultants)? My current query and report do most of that but don't
retrieve the last group. Thanks once again. Sandy

not knowing your table structure or query design I'm kind of flying blind
and
maybe my advice is off base - so take with a grain of salt....

I scanned briefly the query code you selected....I do not see any
reference
to date in it but I see the WHERE clause defined by the field
"Current"....this maybe what is determining what records are returning to
you

You can always create a new query as an experiment.....if I have a simple
3
record table like this:

2004, Sandy, PeanutButter
2005, Sandy,
2006, Sandy, Jelly

Then I can search on dates &/or Sandy and the search will return all
three
records....with your food choice in the second being blank....

I'm not searching for a null....am searching on the other two fields....

or this table
2003, NTC
2004, Sandy
, Sandy
2005, Sandy
2005, NTC

And a search on all 'Sandy' as the Criteria in the query design would
return
3 records
--
NTC

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

:

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

 

[Guest]

Thanks a lot Larry. All is now clear! Sandy

Chances are the Query on which your report is based uses the "equijoin" Join
Type (only where the field is in both records) when it should be a Join Type
of (all records from xxx table and only those that match from yyy table).

Larry Linson
Microsoft Access MVP

Got that. Thanks very much. Can I ask a related question? How do I
retrieve
records so that, on my report, year, date, appraiser etc are tabulated for
each appraisal, but that it also lists people who haven't been appraised
(ie
there is no record in the appraisal table but their names are in the
tblConsultants)? My current query and report do most of that but don't
retrieve the last group. Thanks once again. Sandy

not knowing your table structure or query design I'm kind of flying blind
and
maybe my advice is off base - so take with a grain of salt....

I scanned briefly the query code you selected....I do not see any
reference
to date in it but I see the WHERE clause defined by the field
"Current"....this maybe what is determining what records are returning to
you

You can always create a new query as an experiment.....if I have a simple
3
record table like this:

2004, Sandy, PeanutButter
2005, Sandy,
2006, Sandy, Jelly

Then I can search on dates &/or Sandy and the search will return all
three
records....with your food choice in the second being blank....

I'm not searching for a null....am searching on the other two fields....

or this table
2003, NTC
2004, Sandy
, Sandy
2005, Sandy
2005, NTC

And a search on all 'Sandy' as the Criteria in the query design would
return
3 records
--
NTC


:

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

:

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