Help, please

[Guest]

I am trying to create a database that will allow me to put all the names of
the people in my command on the left side of the page. Then all the
qualifications that we can get across the top. I would like to be able to
track each qualification column ie. If my boss wants to know how many people
we have qualified in Military Free Fall, I would like to be able to type in
the qualification and get a percentage and the names of all qualified. Is
there a template already created for something like this? If not, how can I
create one? Any guidance would be greatly appreciated!!

 

[Rob Parker]

If you really want to do it as you describe, use Excel and investigate the
Autofilter tool. Or some simple formulae to give your percentages.

What you are describing is a typical spreadsheet, and is completely wrong
for a relational database. You would be storing data (ie. a particular
qualification) as a field name - precisely what you should not be doing.

Take a large step back; read some basic material on design of relational
databases, in particular, normalisation. What you describe, in terms of
data so far, would be held in a table with two fields, PersonName and
Qualification. You can then build queries to return the information you
want, in the form you want.

Here's a link to a page with lots of references for starters:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

HTH,

Rob

 

[Guest]

wlstocker - what you need is an 'ofd', an Officer Fascination Device.

Here's how to do it:

1. Create a form and insert a textbox (probably good to source it from your
table but its not entirely necessary)
2. In the control source of the textbox, put in a dcount - here's an example
=DCount("
[Qualification]![FileReference]","Qualifications","[Qualification]![Military
Free Fall] =-1") {presuming you are using yes/no fields to indicate if they
are qualified or not}

[Qualification]![FileReference] = the row you will be counting
"Qualifications" = the name of the table
"[Qualification]![Military Free Fall] =-1" = the qualification you want to
count

3. Rinse, wash, repeat for your other qualifications - this'll get you to
Corporal

4. For additional OFD, create queries to pull out the information relating
to Military Free Fall etc, - here's an example

(select query)

[name]

[Military Free Fall]
Criteria = yes

(lets call this query - military free fall query)

and then write a macro with only 2 lines of code:
open query: military free fall query
maximise

Then attach the macro (lets call it the - military free fall macro) to the
onclick property of the textbox. You'll make Sergeant for this.

Want to get to Warrant Officer ?

I've just shown you how to get a dcount of a 'set' number of soldiers,
unbind that (ie remove any qualifications you want to count) to get the total
number of troops in the database, then you can do simple maths to work out
the percentages of each qualification, all on the same form.

To really bend them around your finger, let them add a soldier or 2 and
watch the numbers go up and down...

Mæl.

 

[Guest]

Rob Parkers advice is absolutely correct on so many levels in a database
purity and proper database design sense.

The solution I proposed is on so many levels an incorrect database - yet
this is a solution towards a military application of logic. 'Wlstocker' faces
additional problems in showing database design logic in a military logic
environment (aka his conglomerate of supervisors) - I'll hint towards the
little footnote that says words to the effect of 'in some cases a
de-normalised database is more appropriate' in most training material on the
subject of normalisation.

Also, in being <i>absolutely</i> correct, the advice may be going over the
technical capabilities of the audience. We all need to create an unnormalised
database at some stage of our careers to realise the errors of our ways,
before we can learn to create 2nd generation normalised databases (where 1st
generation databases followed conventional file structure).

In this case, for a military unit (section, platoon, company) by
qualification (max 20) the database will only ever contain (10,30,100) lines
of data - hardly worth leaving excel over.

The request - and my response, was in my perception tailored to the
audience. They don't want to use excel on account of its a boring product. No
OFT in excel.