Help real newbie to databases

[Max]

Hi, I am new to the world of databases, relational, flat, and any others. I
have been at my part-time job for a year now, thrown into the world of an
existing Access d/b. I now know that there's a lot of things wrong with this
d/b, and that I do not know enough to figure out how to fix it. This is a
non-profit organization, and they have no budget even for a good Access book.


After surfing around this site's groups, and checking out related links, I
have come up with some ideas, and hope I can use y'all as a sounding board.

First of all, can I handle this with no coding? Just using the design
options of Access itself? I have very limited computer knowledge; basically
just word processing experience.

My second question (for now, **wink**), is a "child data table" the same
thing as a subdatasheet? (Don't worry; I'll be back with more questions.)

Thank you all in advance, In Peace, Max

 

[John Vinson]

Hi, I am new to the world of databases, relational, flat, and any others. I
have been at my part-time job for a year now, thrown into the world of an
existing Access d/b. I now know that there's a lot of things wrong with this
d/b, and that I do not know enough to figure out how to fix it. This is a
non-profit organization, and they have no budget even for a good Access book.

Well, that's one nice thing about these newsgroups: the price can't be

After surfing around this site's groups, and checking out related links, I
have come up with some ideas, and hope I can use y'all as a sounding board.

First of all, can I handle this with no coding? Just using the design
options of Access itself? I have very limited computer knowledge; basically
just word processing experience.

That depends on what "this" is. You can get a great deal out of Access
with no VBA coding at all; there are some things that are easier with
some code; there are some other things (usually rather advanced) for
which code is obligatory.

My second question (for now, **wink**), is a "child data table" the same
thing as a subdatasheet? (Don't worry; I'll be back with more questions.)

Not really. A "subdatasheet" is a method of displaying the data in a
child data table. There are actually two separate tables, with a
relationship defined between them.

I personally don't like subdatasheets much, and I don't like Lookup
fields (another way of pulling data from a second table) at ALL.
Datasheets are *not* good ways to interact with data; they're best
reserved for design and debugging. Any real interaction with your data
should be on Forms, which fortunately Access makes pretty easy to
create and manage.

The first place to start, though, is with your Tables and their
Relationships. Identify the "entities" - real-life things, persons, or
events - of importance to your application; each type of Entity will
have its own table. Identify each entity's Attributes - discrete,
nonrepeating, atomic "chunks" of information about that entity; each
attribute will be a field in that entity's table. Identify how your
entities are related; for example, you might have a Families table and
a People table; each Family record would be related to one or more
People, but each Person would (for the purpose of this hypothetical
application) belong to one and only one Family.

If you'ld like to describe the nature of the data you're managing,
we'll be glad to try to help set it up!

John W. Vinson[MVP]

 

[Max]

Thanks, John, for your answer in lay terms. I work in the Teen department of
a community center, and the database includes teen Students in grades 6 thru
12, the Events/Activities we hold, and other Organizations the kids could be
members of that might be able to work together with ours. Currently, there
is a main Students table, an Events table, and a Transactions table. Someone
else before me, set up this format. The Students table is fairly
self-explanatory (kids' first/last names, ID# is PK, addresses, home phone
numbers, grade, date of birth, email, etc., but I feel limited in that I
would like to be able to do the following:

a), automatically "graduate" the kids who leave the system at the end of
their grade 12 (and I don't want to just delete them, I think their records
should go somplace else but remain retrievable);

b) automatically increase all the other kids' grades (both option to be done
at the end of June);

c) have a sub-table (datasheet? child table?) with Parent/Guardian Info,
stuff that may need to be accessible but not often, such as Parent/Guardian
work info, cell phone numbers, emails, etc.

d) be able to see by which Event/Activity each child entered our system, see
which Events/Activities each child attended (again, a child table?), and at
other times do this by zip code or county.

e) be able to break down each Event/Activity's attendees by zip code,
county, &/or Organization (and sometimes more, like grade or age).

The Events/Activities table just lists each activity; as each new activity
starts to be planned, it goes onto this table with a sequential code number,
the title, and date of the event.

The Transactions table lists each students' full name, ID Number (PK from
the main Students' table), and activity code (from the Events/Activities
table). Completing this table is what really drives me nuts. I have to use
the Ctrl+F to search the Students table to see if each new registrant is
already in our system, then if they are new, I have to add a new record. New
or not, I have to make a note of their ID number. Then I have to go to the
Transactions table to type in the new record here: the ID number, a field
with their full name (in the Students table, we have the first and last names
in separate fields), and the activity code number.

One thing I t hink is a problem is that for some activities, we ask for
certain registration information that is not needed for other types of
activities (such as, "is bus transportation needed?" This information needs
to be recorded only for certain events and not others.

I found a software site on the internet that is almost exactly what we need
(it does some things that we don't need), but I know we don't have a budget
for it (plus, I don't think it can be customized). It looks like it's based
on Access, maybe an earlier version than what I have, and I just visit there
occasionally and drool over it, hoping some insights will come to me that
will help me figure out how to create the same sort of things. Don't know if
I can post the link here, but it would give you an idea of how I came to know
what I want.

The way we are currently set-up, this "Transactions" table is the only way
to tie in the Students with the Events/Activities, and this has to be done
manually.

Data entry could also be made easier, I think, such as when I start to type
in a city, there could be an auto-complete like in Word; and after the city
is typed, the auto-complete could "suggest" the state, zip code, area code,
and county. And when I get to the Organization field, a drop-down box could
appear offering a menu of Organizations other Students in that zip code
belong to. And isn't there an easier way to find out of a student is already
in our system? To me, over 3500 records is a lot, but I am finding out that
it's not. Like maybe when I go to type the last name (which I could move to
be the first field after the PK), I get a message asking me if this is new or
that "one of the following records may be a duplicate of the one you are
entering; check the list to ensure no duplication occurs", or is this just a
pipe dream?

Thanks again, John.

In Peace, Max

 

[John Vinson]

Thanks, John, for your answer in lay terms.

Max, I'll need to print out your long question and mull it over.

Just a suggestion on newsgroup etiquette: it's often much more
productive to ask individual questions for individual problems, rather
than posting an intimidating long batch all at once. But I will reply
after I've had a chance to look this over.


John W. Vinson[MVP]