My problem is a query using QBE with the following SQL:


SELECT DEPT228.dept_id, POSCODE228.To, DEPT228.Deptname, GOV609.IT_GLOBAL, GOV609.F21
FROM POSCODE228 RIGHT JOIN (DEPT228 RIGHT JOIN GOV609 ON DEPT228.dept_id = GOV609.Deptcode) ON POSCODE228.Poscode = GOV609.Poscode
GROUP BY DEPT228.dept_id, POSCODE228.To, DEPT228.Deptname, GOV609.IT_GLOBAL, GOV609.F21
HAVING (((GOV609.IT_GLOBAL)=Yes))
ORDER BY DEPT228.dept_id, POSCODE228.To DESC;

Explanation: GOV609 is my main table, joined DEPT228 is a list of 700 departments in up to 600 city locations, IT Global is a boolean operator, joined Postcode TO is a salary range identifier and F21 is the tag I want to update.

The problem, for a newwy at SQL is the query uses 'Totals' to get one IT tagged person per city, which is what I want, BUT:
If I try to update query I lose the 'totals' or if I add the AUTONUMBER, then that totals every record.

Or if I Make table query I also lose the 'totals' option.

Which option or expression will hold the selected query result for the update?

There is no reason to use a totals query since you are not using any of the aggregate functions. Change the query back to a standard select query. Then call up the properties dialog and change the distinct values property to yes. You should then be able to change the query to a make table query.

If I click properties above the query I get only 4 options to change, Where precisely to do I change to 'Distinct'?
My choices are Description, Format, Input Mask and Caption?

In the query design mode, there is a button on the toolbar with an arrow beside it marked query, click select from the options given.

You need to first change the query to a standard select query by unclicking the totals (sigma) button. Then right click on the grey background to call up the query's properties. You seem to be looking at a field's properties. The query has a property named "unique values". That needs to be set to yes.

Thanks, that's great.Correct Properties found.
What if I want to select 2 records per DEPT228.DEPT_ID?

Take a look at the following article and see if it helps. There is a sample of it in solutions.mdb.

Q153747 - ACC How to Create a Top N Values per Group Query

Q153747 creates internal haemmoraging when I tried to run my DeptID with the select SQL function. My opened tables are GOV609, DEPT228 and POSCODE.
Should I set the fields in design as GOV609.surname or GOV609.Deptcode (joined to Dept_ID), then ignore title (since I dont want 1st 2 records with each changing title, but 1st two by highest salary, which is a field called POSCODE.TO, or
Should I put the SQL add-in (Q153747) under Poscode.To?