Re: My first database (long)

From: Brian (bcap_at_IHATESPAMclara.co.uk)
Date: 12/10/04 

Date: Fri, 10 Dec 2004 12:24:17 -0000

"Jack ***" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
news:uH26k9q3EHA.3388@TK2MSFTNGP15.phx.gbl...
> 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 work*** contains a list of repeating tasks for
each
> client in the client work*** (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 work*** contains a list of
> actual tasks (one row per task, potentially several rows per client). All
> of the permanent information contained in the Clients work*** 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.
>
> --
> Return email address is not as DEEP as it appears
>

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.

Loading