Hi. I need some help with an sql query to get unique values. Using the following sample data: fee Pin fee Ref person Occupation fee Bud Rec Time Elapsed DUB BM01 EMP 2610 600 DUB BM01 EMP 2610 600 DUB BM01 EMP 2610 480 DUB BM01 EMP 2610 180 DUB BM01 EMP 2610 510 DUB CD01 EMP 2610 600 DUB CD01 EMP 2610 150 DUB CD01 EMP 2610 90 DUB EE01 EMP 2218 510 DUB EE01 EMP 2218 510 LEE LR01 EMP 1177 450 LEE LR01 EMP 1177 12 LEE LR01 EMP 1177 150 LEE LR01 EMP 1177 168 LEE LR01 EMP 1177 48 LEE PS01 EMP 1957 246 LEE PS01 EMP 1957 222 LEE PS01 EMP 1957 12 I need to be able to get one single value for feeBudRec returned for each feeRef. The value of each feeBudRec is always the same for each individual feeRef (eg for every data row feeRef LR01 will have a feeBudRec of 1177). The reason why I need to get a single feeBudRec value for each feeRef is that I need to be able to total the feeBudRec value for each feeRef in a feePin (eg for feePin LEE, I need to total the feeBudRec values for LR01 and PS01, which should be 1177 + 1957 to get a total of 3134; but if I don't have unique values for feeBudRec, it will add the values for each row, which would bring back a total of 11756 for the 8 LEE rows). My experience with writing SQL queries is limited, but from searching the internet, it looks like I'll need to put in a subquery into my SQL query in order to get a single unique feeBudRec figure for each feeRef, and that a subquery that gets a minimum feeBudRec value for each feeRef should work for me. Based on examples I've found, I think the following subquery should work: SELECT a.feeRef, a.feeBudRec FROM ( SELECT uvw_EarnerInfo.feeRef, Min(uvw_EarnerInfo.feeBudRec) as AvailableTime FROM uvw_EarnerInfo GROUP BY uvw_EarnerInfo.feeRef ) as x INNER JOIN uvw_EarnerInfo as a ON a.feeRef = x.feeRef AND a.feeBudRec = x.AvailableTime; The problem is that I have no idea how to insert that subquery into the query I'm using to produce the report (as follows): SELECT uvw_EarnerInfo.feeRef ,uvw_EarnerInfo.PersonName ,uvw_EarnerInfo.PersonSurname ,uvw_EarnerInfo.feePin ,uvw_RB_TimeLedger.TimeDate ,uvw_RB_TimeLedger.matRef ,uvw_RB_TimeLedger.TimeTypeCode ,uvw_RB_TimeLedger.TimeCharge ,uvw_RB_TimeLedger.TimeElapsed ,uvw_WoffTimeByTime.WoffMins ,uvw_WoffTimeByTime.WoffCharge ,uvw_EarnerInfo.feeBudRec ,uvw_EarnerInfo.personOccupation FROM uvw_RB_TimeLedger LEFT OUTER JOIN uvw_WoffTimeByTime ON uvw_RB_TimeLedger.TimeId = uvw_WoffTimeByTime.TimeId RIGHT OUTER JOIN uvw_EarnerInfo ON uvw_EarnerInfo.feeRef = uvw_RB_TimeLedger.feeRef WHERE uvw_RB_TimeLedger.TimeDate >= @TimeDate AND uvw_RB_TimeLedger.TimeDate <= @TimeDate2 If that subquery will get the correct results, can anyone please help me with inserting it into my report query. Otherwise, can anyone let me know what I will need to do to get a unique feeBudRec value for each feeRef?

To select one feeRef and feeBudRec in SQL you can use Distinct. In your case: select distinct a.feeRef, a.feeBudRec from uvw_EarnerInfo

If I use Select Distinct on the whole query, the results are exactly the same - each row of data includes a value for feeBudRec, so the totals for feePin are incorrect. If I need to use Select Distinct on a subquery to my query, that brings me back to my original problem of not knowing how to write a subquery into the query.

You can do something like this: select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_idfrom product p where p.product_id = 101 This may work for you: (maybe you should modify something for it to work properly) SELECT uvw_EarnerInfo.feeRef ,uvw_EarnerInfo.PersonName ,uvw_EarnerInfo.PersonSurname ,uvw_EarnerInfo.feePin ,uvw_RB_TimeLedger.TimeDate ,uvw_RB_TimeLedger.matRef ,uvw_RB_TimeLedger.TimeTypeCode ,uvw_RB_TimeLedger.TimeCharge ,uvw_RB_TimeLedger.TimeElapsed ,uvw_WoffTimeByTime.WoffMins ,uvw_WoffTimeByTime.WoffCharge ,(select ei.feeBudRec from uvw_EarnerInfo ei where ei.feeRef=uvw_EarnerInfo.feeRef) ,uvw_EarnerInfo.personOccupation FROM uvw_RB_TimeLedger LEFT OUTER JOIN uvw_WoffTimeByTime ON uvw_RB_TimeLedger.TimeId = uvw_WoffTimeByTime.TimeId RIGHT OUTER JOIN uvw_EarnerInfo ON uvw_EarnerInfo.feeRef = uvw_RB_TimeLedger.feeRef WHERE uvw_RB_TimeLedger.TimeDate >= @TimeDate AND uvw_RB_TimeLedger.TimeDate <= @TimeDate2