Form help

[jguillen]

Hello I’m kind of new to Access I have a question on a form
I’m developing, the form contains fields:
1-Job# (has a look out list based on a query)
2- Trade (this field has a list; the options are Plumbing, Sheet metal, and Hydronicks)
3-Phase (here is where I need a drop list based on the previous inputs)
4-Task (same here)

My question is how I make a relationship based on the first and the second field imputes to generate a list for the Phase and Task that relates to the Job and trade. Each job has different phases and tasks by trade.

Where by the job# and trade will only limit input on field 3 and 4 relative to that job# and trade

The phase and task by job# information is in a different table.
I have created a query to pull a look up field for the Job#.

 

[definewebsites]

Hi there,

Is it possible that you could be a little clearer with your explanation. I am finding it rather difficult to follow the flow of your explanation.

Cheers

 

[liddlem]

Hi Jguillen
If i understand you correctly, you are either missing a field (Job Type) or you are making the lookup queries too complex? (Currently, you need to tell the database how many phases there will be for EACH JOB when it is created.)

Within each trade, I imagine that there would be different types of Jobs. For example..
Within plumbing, you could have 2 types of jobs (each with a different number of phases)
INSTALL HOT WATER SYSTEM - Phase 1 = Inspect site / Phase 2 = Quote client / Phase 3 = Install system BUT
UNBLOCK DRAIN could have Phase 1=Locate Blockage / Phase 2=Dig hole to uncover pipes / Phase 3=remove blockage / Phase 4=fill-in hole / Phase 5=Cement / Phase 6=Invoice Client

Now you should do 1 of 2 things.
METHOD 1 - The "hard" way - But ensures better data integrity

Make a table that lists all the PHASES of each JOB TYPE.

JType, Phase, Description
Install Hot Water, 1, Inspect site
Install Hot Water, 2, Quote client
Install Hot Water, 3, Install system
Inblock Drain, 1, Locate Blockage
Inblock Drain, 2, Dig hole to uncover pipes
Inblock Drain, 3, Remove blockage
Inblock Drain, 4, Fill-in hole
Inblock Drain, 5, Cement
Inblock Drain, 6, Invoice Client

On the lookup field, create a query that selects all the records belonging to a particular Job TYPE.

METHOD 2 - Assuming that the user knows all the phases of each job type (leaves you wide open for dirty/incorrect data) \
Allow the user to type in a number which represents the phase.

 

[jguillen]

Yes, sorry I have 2 tables payroll and job info. The form I'm creating is for the payroll,
As the user inputs the job# and the Trade I need to generate a list from the job info table based on the values of the 2 fields(Job# and Trade). As to limit the input on the phase and the Task field correponding to that Job# and Trade. Each Job# has different phases and task by trade.

 

[jguillen]

Thank you Liddlem I do have a table that has the information on job# (job# refers to a project site), Phase, Task and Trade, but I need to generate a list for the phase and task corresponding to that job# and trade.

 

[jguillen]

Thank all for your help I figured it out and fixed my form it works good so far.
What I did I change the task field as a Combo box and used a query to extract the information with the parameters from the Job# and Trade fields from the form.
Here is the SQL code:

SELECT [test task lookup].[Job #], [test task lookup].Trade, [test task lookup].[Task #]
FROM [test task lookup]
WHERE ((([test task lookup].[Job #])=[Forms]![Timecard Data Entry Form]![Job #]) AND (([test task lookup].Trade)=[Forms]![Timecard Data Entry Form]![Trade]))
ORDER BY [test task lookup].[Task #];

Again Thank you all is great to have a community like this to enlighten and help.