Microsoft SQL:
Note: The following sample SQL query also enumerates the COMPTYPE and DEDUCTIONTYPE
SELECT 
    e.EMPLOYEENAME, 
    --alloc.COMPTYPE, 
    CASE alloc.COMPTYPE
        WHEN 1 THEN 'Pay type'
        WHEN 2 THEN 'Benefit'
        WHEN 3 THEN 'Deduction'
        WHEN 4 THEN 'tax'
        ELSE  'unknown'
    end as COMPTYPE,
    alloc.AMOUNT, 
    --alloc.DEDUCTIONTYPE, 
    CASE alloc.DEDUCTIONTYPE
        WHEN 1 THEN 'federal'
        WHEN 2 THEN 'futa'
        WHEN 3 THEN 'soc security'
        WHEN 4 THEN 'medicare'
        WHEN 5 THEN 'local tax'
        WHEN 6 THEN 'suta'
        WHEN 7 THEN 'sdi'
        WHEN 8 THEN 'state tax'
        WHEN 9 THEN 'deduction'
        WHEN 10 THEN 'eic'
        WHEN 11 THEN 'workers comp'
        WHEN 12 THEN 'insurance tax'
        WHEN 13 THEN 'md tax for de'
        WHEN 14 THEN 'misc tax'
        WHEN 15 THEN 'uhic'
        WHEN 16 THEN 'oflt'
        WHEN 17 THEN 'additional medicare'
        WHEN 18 THEN '18'
        ELSE 'n/a'        
    END as DEDUCTIONTYPE,
    comptypes.DESCRIPTION

FROM dbo.PY7EMPLOYEES as e
    INNER JOIN dbo.PY7CALCULATIONS as pycalc ON e.PY7EMPLOYEESID = pycalc.PY7EMPLOYEESID
    INNER JOIN dbo.PY7ALLOCATIONS as alloc ON pycalc.PY7CALCULATIONSID = alloc.PY7CALCULATIONSID
    INNER JOIN dbo.PY7EMPLOYEECOMPTYPES as et ON alloc.PY7EMPLOYEECOMPTYPESID = et.PY7EMPLOYEECOMPTYPESID
    INNER JOIN dbo.PY7COMPTYPES as comptypes ON et.PY7COMPTYPESID = comptypes.PY7COMPTYPESID


Microsoft Access:
SELECT dbo_PY7EMPLOYEES.EMPLOYEENAME, dbo_PY7ALLOCATIONS.COMPTYPE, dbo_PY7ALLOCATIONS.AMOUNT, dbo_PY7ALLOCATIONS.DEDUCTIONTYPE, dbo_PY7COMPTYPES.DESCRIPTION

FROM (((dbo_PY7EMPLOYEES INNER JOIN dbo_PY7CALCULATIONS ON dbo_PY7EMPLOYEES.PY7EMPLOYEESID = dbo_PY7CALCULATIONS.PY7EMPLOYEESID) INNER JOIN dbo_PY7ALLOCATIONS ON dbo_PY7CALCULATIONS.PY7CALCULATIONSID = dbo_PY7ALLOCATIONS.PY7CALCULATIONSID) INNER JOIN dbo_PY7EMPLOYEECOMPTYPES ON dbo_PY7ALLOCATIONS.PY7EMPLOYEECOMPTYPESID = dbo_PY7EMPLOYEECOMPTYPES.PY7EMPLOYEECOMPTYPESID) INNER JOIN dbo_PY7COMPTYPES ON dbo_PY7EMPLOYEECOMPTYPES.PY7COMPTYPESID = dbo_PY7COMPTYPES.PY7COMPTYPESID

WHERE (((dbo_PY7CALCULATIONS.PERIODENDDATE)=#5/31/2004#) AND ((dbo_PY7EMPLOYEES.PY7EMPLOYEESID)=10));


Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Our Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.