2 table query

[CraigBFG]

I'm trying to create a query that will look at 2 tables to calculate remaining holiday.

EmployeeTable has a field HolidayEnt (entitlement)
AbsenceTable can have many types of absence, of which one is holiday.

I want to calculate remaining holiday based on "HolidayEnt-sum(AbsenceTable.DaysOff) where the AbsenceType=Holiday.

If there are no "holidays" recorded, then the result should be HolidayEnt.

Can I get this to work - NO.
Any help would be appreciated.

 

[Pat Hartman]

Try something like:

query1:
Select EmployeeID, Sum(ActivityAmt) as SumActivity
From tblTransactions
Where ActivityType = "Holiday"
Group by EmployeeID;

Select tblEmployee.EmployeeID, tblEmployee.HolidayEnt, query1.SumActivity, tblEmployess.HolidayEnt - Nz(query1.SumActivity,0) As RemainingHolidayEnt
From tblEmployee Left Join query1 ON tblEmployee.EmployeeID = query1.EmployeeID;

 

[CraigBFG]

Hi
That's how I managed to sort it in the end, a 2 query-query. Shame it can't be a single query.

Thanks for your help.

 

[Pat Hartman]

The problem is that the selection criteria is applied to the many-side table. You could do query1 as a subquery and so technically have a single query but Jet doesn't optomize subqueries properly so I rarely use them.