Multiple table select query remove duplicates

[Steve Perrins]

Hi,

I have two tables exp_recs and inc_recs both with the following fields :-
ID - Primary key
ac_gross
ac_date
ac_PROJID

exp_recs has the following records
1 50.20
2 64.67
3 99.33

inc_recs has the following records
1 60.50
2 99.00
I run the following query:-

SELECT exp_recs.ac_gross AS exp_gross, inc_recs.ac_gross AS inc_gross FROM
exp_recs, inc_recs

And the following is produced
ac_gross inc_gross
50.20 60.50
50.20 99.00
64.67 60.50
64.67 99.00
99.33 60.50
99.33 99.00

How would I code my query to produce the following
ac_date ac_gross inc_gross
01/01/2006 50.20
02/01/2006 60.50
08/01/2006 64.67
10/01/2006 99.00
20/01/2006 99.33


Thanks in advance

Steve.

 

[[MVP] S.Clark]

You can use the HideDuplicates property in a report. But nothing in a
query.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Steve Perrins]

Steve,

Thanks for the reply.

As the results are to be displayed on a form I'll use a listbox and populate
it using VBA code. That way I'll have better control on what is displayed.

Regards

Steve Perrins