need sql query to show 1 of each different item

[Lee Weaver]

I have a table that I am using as a lookup for a set of list boxes. the list boxes will cascade from 1 to the next.

the first list box i need to only show each catagory once. the table has about 150 entries. but there are only 6 diferent catagories at this time.

so i need to know how to build a SQL query that will show only unique entries.

 

[pbaldy]

SELECT Category
FROM TableName
GROUP BY Category

or

SELECT DISTINCT Category
FROM TableName

 

[Lee Weaver]

AWESOME the select distinct worked like a charm

 

[Lee Weaver]

cascading trouble.

I am now having trouble getting the next 1 figured out. here is an example of the data in the table...

Catagory,type,configuration
chair,dinner table,wooden
chair,dinner table,metal
chair,lounge,fabric
chair,lounge,leather
table,diner,wooden
table,dinner,glass
table,coffie,plastic
table,coffie,glass
table,coffie,wooden

I need to only show the "Type" in the next field based on the catagory chosen in the previous field.

once i get this down I will be able to figure out the rest of my sql needs based on this information ( I HOPE!)

 

[pbaldy]

Maybe this will help you out:

http://www.baldyweb.com/CascadingCombo.htm

 

[Lee Weaver]

I'm attempting to use the first method. but getting the row source sql right is the problem.
the table is "furniture"
SELECT furniture.type FROM funiture WHERE catagory.funiture=me.block5_catagory;

 

[pbaldy]

"Me" is only valid inside VBA, so in a query it would have to be:

SELECT furniture.type FROM funiture WHERE catagory.funiture=Forms!FormName.block5_catagory

I assume it should be funiture.catagory rather than the other way around.

 

[Lee Weaver]

my problem is im trying to define this in the table definition using the lookup feature. not on a form
and yes I had that mixed up.. this line isn't actually what i'm using. my table has nothing to do with furniture. jsut using that as a aid to discribe my problem

 

[pbaldy]

I'd recommend against lookup fields:

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

In any case, I'm not sure you can cascade them anyway.

 

[Lee Weaver]

hmmm not a good sign although most of the popints that are listed as "evil" won't effect this application. I do need to restrice what can be placed in here. but i want it easily updated by an admin by just adding an entry to the "furniture" table.

I guess i can vba code it. but if someone ever access the table directly and doesn't use the form it will not restrict the options...

 

[pbaldy]

Most of us never let users anywhere near the tables themselves anyway.