DLookup - Not exact match

[kt1978]

Hi

I'm having trouble with something that I though would be simple (and it probably is).

I have a query that displays a persons years service

I then want to look up their entitlemet based on the years service.

The entitlement table is...

0 yrs - 30 days
3 yrs - 31 days
5 yrs - 32 days
10 yrs - 33 days

The problem is it is not finding anything for the any of the inbetween years.

I've tried dlookup and SQL.

the SQL is

SELECT qryEmployees.EIN, qryEmployees.Fullname, qryEmployees.YrsService, qryEnt.Days
FROM qryEmployees LEFT JOIN qryEnt ON qryEmployees.YrsService = qryEnt.Service
WHERE (((qryEnt.Service)>=[YrsService]))
GROUP BY qryEmployees.EIN, qryEmployees.Fullname, qryEmployees.YrsService, qryEnt.Days
ORDER BY qryEmployees.YrsService;

All this returns is anything that is an exact match.

Thanks

 

[kt1978]

[SOLVED] Re: DLookup - Not exact match

I've sorted this now.

I had to remove the join from the entitilement table, this then listed a record for each of the entitlement above the yrs service.

I then had to use the Max on the days.

SQL

SELECT qryEmployees.EIN, qryEmployees.Fullname, qryEmployees.YrsService, Max(qryEnt.Days) AS MaxOfDays
FROM qryEmployees, qryEnt
WHERE (((qryEmployees.YrsService)>=[qryEnt].[Service]))
GROUP BY qryEmployees.EIN, qryEmployees.Fullname, qryEmployees.YrsService
ORDER BY qryEmployees.YrsService;

Thanks