How to display in a form, various jobs assigned to a person?

[captgnvr]

D/All

Have a basic knowledge about Access.

I have about 50 crew and for each one I assign various jobs periodically.

Intend to have one table with crew info; another table with various jobs.

Before I start, I thought it prudent to get basic steps to go about.



The way I have done, it gives each time the persons name and the jobs.
So pls advice me the best way to go about it so that when a person is clicked it shows all the jobs assigned to him.

Whole day kept checking for response and hope to see one by today.

brgds/captgnvr

[RuralGuy]

It is a clasic case for a Form/SubForm with the Crew bound to the Form and the Jobs displayed in the SubForm. Does your Jobs table have a CrewID field as a ForeighKey?

[captgnvr]

D/RuralGuy
Finally got a notification. Thanks.

I am willing to start from fresh.

Table1: crew info with crew_id, names and rank.
Table2: Job descriptions with job_id and job descriptions.

From here pls tell me how to create a third table linking table1 and table2, so that I can make a form and subform to show all the jobs for the selected person.

Just going for dinner and will be back to check for guidance. Was on leave for three months and back on the ship again.

brgds/captgnvr

[RuralGuy]

What sort of relationship do these two tables have? 1:m, m:m? Will a "Job" have more than one "Crew" working on it?

[captgnvr]

D/RG

Nice to see ur post in the morning. I will explain it a bit more of my requirement.

table1
crew_id c_name rank
1 charlie c/O
2 smith 2/O
3 peter bosun
50 michael seaman


table2
job_id job_description
1 life boat cleaning
2 life raft cleaning
3 crane check ups
4 provision crane maintenance
99 fire fighting appliances
100 life saving appliances

So for example charlie can be assigned 'provision crane maintenance' and crane check ups. smith might be assiged jobs like job_id 1 ,4, 100.

And it will be also needed to check from many to many to see who are all assigned for life boat cleaning or crane maintenance or life saving appliances.

Sorry if I am not explaining it well. Pls ask for clarification if not clear.

Will sit at the comp whole day for guidance and steps to go about this.

brgds/captgnvr

[captgnvr]

D/RG

I have attached a sample file which might explain what I am trying to achieve.

When I click the desired crew name it should show all jobs assigned to him.

Next step is to click on the jobs and to show all the crew names who are assigned that job.

Pls help.

brgds/captgnvr

[RuralGuy]

Will sit at the comp whole day for guidance and steps to go about this.

brgds/captgnvr

You really know how to make a guy feel obligated don't you? Okay, I'll stick with this until you get over the hump.

[RuralGuy]

You have a many-to-many (m:m) relationship here and is usually best handled as two 1:m relationships. I've downloaded your db and will have a look/see but in the mean time here's some links you might find useful:
http://office.microsoft.com/en-us/ac...010098674.aspx
http://articles.techrepublic.com.com...1-5285168.html
http://www.databasedev.co.uk/many_to_many_example.html

[RuralGuy]

While I'm at it, here's another link that you should read: http://www.mvps.org/access/lookupfields.htm

[captgnvr]

D/RG

Went thro the link and read about the evils. Will try to eliminate using look up fields in tables once I get the hang of how to do without it. May be with this project guidance this will also be learnt.

Now reading the other links and will revert.

Brgds/captgnvr

[RuralGuy]

You can accomplish the Lookup with ComboBoxes on a form and that is just fine, just not at the table level.

[captgnvr]

D/RG

Read all the links and settled down to 'databasedev' which has the example just suiting my need. Have downloaded and trying to read up. In the mean time some tips for my file attached will help save time for me:P.

Really, I am sitting up whole day waiting for ur response just to complete the task. Nice to know that you are working on it. Will go for dinner and come back and read up again on the databasedev example.
brgds/captgnvr

[RuralGuy]

Don't you have a boat to drive or something? I'm working on a form for you.

[captgnvr]

D/RG
As I hv mentiond before, this is a storage tanker and stationery. So I only have to ensure all goes on well and is in ship shape. Now doing some research on "many to many" or m:m as u put it and trying to get familiar with it. In the mean time I hope to get some guidance on the actual file I sent. brgds/captgnvr

[RuralGuy]

Thanks for the update. Still working on it. Another cup of coffee is in order here.