My first database (long)

[Jack Sheet]

Hi all - feeling my way trying to create first Access database.
Background:
I created a database in Excel, which for various reasons would be better
done in Access (simultaneous access by multiple users, size of database,
data integrity/stability etc). I am well familiar with Excel and VBA for
Excel, but never touched Access before now.

As I see it the problem can be broken down into three stages that should be
tackled in order:
1) Designing the tables, ie the way that data is organised in the database
2) Populating the tables by importing the data from the existing Excel
database
3) Designing the standard reports.

I expect to have problems with 3 simply through unfamiliarity, but I leave
that on hold for the moment and not the subject of this post.

I have problems with 2 because the tables in the Access database will not be
identical in design to the tables in Excel. I expect I shall have to import
the data using some VBA routine, and I may be asking for help on that in the
future, but I leave that on hold until I sort out the problems with step 1.
I did try by experiment to import the excel database "as is", with a view
then to manipulating the design, but could not see how to do that.

My first question for you guys is:
Can I create a table in more than 2 dimensions (and would I be better off
trying a different approach)?
The reason that I think that I desire this feature is explained as follows:

The database contains client records. For each client there are some fields
of permanent information such as date of birth and description of recurring
tasks. For each recurring task there may be a number of outstanding tasks
to be completed, and that number will vary from client to client. For each
task the state of completion may be a variable.

Thus, I may have (eg) two clients. For each client, among other things we
complete tax returns. For client "A" there are three tax returns
outstanding (2002, 2003 and 2004), and for client "B" there are two tax
returns outstanding (2003 and 2004).

If I just have a 2-dimensional table, one of the fields might be "Tax
Returns", but a single cell in that field cannot contain the details of all
outstanding tax returns. For that information to be stored the table would
also have to have "depth". I could reserve additional fields in the 2nd
dimension for each year, but there is potentially no limit to the number of
years outstanding, and even then I do not see how I could use sub-fields to
record the separate state of completion of each return - that would require
another (ie 4th, dimension).

This feels like a wheel that has already been invented, so wondering if
anyone has any advice about the general approach that I should be adopting.

The existing Excel database has three (main) worksheets. One worksheet
contains a list of clients (in rows) and their permanent information (in
columns). The second worksheet contains a list of repeating tasks for each
client in the client worksheet (it also contains the details of the first
task to be created if there are none created, and the last task created if
there have been tasks created). The final worksheet contains a list of
actual tasks (one row per task, potentially several rows per client). All
of the permanent information contained in the Clients worksheet is repeated
in the other two worksheets by way of VLookup, so you can imagine that it
takes a while to recalculate when you have a lot of records.

Any pointers appreciated. Thanks.

 

[Brian]

Hi all - feeling my way trying to create first Access database.
Background:
I created a database in Excel, which for various reasons would be better
done in Access (simultaneous access by multiple users, size of database,
data integrity/stability etc). I am well familiar with Excel and VBA for
Excel, but never touched Access before now.

As I see it the problem can be broken down into three stages that should be
tackled in order:
1) Designing the tables, ie the way that data is organised in the database
2) Populating the tables by importing the data from the existing Excel
database
3) Designing the standard reports.

I expect to have problems with 3 simply through unfamiliarity, but I leave
that on hold for the moment and not the subject of this post.

I have problems with 2 because the tables in the Access database will not be
identical in design to the tables in Excel. I expect I shall have to import
the data using some VBA routine, and I may be asking for help on that in the
future, but I leave that on hold until I sort out the problems with step 1.
I did try by experiment to import the excel database "as is", with a view
then to manipulating the design, but could not see how to do that.

My first question for you guys is:
Can I create a table in more than 2 dimensions (and would I be better off
trying a different approach)?
The reason that I think that I desire this feature is explained as follows:

The database contains client records. For each client there are some fields
of permanent information such as date of birth and description of recurring
tasks. For each recurring task there may be a number of outstanding tasks
to be completed, and that number will vary from client to client. For each
task the state of completion may be a variable.

Thus, I may have (eg) two clients. For each client, among other things we
complete tax returns. For client "A" there are three tax returns
outstanding (2002, 2003 and 2004), and for client "B" there are two tax
returns outstanding (2003 and 2004).

If I just have a 2-dimensional table, one of the fields might be "Tax
Returns", but a single cell in that field cannot contain the details of all
outstanding tax returns. For that information to be stored the table would
also have to have "depth". I could reserve additional fields in the 2nd
dimension for each year, but there is potentially no limit to the number of
years outstanding, and even then I do not see how I could use sub-fields to
record the separate state of completion of each return - that would require
another (ie 4th, dimension).

