Removing duplicates when joining 2 queries together

[annysjunkmail]

Have struggled with this one for ages and need some expert help...

I want to construct a query by extracting results from 2 other queries
but I am getting duplicates records, even though there should not be
duplicates. Query 1 contains most fields that I need, Query 2,
however, contains calculated fields that I also need and which I am
trying to match with Query 1. Both queries analyse different tables so
it was easier for me to design 2 different queries to extract relevant
info. Both queries contain a reference no that can be used to match
the records.

Here's a simplified example of what I am trying to do.

Query1 Query1 Query1 Query2 Query2
RefNo Name Address Grant Pyts Total
1 A xxx 1000 500 500
2 B xxx 2000 1400 600
3 C xxx 3000 900 2100

In my real life example though, my query should return 3 records but as
the reference no occurs 3 times in Query1 and 3 times in Query2 I get 9
records (3 of each)...all I want to see is 3 unique records. Can
someone help? Here is my SQL...

SELECT DISTINCTROW Query1.OperationalProgrammeName,
Query1.PriorityName, Query1.MeasureDescription, Query1.OrgName,
Query1.Grant, Query1.ApplicationRefNo, Query1.DestDescription,
Query1.DatePaidByPayAuthority, Query1.TotalPaymentAmount,
Query2.ExpenditureType, Query2.ExpendStartDate, Query2.ExpendEndDate,
Query2.TotalExpenditure, Query2.TotalEligibleExpenditure
FROM Query1 LEFT JOIN Query2 ON Query1.ApplicationRefNo =
Query2.ApplicationRefNo;


Thanks
Tony

 

[[MVP] S.Clark]

Using GROUP BY can help.

e.g. Select f1, sum(f2) from tablename GROUP by f1

I'm not sure where you would implement in your example, but try adding it in
one place at a time to see how to reduce the results.


--
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