Query Question

[Chris O''Neill]

I have a combobox on a form that is bound to a table that is structure like
this:

ServiceID (AutoNumber) - Unique ID
Service (Text) - Service Description
SubService (Text) - Sub Service Description
Rate (Currency) - Rate Charged for Service

Example data would be as follows:

1 Decorating First Hour $80.00
2 Decorating Added Hrs. $75.00
3 Delivery Local $10.00
4 Delivery Non-Local $25.00
5 Materials $10.00

What I want the combobox to do is display the service description only once
for each main service type, like this:

Decorating
Delivery
Materials

I have tried using SELECT DISTINCT but I still get this in the combo box:

Decorating
Decorating
Delivery
Delivery
Materials

I also tried using SELECT TOP 1 but got this:

Decorating

Is there an SQL statement that will limit the output to just one of each of
the main service types?

Thanks, in advance, for any assistance provided.

Regards, Chris

 

[Ken Snell \(MVP\)]

This SQL statement will show just the unique values from the second field
(Service)

SELECT DISTINCT Service
FROM TableName;

If you want other columns to also be in the combobox's RowSource query, then
you'll need to make a decision about which of the multiple rows with same
Service data value is to be displayed.

 

[Chris O'C via AccessMonster.com]

SELECT service
FROM tablename
GROUP BY service

Chris
Microsoft MVP

 

[Jim Burke in Novi]

If you're saying that the combo box will only display the Service field and
nothing else, then DISTINCT should work, You should be able to just code

SELECT DISTINCT Service FROM tblWhatever.

If you need more than just the Service field then DISTINCT won't work.

 

[Chris O''Neill]

Sorry that I maybe wasn't clear enough in my explanation. I want the combo
box to only display the Service field (i.e. the description of the service,
such as "Decorating", but I need the ServiceID field (the auto number) to be
the bound field so that it's the ServiceID and not the Service field that's
stored in the underlying table. So, yes, DISTINCT does work when the only
field in the query is the Service field (i.e. SELECT DISTINCT Service FROM
tblServices), but as soon as I add the ServiceID field I get the multiple
services.

So, what I want is a way to have only one of each Service displayed in the
combo box and have the ServiceID stored in the table.

Is this possible?

Thanks, again, for any help or advice provided.

Regards, Chris

 

[Michael Conroy]

Chris,
The reason you are having trouble is you need to "normalize" your tables.
You need to seperate your table into two tables. The first table has just
ServiceID and Service in it. Then a seperate table with SubService ID,
SubService, Rate and Service ID. Your combo box will use a query that puts
the two together by joining on the Service ID field, but your bound column
will be the SubService ID, which is really how you make the distinct
reference. Hope that helps.