nfpacct (11/9/2011)
Sean,you are correct some of the tables have a multiple records.
Does the query syntax correct for joining multiple tables.
select a.dtimesheetid,b.dtimesheetid,c.dtimesheetid, d.dtimesheetid,e.dtimesheetid,f.dtimesheetid
from tblPRTS as a (1 Record - Master Record)
join tblPRTSEarn as b on a.dTimesheetID = b.dTimesheetID (1 Record - but can be 1 or many)
join tblPRTSDeduct as c on a.dTimesheetID = c.dtimesheetid (2 Records - but can be 1 or many)
join tblPRTSBene as d on a.dTimesheetID = d.dtimesheetid (5 Records - but can be 1 or many)
join tblPRTSLeave as e on a.dTimesheetID = e.dtimesheetid(2 Records - but can be 1 or many)
join tblPRTSWC as f on a.dTimesheetID = f.dtimesheetid (1 Records - but can be 1 or many)
where a.dTimesheetID = 40827.651917091 and a.sTimesheetType = 'D'
Sure, the query is syntactically correct, but logically not what you are looking for. Notice it will join the IDs where they match for each subordinate table. With 2 rows in tblPRTSDeduct and 5 in tblPRTSBene, there will be 10 combinations. The total number of combinations for your joins will be 1x2x5x2x1=20 in this example. If you want a listing of all subordinate rows that match the one master row, use unions to select from each of the subordinate tables separately.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.