This feels like a wheel that has already been invented, so wondering if
anyone has any advice about the general approach that I should be adopting.

The existing Excel database has three (main) worksheets. One worksheet
contains a list of clients (in rows) and their permanent information (in
columns). The second worksheet contains a list of repeating tasks for each
client in the client worksheet (it also contains the details of the first
task to be created if there are none created, and the last task created if
there have been tasks created). The final worksheet contains a list of
actual tasks (one row per task, potentially several rows per client). All
of the permanent information contained in the Clients worksheet is repeated
in the other two worksheets by way of VLookup, so you can imagine that it
takes a while to recalculate when you have a lot of records.

Any pointers appreciated. Thanks.

The classic mistake made by those familiar with Excel but not Access is to
think of Access as though it were Excel with knobs on! I wish I could have
a week off for every Access database I have seen which had everything
crammed into just one table!

The key to what you are trying to achieve is to treat each of your Excel
worksheets as a different table in Access (although I'm not sure I
understand your descriptions of the 2nd and 3rd worksheets). You will have
a one-to-many relationship between the client table (the "one") and each of
the other tables (the "many"), e.g. one client record (in the client table)
relates to many records in a tasks table. Do NOT attempt to replicate the
client's static data in the related tables: that would be a complete no-no,
you only need the static data once, in the client table.

I strongly recommend that you get an introductory book on data
modelling/database design, and do not embark on this project until you are
happy with the basic concepts of tables, primary keys, foreign keys and
one-to-many relationships.

 

[Pete McQuilken]

Jack-
I highly recommend the Access 2003 Bible by Cary N. Prague. This book
covers all of the problems that you are having (and most of the problems you
will encounter). To answer one of your questions, in order to import the data
from excel, under the File menu, select "Get External Data", then select
"Import". After you do this, a browse window will appear. Select the file you
want to import, and the Import Spreadsheet Wizard will appear. This wizard
will guide you through the rest of the importing process. If you want to
rearrange the data (ie. put some of the data from the imported table into
another table), you can use the Make-Table Query. Once again, I recommend you
check out the Access 2003 Bible before getting to involved in your project.

 

[Bruce]

The main elements of a database are tables, queries, forms, and reports.
Tables are for storing data. Queries are form combining, manipulating, and
arranging data from tables. Form are for entering, editing, and viewing
information on the screen. Data on a form may be bound to a field (cell) in
a table or query, or it may be unbound for various reasons. Reports are for
viewing and printing information. They display information similarly to the
way forms do, but are not interactive.
I agree that more is needed than can be described here, and that you will
need to consult other resources, but here are a few general principles of
relational databases. A table's purpose should be able to be described in a
single sentence without using "and". Client information would be one table.
Clients and tasks do not belong in the same table. You enter client
information (name, address, etc.) once and once only. Thereafter you refer
to that record rather than copying the information. If the client changes
address you change one record (and one only) in your client table.
Every record has a unique identifier (primary key) field. Other records
also contain a foreign key field. A relationship between the primary key in
the clients table and the corresponding foreign key in a tasks table means
that the tasks containing that client's ID are associated with that client
only. Other records in the tasks table will be likewise associated with
other clients.
I would suggest (along with some reading or a course) experimenting with a
two table database, just to begin to see how relationships work. I strongly
urge you to become familiar with the workings of Access before committing to
your particular project, which is probably not all that complex, but which
does contain several elements that need to work together.

 

[Jack Sheet]

Thanks Pete. Is the book tutorial based or reference based (or a
combination of the 2)?
By which I mean:
A tutorial based work takes the form of "I want to achieve 'this'. How do I
do it?"
A reference based work takes the form of "I do 'this'. What is its effect?"

I probably want one of each!

Jack-
I highly recommend the Access 2003 Bible by Cary N. Prague. This book
covers all of the problems that you are having (and most of the problems you
will encounter). To answer one of your questions, in order to import the data
from excel, under the File menu, select "Get External Data", then select
"Import". After you do this, a browse window will appear. Select the file you
want to import, and the Import Spreadsheet Wizard will appear. This wizard
will guide you through the rest of the importing process. If you want to
rearrange the data (ie. put some of the data from the imported table into
another table), you can use the Make-Table Query. Once again, I recommend you
check out the Access 2003 Bible before getting to involved in your

project.

 

[Jack Sheet]

The classic mistake made by those familiar with Excel but not Access is to
think of Access as though it were Excel with knobs on! I wish I could have
a week off for every Access database I have seen which had everything
crammed into just one table!

