Query Help

[BuchanBronco]

In access 2010 I want to have two columns type and status, with the type being either manual or hydraulic and the status being open or closed if the manual type is chosen and either normal or overridden open if hydraulic is chosen. How do I create the query allowing only open and closed to be shown in the status column if i choose the type to be manual and only show Normal and Overridden open for hydraulic? I think I have tried everything. I can do the lookup no problem refering back to the type and status table but its the query im having a problem with.

Eg.

Type Status

Manual Open
Closed

Hydraulic Normal
Overridden

I have made a table type with manual and hydraulic and done a lookup so its a drop down box. Did the same with status table. When i do the drop down for status I only want to see open and closed when the type manual is selected and only want to see normal and overridden when hydraulic is selected. Hope someone can help



Cheers
Cheers

[orange]

Are you working from a form with drop down boxes(combobox)?

People will advise you NOT to use lookups at the table level.

see http://www.btabdevelopment.com/ts/removelookups

http://www.btabdevelopment.com/ts/de...aspx?PageId=34

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

[NTC]

well it depends on how you implement this..... one way is to have a code field in both so that
open 1
closed 1
normal 2
overridden 2

manual 1
hydraulic 2

then use the codes to filter data....this works if it is table driven. On the other hand you might be able to use IIF statements in the query based on the text strings and achieve the same thing.

More than one way to skin the cat - difficult to say from your post's info alone...

Hope it gets you going in the right direction.

[BuchanBronco]

Thanks for your help. Ive tried explain the problem step by step, maybe this might allow you to see my problem. Right i'm going to see if i can simplify the problem. I have created the table DC1 Manifold, In design view i have entered Valve tag (primary key), Description, Type and Status as field names (data type for all being selected as text at the moment). I have created another table called Valve Type, In design view i have entered Valve Type as the field name (primary key). In that table I have entered Manual and Hydraulic under the field valve type. I have created another table Valve Status, In design view i have entered Valve Status as the field name (primary key). In that table I have entered Open, Closed, Normal and Overridden Open under the field Valve Status. Going back to the DC1 Manifold table and clicking design view, I have went to the field name "type" and for the data type i have used the lookup wizard and linked to the table Valve Type. I also went to the field name "status" and for the data type used the lookup wizard again to link to the table Valve Status. If I go to the DC1 manifold table and input some valve tags and click the type column it allows me to choose manual or hydraulic. If i click the status column it allows me to choose open, closed, normal and overridden open. Is there any way to do a query or a filter that would mean that if i chose the type to be manual that only open and closed would pop up on the status. Also if the type chosen was hydraulic that only normal and overridden open would be shown. I hope there is a way to do this. Thanks again