Query returning duplicate entries

[cbure]

I am new to Access and my company is expanding rapidly. I want to use a
database to help me easily compare data from two business units. I used to
do this comparison by "hand" in Excel and it has become far to time
consuming. I have put all the data that I use into two tables,
Transaction_Exceptions and Cash_Movements that I want to query against each
other. Each table is created from seperate business units, thus they can not
be combined. Here is the basic table structure for each:

Exceptions Table Cash Movements

Customer ID Customer ID
Trans ID Trans ID
Date Date
Amount Fee Amount
Base Amount
Total Amount


The basic idea is to list all the exceptions and only the related cash
movements. In the query I have designed I have created a relationship
between Trans ID, Date and Customer ID. I would
The exceptions table contains data that relates to the cash table, (Customer
ID, Trans ID, Date). The output I am looking for is as follows:

Exceptions Customer ID Date Trans ID Amount Fee
Amount Base Amount Total Amount
AAA 5/6 00001
10.00
Cash Movement Customer ID Date Trans ID Amount Fee Amount
Base Amount Total Amount
AAA 5/6 00001
5.00 5.00 10.00

What I would like to happen is to have every entry from the exceptions table
returned and only the related records from the cash table. The problem is
that their can be numerous exceptions records that relate to one cash item,
(one exception for Fee, one for Base), and the query output is returning
cash records that match by date, Customer ID and Trans ID but have already
been used. For example:

Exceptions Customer ID Date Trans ID Amount Fee
Amount Base Amount Total Amount
AAA 5/6 00002
20.00
Cash Movement Customer ID Date Trans ID Amount Fee Amount
Base Amount Total Amount
AAA 5/6 00002
0.00 20.00 20.00

Exceptions Customer ID Date Trans ID Amount Fee
Amount Base Amount Total Amount
AAA 5/6 00002
10.00
Cash Movement Customer ID Date Trans ID Amount Fee Amount
Base Amount Total Amount
AAA 5/6 00002
0.00 20.00 20.00

Is there a way to have the query only return a cash record once? The way my
tables are set up and the way the data is collected may be the underly issue
and there may be no way to do this but I thought I would try .

Thanks.

 

[Guest]

Is there a way to have the query only return a cash record once?
In a report change the property of the duplicated fields to Hide Duplicates
- Yes.

 

[cbure]

I went through and changed all the fields to hide duplicates and it is still
returning all the records.