Access Novice Taking On Large Project - Need Guidance

[gtangjr]

Greetings Access Geniuses,

I work for a very large company (100,000+ employees) as a paralegal. I
was hired because of my potent combination of legal and technical
knowledge.

Currently, my team has a serious problem concerning the management of
data relating to a series of products and associated processes. I'll
try to describe the DB I am trying to build that will help alleviate a
significant portion of that problem in time for our huge push during
the period between March and April.

Currently, I am managing 20 different tables.The two main tables are
our major product lines. We'll call them PL1 and PL2. The data
captured on these two tables illustrates each product assicated with
the product lines. We'll call these records PL1.1, PL1.2..., PL2.1,
PL2.2..., and so on.

There are also the following tables that list what we'll call "Simple
Data":

Features ("F"),
ID Number ("ID"),
ID Number 2 ("ID2"),
ID Number 3 ("ID3"),
ID Number 4 ("ID4"),
ID Number 5 ("ID5"),
Fiscal Year End ("FYE"),
Annual Update Date ("AUD"),
Surrender Period ("SP"),
Launch Date ("LD"), and
Status ("S")
G/Non-G ("GNG")

I should note that all the above tables only have one column. Also,
the tables called "ID..." are not in reference to primary keys, but
show product ID's corresponding to different sources. Just trust me
when I say they need five different ones, even though it looks silly
on paper.

Lastly, there are the following tables that list what we will call
"Complex Data":

-Portfolio/Sub ("PS") - This table has two columns, one for Portfolio
and one for Sub.
-TP Version ("TPV") - Simple table with a list of version numbers.
-P Trust ("PT") - A list of the P Trusts
-T Booklet Version ("TBV") - Two columns, one listing the version
names, another with the contents of the
corresponding version.
-Non-P Trust ("NPT") - A list of the Non-P Trusts
-Share Class ("SC") - A list of the Share Classes.

With these tables, the problem I am trying to solve is this. For each
product in a particular PL, we need to see a lot of information (hence
all the tables). If I were to generate a form, one would be able to
select a product, say PL1.1, and instantly see the following
specifically tailored information:

F
ID
ID2
ID3
ID4
ID5
FYE
AUD
SP
LD
S
GNG
PS
TPV
PT
TBV
NPT
SC

With respect to the Simple Data, the relationships are easy. Each of
F, ID, ID2, ID3, ID4, ID5, FYE, AUD, SP, LD, S, and GNG are related to
their corresponding PLs. What's important is that only specific
records from each of these show, however. For example, PL1.1 might
only get F1, F3, F4 and F5, even though the list of F's goes from
1-20. Additionally, it would only get one ID from each of the ID
tables, even though there are 50-60 ID numbers per table. I think you
get the point.

With respect to the Complex Data, the relationships are not so clear
cut. Here is how it breaks down. (We'll only use one PL because the
exact same applies to both PL1 and PL2.)

PL1 has a certain series of PS's associated with it (PS1, PS3, PS4...)
out of a list of about 60 PS's. It also has a series of related PT's
and NPT's. Within the NPT's there are TBV's. Within the TBV's there
are TPV's. Finally, each PS is associated with an SC.

So, PL > PS > PT/NPT > TBV > TPV, and PS > SC.

Where it gets even trickier is the each PS record is associated with a
corresponding SC. Based on the above flow, if you choose a PL record,
you should see all the PS's, PT's, NPT's, and so on associated with it
(in addition to the Simple Data above). However, the problem is that
an SC might be the same (i.e. SC1), but might exist within more than
one TPV associated within different PS's.

I'm 100% certain I did a poor job of explaining that. Sorry. I tried
my best.

Now that you have an overview, I'd like to know the following:

How should I set up the actual relationships? How should I assign
primary keys? Is a form the best way to achieve my purpose? Should I
use this many tables, or combine the data into one table and just
point the relationships to the correct field names? Is there anything
else I should know?

Thanks so, so much in advance for any assistance.

Best,

G

PS - Using Access 2003

 

