Access 2003 database help

[Guest]

Alright here is the scenario...........we have a database (that nobody knows
how to fix) that tracks when all the people in our Group (1100 people) take
two tests...................it is a annual requirment

we (my job) is to input them into the database (that someone created long
ago) with the new date they took it..........problem #1 is we have to delete
their 2006 test date first before we can add the new 07 date or they still
show up on our reports (that we generate from the same database) as being
overdue even though we put in the 07 date......I was hoping there is a way to
edit it so the 07 date cancels out the 06 one......not sure what to do
there???

Problem 2......we have a button that we click that generates a report of who
is due within 90 days.......problem is that if they some how pass the 90 days
they dont show up on our report......they just get lost in the system.....and
with 1100 people its hard to individualy look everyone up to check it
manualy..........I was hoping there was a way to edit it to generate not only
who is due in 90 days but who is just flat our expired???

 

[Jeff Boyce]

Have you considered setting the old db aside (except as a source for the
folks) and just creating a new one?

There should be no (good) reason in a well-normalized Access database for
having to throw away last year's records before entering this year's data.

Step back for a second from what you have now, and how you have to use it.
Start with the things about which you want to record data ... it sounds like
you have People, Requirements, and PeopleSatisfyingRequirements. Once you
have those categories (in relational database design, these are called
"entities"), you need to figure out how, in your situation, each of these
might relate to the other(s). These are called "relationships".

I'll guess that one person can satisfy multiple requirements (and again and
again over multiple years). I'll also guess that one requirement can be
satisfied by multiple (?!1,100) people. That means these two "entities" are
related M:M (many-to-many). In a relational database, showing that
relationship requires a third table -- that's the
[PeopleSatisfyingRequirements] table I mentioned above. That third table
could be as simple as:

trelPeopleSatisfyingRequirements
SatisfyingID
PersonID
RequirementID
DateSatisfied

Now, when you need to see all the folks who have satisfied Requirement1 or
Requirement2 in the year 2007, just query that third table!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

wow..............it sounds easy but I have no idea what you said............I
am really really new to this program which is y im confused as to y they
would put me in charge of it...............this may sound really really dumb
but is there a way to have a database built and just type the data in
myself.........lol

Have you considered setting the old db aside (except as a source for the
folks) and just creating a new one?

There should be no (good) reason in a well-normalized Access database for
having to throw away last year's records before entering this year's data.

Step back for a second from what you have now, and how you have to use it.
Start with the things about which you want to record data ... it sounds like
you have People, Requirements, and PeopleSatisfyingRequirements. Once you
have those categories (in relational database design, these are called
"entities"), you need to figure out how, in your situation, each of these
might relate to the other(s). These are called "relationships".

I'll guess that one person can satisfy multiple requirements (and again and
again over multiple years). I'll also guess that one requirement can be
satisfied by multiple (?!1,100) people. That means these two "entities" are
related M:M (many-to-many). In a relational database, showing that
relationship requires a third table -- that's the
[PeopleSatisfyingRequirements] table I mentioned above. That third table
could be as simple as:

trelPeopleSatisfyingRequirements
SatisfyingID
PersonID
RequirementID
DateSatisfied

Now, when you need to see all the folks who have satisfied Requirement1 or
Requirement2 in the year 2007, just query that third table!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Alright here is the scenario...........we have a database (that nobody
knows
how to fix) that tracks when all the people in our Group (1100 people)
take
two tests...................it is a annual requirment

we (my job) is to input them into the database (that someone created long
ago) with the new date they took it..........problem #1 is we have to
delete
their 2006 test date first before we can add the new 07 date or they still
show up on our reports (that we generate from the same database) as being
overdue even though we put in the 07 date......I was hoping there is a way
to
edit it so the 07 date cancels out the 06 one......not sure what to do
there???

Problem 2......we have a button that we click that generates a report of
who
is due within 90 days.......problem is that if they some how pass the 90
days
they dont show up on our report......they just get lost in the
system.....and
with 1100 people its hard to individualy look everyone up to check it
manualy..........I was hoping there was a way to edit it to generate not
only
who is due in 90 days but who is just flat our expired???

 

[Jeff Boyce]

Robert

Most folks understand what a spreadsheet is for, so picking up Excel is, to
start with, a matter of figuring out how to do things they already do (add
up columns of numbers, etc.).

Most folks understand how to write something, whether by hand or ..., so to
start with, using Word means learning a new way to do something they can
already do (e.g., scratch out some words and replace them with other words,
etc.).

Most folks do NOT understand relational databases, so picking up Access
takes a bit of a steep learning curve. Access is less like a familiar
bookcase (you know what to do with it) and more like a set of woodworking
tools (you have to have a plan in mind before you even start cutting...).

There'd be no guarantee that any pre-built, already existing
database/application did what YOU need it to do unless you build it yourself
or work closely with someone who builds it for you (...hmmm, still, may not
be guarantees THOSE ways, either <g>).

It would be nice if you could just push a button and the program would
appear and do what you want ... I worked with a fellow who described that as
.... "Hollywood computer" <G!>

If you have the time to spend working your way up the learning curve, you
will find other opportunities to use Access. Feel free to post your
questions back here in the newsgroups as you go along.

If you don't have time to spend, you might need to locate someone who builds
systems for a living. Your application will (probably) get built quicker,
but it will certainly cost more.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thank you for your timely and informative response. I guess I will start
playing with the program and see what happens, once again thank you for the
information

 

[Larry Linson]

Robert, "just playing with the program" implies that you will be guessing
both at what the software is supposed to do, and at how it is supposed to do
it, and trying to see if your guesses were correct -- a mode of learning
that is frustrating at best, and completely ineffective at worst. Instead of
just playing, you might start with a good self-study book on Access.

For the rank beginner, "Microsoft Access <versionnumber> Step by Step"
starts you off "easy", but may not go as deep as you need. Also starting off
at the beginning and going deeper is "Microsoft Access 2003 Inside/Out" by
John Viescas or "Microsoft Access 2007 Inside/Out" by John Viescas and Jeff
Conrad, all published by Microsoft Press or Microsoft Learning. Another
series of books that have been good in past versions, though I haven't
reviewed the most recent, were the "Special Edition Using Microsoft Access
<version>" by Roger Jennings, published by Que.

I don't believe you said what version of Access you are using... Access 2003
Help is better than the Help in Access 2002 or 2000, but online help is
primary, so you need a high-speed connection... and that Help tends to be
more _reference_ than tutorial, so really is not as good a learning tool as
you need.

There are also some good tutorials at http://office.microsoft.com that are
free.

But, most companies will provide some assistance in learning a new skill if
they expect you to take over and maintain a database requiring that skill,
unless that knowledge was part of your job requirement when you were hired.
A few good books and time to read those and to work through online tutorials
would be a good start at "some assistance."

Almost certainly, with a little re-design and revision, you would be able to
use the same basic database to provide the 2007 information, without having
to remove or overlay the 2006 data. Note the "almost" because nothing is
_absolutely certain_ about a database that someone else created and we've
never examined.

Larry Linson
Microsoft Access MVP