Unwanted Duplicate Fields in Query

P

PJ

I am trying to consolidate two tables, Transfer_In and Transfer_Out, into one
table that will give me the transfer in date and the transfer out date for
each issue. The wrinkle is there are multiple transfer in and out records
for each issue. I'm trying to get the query to align the correct transfer in
date with the correct transfer out date...ultimately being able to calculate
the total days the issue was worked. I'm pulling this information from an
audit trail of another database.

The problem I'm having is I pull in all the transfers in and all the
transfers out (ie if there are two transfers I end up with 4 records). I
have widdled this down somewhat by limiting the transfers out to only those >
the transfer in date. But now I'm stuck. Here is the SQL. I'm Using Access
2003. Appreciate any advice...including how I could pull in differently from
the audit trail.

SELECT [TBL_VOLUME_TRANSFERIN_WHLS/OPT 1].ISSUE_ID,
[TBL_VOLUME_TRANSFERIN_WHLS/OPT 1].TRANS_DT AS [IN],
[TBL_VOLUME_TRANSFEROUT_WHLS/OPT 1].TRANS_DT AS OUT
FROM [TBL_VOLUME_TRANSFERIN_WHLS/OPT 1] LEFT JOIN
[TBL_VOLUME_TRANSFEROUT_WHLS/OPT 1] ON [TBL_VOLUME_TRANSFERIN_WHLS/OPT
1].ISSUE_ID = [TBL_VOLUME_TRANSFEROUT_WHLS/OPT 1].ISSUE_ID
WHERE ((([TBL_VOLUME_TRANSFERIN_WHLS/OPT 1].TRANS_CODE)="6") AND
(([TBL_VOLUME_TRANSFEROUT_WHLS/OPT 1].TRANS_CODE)="6"))
GROUP BY [TBL_VOLUME_TRANSFERIN_WHLS/OPT 1].ISSUE_ID,
[TBL_VOLUME_TRANSFERIN_WHLS/OPT 1].TRANS_DT, [TBL_VOLUME_TRANSFEROUT_WHLS/OPT
1].TRANS_DT, [TBL_VOLUME_TRANSFERIN_WHLS/OPT 1].TRANS_DT
HAVING ((([TBL_VOLUME_TRANSFEROUT_WHLS/OPT
1].TRANS_DT)>[TBL_VOLUME_TRANSFERIN_WHLS/OPT 1].[TRANS_DT]));
 
Top