Design with no experience

[.:RoKsTaR:.]

Hi There,

I have no experience with access, so please let me know if this task is too
big.

Is it feasible for me to develop a databse for my private music students
that tracks and reports on the following:

Contact Info
Individual and overall Attendance
Individual and overall Payments
Material covered in each lesson

Cheers!

[Jeff Boyce]

That REALLY depends...!

Here are four areas I suggest folks understand if they intend to build an
application in Access:
1. Normalization & relational database design
2. Access tips/tricks
3. Graphical user interface design
4. Experience building applications

To the extent that any/all of these are new to you, plan on spending time
working your way up the learning curve(s).

So, it depends ...

How much time do you have to get this done?

How much do you need/care to learn?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

".:RoKsTaR:." <.:RoKsTaR:.@discussions.microsoft.com> wrote in message
news:9355BD4B-D0BA-4329...@microsoft.com...

[.:RoKsTaR:.]

I'm usually pretty quick at learning new programs. I'm constantly having to
learn new ones being in the music business since there's new programs almost
every month.

I think it would make my business a lot easier to run, so I have no problem
devoting the time to it. That being said, I'm not sure where to start...I
did a tutorial on the MS Access page, but what next?

"Jeff Boyce" wrote:

> .
>

[Fred]

It's a little different. From a learning standpoint, Access is more like a
big toolbox than a program. And besides just learning what the tools and how
to use them, yo have to start learing architecture (information and db
structures) to decide what to build with those tools and how to build it.

Sometimes it good to structure/facilitate/move along your learning by trying
to simultaneously. So here's something like that. Try to:

Decide what the fundamental "entities" are that you are trying to database.
My guess is that they are

T1. People
T2. Instances of payment for instruction (of that person)
T3. Instances of a class being scheduled for a person

Make a table for each of those entities, include an autonumber primary key
in each table. For people name the PK Field "PeopleID"

Add an integer (not autonumber) field "PeopleID" in tables T2 & T3.
(these are "foreign keys) and link each of those fields to the "PeopleID"
field in T1

Add fields for each of the attributes that you want to record about your
entities. Here are a few of the more abstract ones:

T1
Current Student? (yes/no)

T2

T3
Showed up? (yes/no)
Description of material covered.

- - -

Now make datasheet style forms for T2 and T2. These will be used as subforms

Make a "one record per page" style form for T1. Then put the T2 & T3
subforms into it.

Then make reports that summarizes T2 records, grouped by Person
Then make reports that summarizes T3 records, grouped by Person

Of courses, explainiing in detail how to do the above is a book, not a post,
but trying to do the above should help driver/direct the learing process.

[John W. Vinson]

On Thu, 12 Nov 2009 12:32:10 -0800, .:RoKsTaR:.
<RoK...@discussions.microsoft.com> wrote:

>I think it would make my business a lot easier to run, so I have no problem
>devoting the time to it. That being said, I'm not sure where to start...I
>did a tutorial on the MS Access page, but what next?

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

The last three specifically. If you're new to database design, Crystal's video
is a great start.
--

John W. Vinson [MVP]

[Jeff Boyce]

I'm with Fred & John ... Access is not a "bookcase" (which everyone knows
how to use), its not ANY kind of furniture, its a tool set you use to build
furniture. Please re-check my 4 learning curves. Using Access successfully
may involve working through all 4.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

".:RoKsTaR:." <RoK...@discussions.microsoft.com> wrote in message
news:9930ECEE-27F7-4A4E...@microsoft.com...

[.:RoKsTaR:.]

Wow! Thanks, I'm pretty sure I know how to do all that stuff. I'll get
started right away and be back with any questions I have ;)

Thanks again!

"John W. Vinson" wrote:

> .
>

[.:RoKsTaR:.]

Thanks again, I'll definitely have some things to study and practice on :)

"Jeff Boyce" wrote:

> .
>

[Fred]

Same thing as I wrote B4 with some of my crucial typos / omissions corrected.

It's a little different. From a learning standpoint, Access is more like a
big toolbox than a program. And besides just learning what the tools and how

to use them, you have to start learning architecture (information and db
structures) to design what to build with those tools.

Sometimes it's good to structure/facilitate/move along your learning by
trying to do "make" something specific simultaneously with your reading. So

here's something like that. Try to:

Decide what the fundamental "entities" are that you are trying to database.
My guess is that they are

T1. People
T2. Instances of payment for instruction (of that person)
T3. Instances of a class being scheduled for a person

Make a table for each of those entities, include an autonumber primary key
in each table. For people name the PK Field "PeopleID"