The key to what you are trying to achieve is to treat each of your Excel
worksheets as a different table in Access (although I'm not sure I
understand your descriptions of the 2nd and 3rd worksheets). You will have
a one-to-many relationship between the client table (the "one") and each of
the other tables (the "many"), e.g. one client record (in the client table)
relates to many records in a tasks table. Do NOT attempt to replicate the
client's static data in the related tables: that would be a complete no-no,
you only need the static data once, in the client table.

I strongly recommend that you get an introductory book on data
modelling/database design, and do not embark on this project until you are
happy with the basic concepts of tables, primary keys, foreign keys and
one-to-many relationships.

Thanks Brian, that is the sort of general pointer I am looking for.

 

[Jack Sheet]

Snipped aspects taken on board. Thanks, it is all very useful

I strongly
urge you to become familiar with the workings of Access before committing to
your particular project, which is probably not all that complex, but which
does contain several elements that need to work together.

You may be right about that. My past experience with Excel (all
self-taught) is that there is no faster way to learn than to try to apply it
to a particular required project. But I reckon that Access is stepping up a
gear, so I need to get some basics. I may come back here with briefer, more
specific questions later

 

[Pete McQuilken]

Jack~
The book is a combination of the two. It is layed out in a tutorial form
(the book comes with a sample database that you work with); and is indexed
like a reference book. I have used it more like a reference book, but it is
chock full of tutorials. I have found it to be extremely helpful.

 

[Lynn Trapp]

Jack,
Pete and Bruce have given you some good advice, but let me add a couple of
things. Take the word "Excel" out of your vocabulary for a bit -- at least
while you are working in Access. Unfortunately, because an Access datasheet
looks similar to an Excel spreadsheet, many people tend to confuse the 2.
You need to start thinking relationally.

While you think your biggest problem is with populating tables and
designing reports, the biggest issue for you right now is actually designing
tables. Here are a few things to keep in mind.

1. Each table should store one and only one type of data -- a Clients table
to store information specific to each client, a ClientTasks table to store
information about tasks.
2. Each record in a table should be unique. One of the primary reasons for
using a relational database is to eliminate redundant data.
3. Each field in a table should be "atomic" -- that is it should store one
and only one bit of information. For example, do not store First Name and
Last name in the same field.

As Pete and Bruce have mentioned, getting a good book on database design
will be very helpful. Let me add one to the suggestions -- Database Design
for Mere Mortals by Michael Hernandez.

Good luck.

 

[Albert D. Kallal]

Actually, from your description so far, you already in a way have a database
design from excel.

The fact that you have 3 work sheets related is almost identical to that of
having 3 tables...

As I see it the problem can be broken down into three stages that should
be
tackled in order:
1) Designing the tables, ie the way that data is organised in the database
2) Populating the tables by importing the data from the existing Excel
database
3) Designing the standard reports.

Actually, you are missing one more step, and that is the designing of the
user interface (forms) to edit the data. (lets call this step 2.5, as it
usually becomes before step 3).

My first question for you guys is:
Can I create a table in more than 2 dimensions (and would I be better off
trying a different approach)?

No, each table in ms-access (or any relational database) can be though as a
single spreadsheet in Excel. You simply have a defined lest of field names
(cell names), and then the record repeats over and over. You have no
different ability in this regards in ms-access. The main difference is
however is that ms-access is relational..and you don't have to resort to
kluge things like vlookup. (however, the conceptual idea of linking sheets,
and relating tables is the same idea).

The reason that I think that I desire this feature is explained as
follows:

The database contains client records. For each client there are some
fields
of permanent information such as date of birth and description of
recurring
tasks. For each recurring task there may be a number of outstanding tasks
to be completed, and that number will vary from client to client. For
each
task the state of completion may be a variable.

Thus, I may have (eg) two clients. For each client, among other things we
complete tax returns. For client "A" there are three tax returns
outstanding (2002, 2003 and 2004), and for client "B" there are two tax
returns outstanding (2003 and 2004).

If I just have a 2-dimensional table, one of the fields might be "Tax
Returns", but a single cell in that field cannot contain the details of
all
outstanding tax returns. For that information to be stored the table
would
also have to have "depth".

Yes, that depth means you create another table. I mean, the way you did it
now was define another sheet..and simply "repeat" or "add" as many records
as you needed. In ms-access this would simply be a new table that is related
back to the main client table.

I could reserve additional fields in the 2nd
dimension for each year, but there is potentially no limit to the number
of
years outstanding

Right..so you make a table like:

Year Status Completed bla
bla