[Jeff Boyce]

If, as your subject line suggests, you are new to Access, you have several
fairly steep learning curves before you if you want to be making good use of
Access relationally-oriented features and functions. First, brush up on
"normalization" and "relational database design".

Next, repeat after me "Access is NOT a spreadsheet". Now repeat that a
couple-hundred times <G!> You will have to un-learn what you know works in
Excel before you can begin making good use of Access.

Finally, you'll need to determine/decide how "user-friendly" (AKA
"user-proof") you want this application to be. I call it an application
because Access is NOT like Word or Excel. Both those products do something
that folks pretty well understand, moving words around, adding up columns of
numbers.

Access is a relational database -- how many people "do" database in daily
life?! OK, how many normal people ...?

You will need to learn the tricks of the trade to hide the "behind the
curtain" work that Access does from your users. Word and Excel are like
bookcases ... everyone can figure out how to take books off the shelves and
put them back. Access is like a table saw ... and folks who don't
understand that can cause themselves (and your data) some painful accidents!

If I haven't managed to discourage you yet, welcome! Keep coming back here
with specific questions as you work your way into and through this. The
folks who frequent these newsgroups are mostly volunteers, so you may have
to wait a day or so for a response.

If your project is more urgent, your experience low, and your user-demands
high, you might want to consider hiring some professional help. Taking on a
"really big project" for a first project in Access makes for considerable
risk. Whether you hire someone to do it, or someone to "mentor" you through
the process, a short time line may push you to find outside help.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[gtangjr]

Hi Jeff,

I don't think you have discouraged me quite yet! I am definitely
motivated to learn this as quickly as possible, and I look forward to
working through the process with all here.

I have posted the same inquiry on the Utter Access forum and have
gotten a reply with a lot of great resources. Hopefully, they will
give me a more solid foundation.

Fortunately, I have a firm grasp on what Access is. This isn't my
first time coming up with the concept of a database, but it is my
first time actually building one myself. We'll see what happens...

Any other info you have would be of great assistance. Thanks so much!

Best,

Gilbert

 

[John W. Vinson]

Any other info you have would be of great assistance. Thanks so much!

These may overlap with what you got over at UtterAccess but I've found them
useful:

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

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

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

 

[david]

You've missed something in your description of the structure:
all you've shown is that a PS has an SC, so a PL may have
multiple SC's.

Where it gets more complicated is when you have a loop,
such as (PL1 > PS > PL1). This is called a "many to many"
join, and the 'loop' is not always clear.

As it happens, Access/Jet is not able to make many-to-many
joins simple. That is, if you have many-to-many joins, you won't
be able to use updatable bound forms. If you aren't able to use
updatable bound forms, you are reduced to using Access like
a replacement for VB.NET or C#.

If a many-to-many join is only required for reports, this won't
matter much. If a many-to-many join is only required for a small
part of your application, this won't matter much. If you can get
rid of the many-many join by denormalizing your data, you will
have to be careful about update anomalies, but it might not
matter much. If you use SQL Server as a back end, you can
hide the problem by using stored procedures to update the
elements of your many-many join. If you don't use SQL Server
as a back end, you can use sequences of update queries or
code to update the elements of your many-to-many join

In any case, if your data does actually have a many-to-many
relationship be re-assured that you haven't missed the magic
trick for making it all simple: there is no magic trick to make
many-to-many relationships simple.

(david)

 

[gtangjr]

Hi David,

I have made some significant updates to the discussion over at
utteraccess.com. If you're interested in seeing how far (or not far)
I've gotten on this, feel free to check it out. You can find the
discussion here

http://www.utteraccess.com/forums/s...&page=0&view=collapsed&sb=5&o=&fpart=all&vc=1

It also includes a download of the database itself, which is currently
a series of tables with some basic sample relationships made (these
are not the actual relationships, just the "lines" showing how things
"should" be put together as far as I see it), as well as a sample of
the output we are trying to produce via a form of some sort.

If you can lend any insight into this, I would appreciate it so
much.

Best,

Gilbert