Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    23

    Unanswered: Issue with sum in query when no records exist Access 2003

    Hi all,

    I'm in the process of building a staff holiday booking system.

    I have a sub form based on a query which displays a staffs current holiday entitlement and sums the the staffs booked holidays.

    The issue im having is it wont display the sub form if a staff has no holidays booked, is there a work around?

    Current query lists all entitlemtns with bookings and sums the bookings which is fine.

    SELECT tblHolEnt.HolEntID, tblHolEnt.StaffID, tblHolEnt.StartDate, tblHolEnt.EndDate, tblHolEnt.Entitlement, tblBookHol.HolType, tblBookHol.HolStartDate, tblBookHol.HolEndDate, tblBookHol.NoDays, tblBookHol.Canceled

    FROM tblHolEnt LEFT JOIN tblBookHol ON tblHolEnt.StaffID = tblBookHol.StaffID
    GROUP BY tblHolEnt.HolEntID, tblHolEnt.StaffID, tblHolEnt.StartDate, tblHolEnt.EndDate, tblHolEnt.Entitlement, tblBookHol.HolType, tblBookHol.HolStartDate, tblBookHol.HolEndDate, tblBookHol.NoDays, tblBookHol.Canceled

    HAVING (((tblHolEnt.StartDate)<=Date()) AND ((tblHolEnt.EndDate)>=Date()) AND ((tblBookHol.HolType)=2) AND ((tblBookHol.HolStartDate)>=[StartDate]) AND ((tblBookHol.HolEndDate)<=[EndDate]) AND ((tblBookHol.Canceled)=False));

    I can display all the entitlement as well as bookings using an "Is Null" criteria but it will then not allow me to sum.

    Hope this makes sense

  2. #2
    Join Date
    Apr 2011
    Posts
    23
    Its ok all sorted.

    Created SubForm showing staff Entitlement then a SubSubForm showing sum of days.

    Thanks

    Anyway

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •