Limiting repeating rows in a listbox

[SimonSezz]

I have a listbox that is unbound. I have set the rowsource property to a query which is:

SELECT tblCompanyPlans.PlanCategory
FROM tblCompanyPlans
ORDER BY tblCompanyPlans.PlanCategory;

The tblCompanyPlans table has three fields:

PlanCategory
PlanType
CoverageType


There are multiple rows in the table with the same PlanCategory but the PlanType and CoverageType fields differ from row to row, for example:

----------------------------------------------------------------
|   Plan Category    |     Plan Type      |   Coverage Type    |
----------------------------------------------------------------
| Dental             | HMO                | Family             |
----------------------------------------------------------------
| Dental             | HMO                | Single             |
----------------------------------------------------------------
| Medical            | HMO                | Family             |
----------------------------------------------------------------
| Medical            | HMO                | Single             |
----------------------------------------------------------------
| Medical            | HMO                | Single + 1         |
----------------------------------------------------------------
| Dental             | HMO2               | Single + 1         |
----------------------------------------------------------------
| Medical            | PPO                | Family             |
----------------------------------------------------------------
| Medical            | PPO                | Single             |
----------------------------------------------------------------
| Medical            | PPO                | Single + 1         |
----------------------------------------------------------------
| Dental             | PPO2               | Single + 1         |
----------------------------------------------------------------

I want the listbox to just show one row for each type of PlanCategory. So for the above table the listbox would have just two rows:

Dental
Medical


Is this better done with VBA or can it be easily accomplished in the QBE or a query statement?

 

[boblarson]

Use either:

SELECT DISTINCT tblCompanyPlans.PlanCategory
FROM tblCompanyPlans
ORDER BY tblCompanyPlans.PlanCategory;

OR

SELECT tblCompanyPlans.PlanCategory
FROM tblCompanyPlans
GROUP BY tblCompanyPlans.PlanCategory
ORDER BY tblCompanyPlans.PlanCategory;

 

[khawar]

SELECT Distinct tblCompanyPlans.PlanCategory
FROM tblCompanyPlans
ORDER BY tblCompanyPlans.PlanCategory;

 

[SimonSezz]

That was so easy <hits head>

Thanks guys!

 

[boblarson]

 

[khawar]

Hi bobLarson have you ever compared which one is faster on large data set
Select Distinct or group By

 

[boblarson]

Hi bobLarson have you ever compared which one is faster on large data set
Select Distinct or group By

No, never have tested that.

 

[khawar]

Not so important but just came to my mind