access puzzle

[Matt]

Hi all,

encountering something of a problem I'd be garteful for aid ...

I ahve a form which is based on a query - tbEmployees - works fine
into this i have a subform tbSick into which info is placed and sent via
qrySick

also fine

I have a second subform - frmTotals which gives a running total of sick days
based on a query - qryTotalSick

This is the problem - it all works fine as soon as there is a record but
doesn't appear at all if there are nop sickness records for the person in
question -

the reason I tghink is that qryTotal sums the number of sick days from a
sickness table and groups by staffId from an employee table (in a on-many
realtionship)/ This all works gine but, of course, there are no records in
this summing query for people who haven't been sick....

ths, in the form when I force a rquery I am reading the value of a text boix
which isnt there because when teh form opened there was no value -

what I really want is there to be always a default box with a value 0 but
entering defaults in teh propetry list doesnt work -


any ideas ..... anyomne?

 

[Steve Schapel]

Matt,

Try a query something along these lines, as the Record Source of the
frmTotals subform...

SELECT tbEmployees.staffId, Nz(Sum([SickDays]),0)
FROM tbEmployees LEFT JOIN tbSick ON tbEmployees.staffId = tbSick.staffId
GROUP BY tbEmployees.staffId

 

[G. Vaught]

or set your default value to 0 for sick days. That way anyone who does not
have any sick days will have a value of zero. Having said this, you will
need to execute an update query to existing employees whose sickdays are
null (empty). Also any new employees added will automatically start at 0
sick days.

Matt,

Try a query something along these lines, as the Record Source of the
frmTotals subform...

SELECT tbEmployees.staffId, Nz(Sum([SickDays]),0)
FROM tbEmployees LEFT JOIN tbSick ON tbEmployees.staffId = tbSick.staffId
GROUP BY tbEmployees.staffId

--
Steve Schapel, Microsoft Access MVP

Hi all,

encountering something of a problem I'd be garteful for aid ...

I ahve a form which is based on a query - tbEmployees - works fine
into this i have a subform tbSick into which info is placed and sent via
qrySick

also fine

I have a second subform - frmTotals which gives a running total of sick
days based on a query - qryTotalSick

This is the problem - it all works fine as soon as there is a record but
doesn't appear at all if there are nop sickness records for the person in
question -

the reason I tghink is that qryTotal sums the number of sick days from a
sickness table and groups by staffId from an employee table (in a on-many
realtionship)/ This all works gine but, of course, there are no records
in this summing query for people who haven't been sick....

ths, in the form when I force a rquery I am reading the value of a text
boix which isnt there because when teh form opened there was no value -

what I really want is there to be always a default box with a value 0 but
entering defaults in teh propetry list doesnt work -


any ideas ..... anyomne?

 

[Steve Schapel]

G.,

My understanding is that sick days are in a separate table (as, indeed,
they should be). So you would run an Append Query to put a zero record
in for new/existing employees.

 

[G. Vaught]

Yes, the method you take depends on where the field exists.