With the above table..I can enter..5, or 500 records...so there is no limit
here. (and, you did the same thing with Excel..buy creating another
worksheet...and you defined the column names..and then simply entered as
many records (rows) as you needed for that one client.

The existing Excel database has three (main) worksheets. One worksheet
contains a list of clients (in rows) and their permanent information (in
columns). The second worksheet contains a list of repeating tasks for
each
client in the client worksheet

Excellent, that 2nd worksheet of tasks would become a 2nd table in
ms-access, and you would relate it back to the main table. The way you
relate the table back is VERY simply. You have to add a column to the child
table, and this column is simply a long number type field, and in that field
you place the "id" of the main client of who this record belongs to. So, we
don't use vlookup, but in fact just add a extra column, and in the column we
put the id of who the record belongs to. Further, you can add a autonumber
column to the main client table..and use that number. You could also perhaps
use some client id number you use now..but it don't really matter.

The final worksheet contains a list of
actual tasks (one row per task, potentially several rows per client). All
of the permanent information contained in the Clients worksheet is
repeated
in the other two worksheets by way of VLookup, so you can imagine that it
takes a while to recalculate when you have a lot of records.

Again, the above is a correct model from a database relation point of view
(it is just that the mechanizing on how we pull the data together is
different (you don't use vloolup...but that extra column with the id of the
"parent" record....or in this case the client ID).

So, while you been strongly told that ms-access is not a spreadsheet, in
fact, you design you have in Excel is very much a relational data model now.
There is no special magic in a database..but only that it can VERY easily
pull data together from many tables. So, the trick in data modeling is to
design a new table for each set of repeating data.

You thus will wind up importing each of those 3 sheets you have into 3
tables. Once done, the hard clean up part would be to set the values of the
"new" columns in the two child tables to the correct parent (client id).

From what I am reading now...you have a relation design in Excel....but as
you mentioned..it don't work very fast..and also adding new "child" recodes
etc is a pain (where as in ms-access if you build nice forms..then the
"relating" of data is done for you. However, you do in fact has to "set"
that column that tells ms-access what parent record the data belongs do.
(ms-access can help you set this value..but you in fact have to specify
this)

 

[DanClayton]

The MOST fundamental but seemingly most easily missed characteristic of a
relational database is the one-to-many relationship. That's the magic of it
all. You can read volumes on rdbs design but until you get this basic
concept (which some Access users never seem to get), you won't be able to
make efficient use of Access. I don't have a copy of it readily available
but I used a very fundamental 'basic' book for many months while learning
Access, then stepped up to more detailed books.

 

[Larry Linson]

Look at the post in this very group, a response by Jeff Conrad to the post
entitled "Free Access Training" by Timboo. You'll find a very extensive list
of resources, either all or mostly all, free.

Larry Linson
Microsoft Access MVP

 

[Jack Sheet]

Found it, thanks for the tip

 

[Jack Sheet]

(Snip)

Right..so you make a table like:

Year Status Completed bla
bla

With the above table..I can enter..5, or 500 records...so there is no
limit here. (and, you did the same thing with Excel..buy creating another
worksheet...and you defined the column names..and then simply entered as
many records (rows) as you needed for that one client.

Presumably one additional field would be required? ... The client code,
being the key field in the client list table?

 

[Jack Sheet]

(Snip)

Good luck.

Thanks - think I'm going to need it!

 

[Albert D. Kallal]

(Snip)

Presumably one additional field would be required? ... The client code,
being the key field in the client list table?

Yes...you got the idea.

 

[J SCHWARTZ]

And Dan, how many times have you run into users making worksheets instead of
databases??
When I was consulting, it used to drive me nuts to see what people were
cramming into Lotus.
You get these organizations where you would expect to have someone
overseeing training of this sort, but instead , because it appears to get
the work out faster, every administrative person in the building is retyping
information into their own Excel application. I wonder if anyone has ever
calculated the employee costs associated with this. I even ran into one
last week where they made a copy of selected columns from the Excel sheet
into a new sheet in order to do a mail merge. Noone has a basic
understanding of this stuff, so you can imagine what the common working
folder looks like... subsets upon subsets and multiple copies for no
apparent reason. You gotta wonder sometimes how corporate America
functions. Computers are used like typewriters, so have we really
progressed very far?

<end of soap box>

jo

 

[Jack Sheet]

Hi Jo

I expect it has something to do with the number of Office installations out
there that do not have Access included. Time was when it would cost an
additional couple of hundred for that feature. The difference now seems to
be down to about 50 squid, so there is little point in getting Office
without Access.

 

[Bruce]

I should have said "before deploying" rather than "before committing to". I
have learned a great deal by experimenting, but have always tried to work out
everything before committing to actual data storage. I have attempted to
work with quite a few databases that were deployed before they were ready, as
a consequence of which they are clumsy and cranky and don't begin to do what
is needed.