Thread: Help with Query
06-30-11, 11:10 #1Registered User
- Join Date
- Jun 2011
Unanswered: Help with Query
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.
06-30-11, 15:37 #2Registered User
- Join Date
- Apr 2006
- Huddersfield, UK
This is a quite simple for you to resolve, but first things first, all the data accross all the tables need to be linked to each other. What I'm thinking you need will look like this:
type ----------> called from tbl_type
Status ----------> Called from tbl_status
if you have used lookup wizard then the relationships will already be created for you. (if not msg back)
Ok on with the Query;
You first need another new table, we'll say call this tbl_query
Do the same as you did before and create a lookup field with status in it from the tbl_status call this field status check
Start a new query and add the two tables tbl_query and tbl_dcmanifold. Add All fields from each to the query.
Under status from the tbl_dcmanifold where is says criteria put =[statuscheck]
save and run the query should only pull records that match the record the table status_querysometimes simple is best.... and i'm just a simple fellow.