Managing attribute requests

[Debra]

Hi,

I am a minimally knowledgeable Access user and have been asked to track our
programmers' requests for permission to use various client attributes from a
central database. I need to run a report of each element on the submission
form and be able to sort it in various ways. My problem is how do I create a
way to list each attributes requested by the programmer in a report that can
be sorted when they want to know how often and who is using one particular or
every attribute? I thought a list box would be fine but how would it work if
the programmer requested 5 or more attributes? How would I display the list
box results in the report? Please help!! I'm stumped.

Thanks a million,
D.

 

[Jeff Boyce]

Debra

Start with data (that's where Access starts), then work up the form/report.

What pieces of data are you collecting? In what tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Debra]

Hi Jeff,

I'm collecting about 25 pieces of data and storing it in two tables. One
table lists the info of the requester (name, department, application name,
etc) the other tracks the review board action (date submitted, approved,
etc.) The attribute data request would consist of personal info like
username, userid, address, surname, etc...

D.

 

[Jeff Boyce]

Debra

You have programmers, you have client attributes, and you have
attributes-requested-by-programmers. Three tables, not two.

A request-by-programmer for an attribute goes into the third table.

You want how many times attribute = #17 has been requested? Run a query on
that third table. You want how many attributes programmer #2 has requested?
Run a query on that third table!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Debra]

Hey Jeff,

I've been experimenting....I tried to create a combo box on the data input
form but can't figure how to identify each attribute separately or indicate
that an individual desires multiple attributes. Anyway..

Let's see if I understand your suggestion. Remember now I'm a novice....
Seems like you're saying I should create a third table with two columns -
the name of the requester and the name of the attirbute requested. So, this
table would list the name of the requester 10 times if that person requested
10 different attributes? If not how would the request of each attribute be
sorted and displayed in a report? You see, each 'request' attributes is
related to an application and has a submission number associated with it -
Would I make 'requester' a key field in the third table and create a
relationship between this and the table that contains the submission number
and other info related to this request? Someone else mentioned a sub-report,
would that better serve the purpose?

Am i making this too complicated? Appreciate any direction.

Thanks,
Debra

 

[Jeff Boyce]

Debra

see comments in-line below...

Hey Jeff,

I've been experimenting....I tried to create a combo box on the data input
form but can't figure how to identify each attribute separately or
indicate
that an individual desires multiple attributes. Anyway..

One form for the programmers, a second form for the RequestedAttributes.
That second form will be embedded into the first as a "subform", linked by
the ProgrammerID fields in each (see below for more explanation). On that
second form, you'll probably only need a single combobox, based on the
"Attributes" table. The combobox will be bound to the AttributeID field in
the RequestedAttributes table (*again, see below*). You may decide that you
also want to keep track of when the attribute was requested. Add a field in
the table and a control on the subform.

Let's see if I understand your suggestion. Remember now I'm a novice....
Seems like you're saying I should create a third table with two columns -
the name of the requester and the name of the attirbute requested.

Close, but consider this scenario ... two programmers named Bill Smith?!
Instead of using the name of the programmer and the name of the attribute,
in each of THOSE tables, use an ID field (ProgrammerID, AttributeID) to hold
a unique identifier. The Access Autonumber datatype will work for this.
Then, in each of those tables, also include ProgrammerFirstName,
ProgrammerLastName (in tblProgrammer) and AttributeTitle (in tblAttribute).

So, this
table would list the name of the requester 10 times if that person
requested
10 different attributes?

Again, close (see above). Actually, it would contain the ID of the
programmer 10 times (in ten rows) and ten different AttributeIDs (one per
row). While this seems redundant, you can query this table to find all
Programmers (by ID) who requested attribute # 17 (by ID).

If not how would the request of each attribute be
sorted and displayed in a report?

When you get ready to display the results to humans, create a query that
joins the tblProgrammer and the tbl Attribute to the trelRequestedAttribute,
by their respective ProgrammerID and AttributeID. Now you can show which
programmer goes with ProgrammerID = 7 and which Attribute goes with
AttributeID = 17.

Then use this query as a source for your report.

You see, each 'request' attributes is
related to an application and has a submission number associated with it -
Would I make 'requester' a key field in the third table and create a
relationship between this and the table that contains the submission
number
and other info related to this request? Someone else mentioned a
sub-report,
would that better serve the purpose?

I'm not clear on the relationship among application, submission number,
programmer, attribute and requestedattribute. More explanation on how these
relate one to another?

Am i making this too complicated? Appreciate any direction.

I don't believe YOU made this complicated. I do believe the relationships
among the entities IS complicated.

Thanks,
Debra

Regards

Jeff Boyce
Microsoft Office/Access MVP