Base combo box off of yes/no

[idtjes3]

Hello,

I have a combo box that displays certain task for a company. Each task is
primarily specific to a certain department, however, one department may be
able to do the tasks of another. For instance a person in the office can do
drafting and may make molds. While someone in the shop can make molds but
would never do drafting.

The way i was thinking about setting this up was have a table with a field
that lists the task then in the fields next to it list my departments such as
accounting, engineering, shop, etc with a yes or no box. so for instance, say
i list Task = Mold Making, then the people who have access to that would be,
Engineering = Yes, Shop worker= yes. I already have an index table with all
the departments and employees associtaed to them. on the time card in a combo
box i have the employee name in one column and their department in the next.
I just need a way for the task combo box to look at the employee department,
refer to the task table, and see if its checked as a "yes" or not. Any ideas?
ive been to http://www.mvps.org/access/forms/frm0028.htm but im not sure how
to apply this with the use of yes/no check boxes. Thanks

 

[Jeff Boyce]

That sounds like just the kind of approach to use ... if you were limited to
a spreadsheet! You won't get very good use of Access' relationally-oriented
features/functions if you insist on feeding it 'sheet data.

With the design you are proposing, what happens when your company adds a new
"department" (or consolidates and removes one)? First you'll have to modify
your table. Then any queries that relied on it. Then any forms, reports,
macros, procedures, ...! That sounds like a maintenance nightmare!

A more-relational way of looking at this is a (new) table that holds any/all
valid pairs (WHO can do WHAT). And since working directly in the tables is
not considered "user-friendly", you'd probably want to build a main form
that lists the WHOs, and a subform that points to that new table I mentioned
and lets you (and your users) pick the WHATs.

If you don't have experience with relational databases and/or normalization
and/or MS Access, you may have a bit of a steep learning curve coming up (or
2 or 3!).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[idtjes3]

Thanks for your response Jeff. The reason i wanted to do my approach was our
company is pretty set in tasks and will not be adding any in the near future
( or ever really). However, I understand the benefits of the method you
suggested.
I can kind of grasp the concept your trying to tell me but its hard to
really understand what you mean without an example. Do you have any "for
instances" I could look at to get a better handle on layout and application?
I'm not sure how I would set your method up. I am familiar with
relationships and normalization, however I'm no expert.

 

[Jeff Boyce]

Access comes with the Northwind database as a sample/example. You can
probably find something like this there.

And Microsoft offers "templates" at its website ... I'll bet you can find
one that does something similar (maybe not an exact match, but using the
approach).

Regards

Jeff Boyce
Microsoft Office/Access MVP