Add an integer (not autonumber) field "PeopleID" in tables T2 & T3.
(these are "foreign keys) and link each of those fields to the "PeopleID"
field in T1

Add fields for each of the attributes that you want to record about your
entities. Here are a few of the more abstract ones:

T1
Current Student? (yes/no)

T2

T3
Showed up? (yes/no)
Description of material covered.

- - -

Now make datasheet style forms for T2 and T2. These will be used as subforms

Make a "one record per page" style form for T1. Then put the T2 & T3
subforms into it.

Then make reports that summarize T2 records, grouped by Person
Then make reports that summarize T3 records, grouped by Person

Of course, explaining in detail how to do the above is a book, not a post,
but trying to do the above should help drive/direct the learning process.

[.:RoKsTaR:.]

So I watched crystals video and went through some of the other material and
have been noodling around with the program and existing databases to help
figure things out. In crystals video, she talks about putting people,
addresses, and phone numbers in their own table and linking them by their
unique id or key number. Existing templates do this all in one table, whcih
to me makes data entry easier, but I'm not sure.

Can anyone clarify if separate tables would make the data entry portion more
time consuming? Can you have one form that enters data into many tables?

Side note : A friend of mine told me he can get me some training dvds from
his office library, so I think that's my next step ;)

[John W. Vinson]

On Sat, 14 Nov 2009 05:59:01 -0800, .:RoKsTaR:.
<RoK...@discussions.microsoft.com> wrote:

>So I watched crystals video and went through some of the other material and
>have been noodling around with the program and existing databases to help
>figure things out. In crystals video, she talks about putting people,
>addresses, and phone numbers in their own table and linking them by their
>unique id or key number. Existing templates do this all in one table, whcih
>to me makes data entry easier, but I'm not sure.

One table makes data entry easier... but is also more limited. Some people use
more than one address; many people use more than one phone (home, office,
mobile for example). It's a judgement call based on how the database will be
used - some databases will be fine with one table (with its limitations),
others may need separate tables.

>Can anyone clarify if separate tables would make the data entry portion more
>time consuming? Can you have one form that enters data into many tables?

Sure. A Form based on Contacts with Subforms based on the Address and Phone
tables. There'll actually be three forms, but they'll all be onscreen and
editable at the same time.

>Side note : A friend of mine told me he can get me some training dvds from
>his office library, so I think that's my next step ;)

If they're good ones....

[.:RoKsTaR:.]

I talked to him yesterday and he said that he can get me something called
trainsignal from their library. He says it's pretty intense and he thinks
his company used it to certify some employees for access...does that sound
right?

[.:RoKsTaR:.]

He says it something called trainsignal, but didn't know much about it. From
what I can tell, it looks pretty thorough :)

[.:RoKsTaR:.]

Whoops ;) I dind;t think that first post showed up so I did another
one...sorry ;)

[Fred]

I have not watched Crystals (even through she is an Access teaching goddess)
but I suspect that you misinterpreded something from it, and, either way, you
post indicates that you have yet to really understand the important
foundation items.

(The table design that ends up as ) linking tables is to to properly handle
"one to many" and "many to many" type relationships between the entities that
you are databasing.

If the entity that you are databasing in your address book is just People
(i.e not organizations, companies, etc. lists of coins that those people
own ) chances are that putting all of their info into one table (i.e. not
separate linked tables) is the best way for you to go.

[.:RoKsTaR:.]

No I get the one to one, one to many, and many to many relationship. My
question was, does it make data entry more time consuming to have the info
in many tables over one. Needless to say, John clarified it for me and I'll
probably do many tables so I don't have to make changes later on ;)

[AccessVandal via AccessMonster.com]

In the meantime you also can look at other Data model from another
contributer Barry Williams site here.

http://www.databaseanswers.org/data_models/index.htm

look something under music or student (I guess).

:RoKsTaR:. wrote:
>No I get the one to one, one to many, and many to many relationship. My
>question was, does it make data entry more time consuming to have the info
>in many tables over one. Needless to say, John clarified it for me and I'll
>probably do many tables so I don't have to make changes later on ;)

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1

[BruceM via AccessMonster.com]

Convenience of data entry depends on how things are set up. There may be one
phone number or there may be several. You could have Phone1, Phone2 etc.
fields in your main table, but you have to guess about the maximum number you
will need, and you are unlikely to guess correctly. With the phone number
information in a related table you need a continuous subform or other
mechanism to display the phone numbers. If you make the subform big enough
to display three records (phone numbers), in some cases there will be empty
fields that take up room on the form, but if there are four or more phone
numbers it is no problem, as you can scroll up or down as needed. There are
other options, such as a phone number subform on a tab page. My point is
about the overall structure, not the details of the interface. If you allow
for unlimited phone numbers you can find a way that makes sense for entering
and displaying the numbers. However, if you allow for three numbers in the
main table there is no good way to add a fourth.

I would keep in mind how often such information needs to be changed. A
somewhat inconvenient data entry approach may be an acceptable trade-off for
a convenient display.

For payment, attendance, and materials there is no reasonable alternative to
related tables. The details depend on considerations such as the
relationship between materials and attendance. If you want to associate
specific material with specific lessons (attendance) you may approach it
differently than if you just want a listing of materials covered or books
purchased or some such.

.:RoKsTaR:. wrote:
>No I get the one to one, one to many, and many to many relationship. My
>question was, does it make data entry more time consuming to have the info
>in many tables over one. Needless to say, John clarified it for me and I'll
>probably do many tables so I don't have to make changes later on ;)
>

>> I have not watched Crystals (even through she is an Access teaching goddess)
>> but I suspect that you misinterpreded something from it, and, either way, you

>[quoted text clipped - 9 lines]

>> own ) chances are that putting all of their info into one table (i.e. not
>> separate linked tables) is the best way for you to go.

--