SQL combobox question

[DAmoss]

I have the following sql code in a query, this quite happily gives me totals for each combo item, but my ETHN combobox item is made up of a 'value' and a 'description'

How do I show the description with its total rather than the value with its total?

ie. Ethnicity Total
1 234 (value)

I would like this:

White 234 (Description)


SELECT [INDIVIDUALS Starter Info].ETHN AS Ethnicity, Count([INDIVIDUALS Starter Info].ETHN) AS Total
FROM [INDIVIDUALS Starter Info]
GROUP BY [INDIVIDUALS Starter Info].ETHN;

Cheers

 

[PHV]

Hopefully you have a lookup table instead of a lookup field ?

http://www.mvps.org/access/lookupfields.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DAmoss]

Yes, it uses a lookup table for the ETHN, I have checked out the link you supplied ???

Your reply really doesn't help as far as I can see. Are you saying that I can or cannot change my query to show the description instead of the value?

Cheers

 

[PHV]

Why not simply JOIN your ethnicity lookup table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DAmoss]

JOINing works a treat, but I was wondering if there was a shorter way of doing it and if possible, how to show the description and total even if it is zero. At the moment zero totals don't show at all.

My new Sql:

SELECT [INDIVIDUALS Starter Info].ETHN AS [ETHN Value], ETHN.Description, Count([INDIVIDUALS Starter Info].ETHN) AS Total
FROM [INDIVIDUALS Starter Info] INNER JOIN ETHN ON [INDIVIDUALS Starter Info].ETHN = ETHN.Value
GROUP BY [INDIVIDUALS Starter Info].ETHN, ETHN.Description;

 

[PHV]

Something like this ?
SELECT E.Description, Count(I.ETHN) AS Total
FROM ETHN AS E LEFT JOIN [INDIVIDUALS Starter Info] AS I ON E.Value = I.ETHN
GROUP BY E.Description

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DAmoss]

That works beautifully.

Thanks PHV