Select Distinct Help

[fionny]

Hi All,



Another question someone might be able to help with.

I am doing a select Distinct query on a large table of info. This works perfectly until I add in a specific set of data "Age Group" this is because my Order number can be in multiple age groups. Is there any way of telling Access that I only want 1 instance of each order number and to just take the first value?

thanks,
Fionn

[euanl]

I would question why you would want to include the age group since you are only wanting to see one instance of the record, thereby making the age group irrelevant.

If you simply can't live without this field though, you could create a second query that shows the Max or Min value against for the age group against each record and link your query to that. So, let's call your field [Age Group], select that into the query twice, hit the 'totals' icon, leave the first instance of the field as 'Group by', the second as 'Max' (or 'Min' as the case may be). Now in your query link your [Age Group] to the [MaxofAge Group] field in the first query. Hope that makes sense. You may have to add other fields to the first query and link them to make it work right, but I have used a similar method before (with 3 fields linked), which has worked fine so I know it is possible.

[fionny]

I do need the field in there for various lookups and counts but yes what you described sounds right, I will try it thanks!