Struggles for the "visual" people

[Guest]

I am a new MS Access user. Previously, I used MS Excel for several years.
Ever since I was a kid, I needed to "visualize" almost everything in order to
learn it. I was the kid who nearly failed history but got straight-A's in
math and other science-based classes. Somebody assuring me that a complex
process really works usually isn't enough for me to completely understand. I
have to see it all.

As such, I have an extremely difficult time *trusting* in the relational
database concept. Being accustomed to a flat format in MS Excel, I could
trust in every record being held captive in one row (for the most part,
anyway). Now, having several different tables linked together by a
relationship I can't really "see" spins my brain into mass chaos. I'm OK
dealing with small databases but as the number of records and tables
increases, I get mentally hung up.

Has anybody out there had to overcome the same battle as me? How did you do
it, and what can you recommend?

 

[Tom Lake]

As such, I have an extremely difficult time *trusting* in the relational

database concept. Being accustomed to a flat format in MS Excel, I could
trust in every record being held captive in one row (for the most part,
anyway). Now, having several different tables linked together by a
relationship I can't really "see" spins my brain into mass chaos. I'm OK
dealing with small databases but as the number of records and tables
increases, I get mentally hung up.

Has anybody out there had to overcome the same battle as me? How did you
do
it, and what can you recommend?

I create a query for all relationships. That way I can see all the data
together but don't
waste the space storing the data redundantly. I also document my
relationships in the
Relationships window which shows those relationships visually and makes
query
writing easier since the relationships are automatically filled in when
pre-defined
in the Relationships window.

Tom Lake

Tom Lake

 

[Guest]

You can see the relationship of all your tables by clicking on
Tools-Relationships. I do understand you concern; however, mine is exactly
the opposite. I don't trust what is in an Excel spreadsheet, because there
is no way of validating it is correct. Did the person entering the data put
the right values in, or did I, for that matter? You never know, because
there is usually no way to edit the information.
Much of what I have to do in my current Access Applications is read in data
that has been entered in Excel and clean it up so values are usable and
correct.
At the end of it, it is not whether the data is in a flat file or a
relational format or whether the tools are trustworthy, it is more about how
competent are the people creating the applications and manipulating the data.

 

[Albert D.Kallal]

I find that using paper, and drawing out my designs really helps.

I don't setup relationships, or any thing, but simply start with a blank
piece of paper.

If I need something called customers,then I draw a big box, and call it
customers. I will then note that customers need invoices, so then I will
draw a big box, and name it Invoices. I will then draw a line with a arrow
head from customer box to invoices box (customers -----> invoices.)

The draw often looks very child like. Note that I don't mention field names,
or any kind of mumbo-jumbo. Just a easy to draw diagram. It turns out that
visual minds tend to be great at data modeling, but you just got to make it
a visual process!!

If fact, if you are a up to speed with Excel, then try using the 'drawing'
tools in excel (the ones for flow charts are really nice. In fact, they are
better then "word" drawing, since when you move around the boxes, the "join"
lines will also move. Drawing cute squares, giving each square a name, and
connecting them is very easy and "fun". No funny relationships stuff, just
squares and lines!!

The above should help you. The only thing to remember is that a table can
have a relationship to another table. It is either one to one, or one to
many. That is the ONLY choice you got....

I will also "re-post" a old post of mine where I explain how I use the
"visual" cues of a the relationships diagram to "guide" my lost brain.....

------

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

The ER window in fact turns the development process into a "visual" one, and
that is how my brain works...

 

[Guest]

Good advice, Albert. I use Visio or if I am at my favorite coffee hang out,
napkins and a pencil borrowed from the waitress.

 

[George Nicholson]

lol, bogarting a pencil from a waitress to draw out table relationships? I
hope you are a *really, really* good tipper.

 

[Guest]

Depends on the quality of the pencil

lol, bogarting a pencil from a waitress to draw out table relationships? I
hope you are a *really, really* good tipper.

 

[Vincent Johns]

It's too bad that this discussion isn't part of a textbook or video for
database students. I imagine that the modeling problem that Brian
mentioned, of translating real-world entities into fields and
relationships and queries, etc., bedevils many students (or other new
Access users) and leaves them slightly bewildered -- but, because the
project is due in 2 days, there's no time to think about it.

Albert, I like your idea of drawing boxes and arrows and labels on
paper, but for me it often requires a pencil with a big eraser... so I
sometimes use Access's "Query Design View" as a kind of drawing board.
I create, modify, and destroy relationships, move Tables around, etc.,
until they look right. The Query doesn't have to do anything useful to
be a helpful visualization tool, though Access may not let you save it
unless you put it into a somewhat meaningful form. Paintbrush works,
too, though the files are often larger than I'd like if I save them in
*.BMP format. (I suppose paper would work, too, but I like being able
to save my scratch-paper work in the same folder as the database project.)

At the risk of sounding like a commercial advertiser, let me suggest
visiting http://www.idef.com/, maintained by a former employer of mine,
which presents a nice tutorial on several formal graphical systems used
for modeling industrial processes. (One of them, IDEF1X, models
relational databases.) You don't have to buy the products to benefit
from the tutorials, and you can think of them as suggestions for ways to
do pencil-and-paper (or mouse-and-Paintbrush) modeling.

Brian said, "Previously, I used MS Excel for several years." Why not
now? Excel still works well, and though there's some overlap with
Access, each tool does some things the other is ill-suited for. It's
not too hard to link them together if you need to.

I recently finished a project in which I basically converted an Excel
spreadsheet (actually, about half a dozen Excel files) to Access; the
customer desired about the same functionality from the user's viewpoint
as he'd had with the Excel version. The big difference, as I saw it,
was maintainability -- even though the Excel spreadsheet was laid out
fairly logically, I had to do a lot of searching to find all the cells
that contributed to a given result. In Access, even though I wound up
with a couple of dozen Tables and maybe 150 Queries, all the information
of a given type was together and (I think) easier to find and to modify
if necessary. (And yes, I did include comments to make the database
easier to navigate.)

Brian, here's a suggestion for making Access look a bit more like what I
think you're used to seeing in Excel... Sometimes, in Excel, you'll
have several rows (after sorting) with the same value in some columns,
such as a mailing list with several people sharing an address. In
Access you might have two linked Tables, one with addresses and one with
people. You can define a Query which displays an address and a person
in each record (= row in Datasheet View), and thereafter work with the
Query as if it were a Table.

Also, if the number of Tables or Queries visible at one time becomes
daunting (sometimes it does for me), you can make copies in what Access
calls "Groups" in the Database Window. It's also not difficult to have
the Relationships Window hide Tables and Queries that you aren't dealing
with at the moment. For me, keeping to a manageable number (such as
six) the number of objects visible at a time can help make things
clearer. (Don't erase them, just hide them. You can easily bring them
back later.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.