How to move from design to development more quickly?

I think my DB design process for all but very simple databases requires
*significantly* more time to implement the design than necessary. Are there
other methods and specific tools to "speed things up"? (This is not about
what the design is, rather how to do the design and get it implemented as
fast as possible.)

I think I'm an "intermediate" Access power user, whatever that might mean.
Fairly strong on database theory and how to use DBs for the analytical work I
do (financial, organizational, etc). I can set DBs up fairly well, but I'm
not much of a programmer (trying to learn).

Here's how I do it now. I start on paper. When I begin to think I've "got
it", I do two main things: develop a Data Dictionary and an
Entity-Relationship Diagram. If I can identify existing or needed proceedures
I try to document them as well.

I use Excel to develop the Data Dictionary. Each worksheet is (in effect) a
data dictionary for each table. I name each worksheet's tab with the table's
name.

Here's a wrinkle: unlike "normal" database formating, the "records" are
columns and the attributes are rows. That is, the first row has all the
"FieldName"s in the table, the second indicates which fields are "Key"s
(Primary or Foreign), and so on for Description, DataType, DataDomain, Size,
Format, Lookup (that is, tied to a Lookup Table), Required, and Notes.

That puts the field names in the top row. When I think I've "got it", I
create a named range for each top row. I then open a new Access db and import
each named range from each table/worksheet to create the tables with fields.
But then I have to enter by hand all the characteristics for each field (data
type, desc, size, format, default, required, etc.)

I find it's faster to change the evolving design of tables in Excel than
Access itself because I can see and edit multiple tables' dictionaries at one
time.

I then use my Publisher-generated Entities-Relationships diagram to guide me
in creating the Table Relationships in Access.

I recently posted a related question, and got two helpful responses.

__
I found that the time it took me to think out (paper/pencil) a design, even
a preliminary structure, then define it better, then ... this process
slowed me down.

If by "efficient" you mean getting on with it, I found that I could create a
table structure (entities, attributes, relationships) in Access fairly
quickly, then step back and refine it.

Adding in a layer of paper, or a layer of "db definition table" slowed me
down.

But that is JOPO - just one person's opinion...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

___


Jamie Collins and Roger Carlson gave me good advice about how to use VBA
and/or SQL to grab my field definitions out of Excel to create the DB, but as
I say, I'm not very advanced at programming.
___

Suggestions are greatly appreciated. I'd much rather be canning my tomatoes
right now (32 plants in the garden!) than retyping stuff I've already typed.

Thanks - John D

I find it's faster to change the evolving design of tables in Excel than
Access itself because I can see and edit multiple tables' dictionaries at
one
time.

You can have multiple windows open in Access, and one of those can be the
relationships window.

And one of those can be a datasheet view of a query that brings in just the
table data that you want (selected wit checkboxes) and you can see and edit
it just like you can in Excel.

And you can cut and paste fields from one table design window to another if
you change your mind,

and you can cut and paste an entire line from a query to another line or to
a new record...

Please explain what Excel can do in this application that Access can not?



"John D" wrote in message
...
I think my DB design process for all but very simple databases requires
*significantly* more time to implement the design than necessary. Are
there
other methods and specific tools to "speed things up"? (This is not about
what the design is, rather how to do the design and get it implemented as
fast as possible.)

I think I'm an "intermediate" Access power user, whatever that might mean.
Fairly strong on database theory and how to use DBs for the analytical
work I
do (financial, organizational, etc). I can set DBs up fairly well, but I'm
not much of a programmer (trying to learn).

Here's how I do it now. I start on paper. When I begin to think I've "got
it", I do two main things: develop a Data Dictionary and an
Entity-Relationship Diagram. If I can identify existing or needed
proceedures
I try to document them as well.

I use Excel to develop the Data Dictionary. Each worksheet is (in effect)
a
data dictionary for each table. I name each worksheet's tab with the
table's
name.

Here's a wrinkle: unlike "normal" database formating, the "records" are
columns and the attributes are rows. That is, the first row has all the
"FieldName"s in the table, the second indicates which fields are "Key"s
(Primary or Foreign), and so on for Description, DataType, DataDomain,
Size,
Format, Lookup (that is, tied to a Lookup Table), Required, and Notes.

That puts the field names in the top row. When I think I've "got it", I
create a named range for each top row. I then open a new Access db and
import
each named range from each table/worksheet to create the tables with
fields.
But then I have to enter by hand all the characteristics for each field
(data
type, desc, size, format, default, required, etc.)

I find it's faster to change the evolving design of tables in Excel than
Access itself because I can see and edit multiple tables' dictionaries at
one
time.

I then use my Publisher-generated Entities-Relationships diagram to guide
me
in creating the Table Relationships in Access.

I recently posted a related question, and got two helpful responses.

__
I found that the time it took me to think out (paper/pencil) a design,
even
a preliminary structure, then define it better, then ... this process
slowed me down.

If by "efficient" you mean getting on with it, I found that I could create
a
table structure (entities, attributes, relationships) in Access fairly
quickly, then step back and refine it.

Adding in a layer of paper, or a layer of "db definition table" slowed me
down.

But that is JOPO - just one person's opinion...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

___


Jamie Collins and Roger Carlson gave me good advice about how to use VBA
and/or SQL to grab my field definitions out of Excel to create the DB, but
as
I say, I'm not very advanced at programming.
___

Suggestions are greatly appreciated. I'd much rather be canning my
tomatoes
right now (32 plants in the garden!) than retyping stuff I've already
typed.

Thanks - John D

"David Cox" wrote:

I find it's faster to change the evolving design of tables in Excel than
Access itself because I can see and edit multiple tables' dictionaries at
one
time.

You can have multiple windows open in Access, and one of those can be the
relationships window.

And one of those can be a datasheet view of a query that brings in just the
table data that you want (selected wit checkboxes) and you can see and edit
it just like you can in Excel.

And you can cut and paste fields from one table design window to another if
you change your mind,

and you can cut and paste an entire line from a query to another line or to
a new record...

Please explain what Excel can do in this application that Access can not?



David

Let me make sure we're talking about the same thing - I'm not talking about
after the database has been populated with data - I'm talking about
implementing a design. Unless I'm mistaken, I thought queries only access and
manipulate data living as records in tables. I didn't think a query could
access and manipulate field specifications such as size, format, data type,
etc. Am I wrong?

Is there a way to display and edit field specifications in a datasheet
format?

As to what Excel can do that Access doesn't as regards the design process
(at least what I know about Access at this point):

In Access the Table Design View has two areas. At the top you can see all
the fields, data types, and descriptions. But field size, format, input mask,
caption, default value, validation rule, etc. are displayed at the bottom
only for a single field that is highlighted at the top. I don't know how to
view and manipulate/edit all the attributes for a table in Access. It's kind
of clumsy to have to repeatedly highlight similar fields in different tables
so that you can see their attributes in the lower section of the view.

However, Excel displays rows and columns with each cell accessible and
edit-able. I can see all the design attributes for all fields in a table -
and by having multiple windows open with different table worksheets in each,
I can quickly browse the tables to make sure I'm defining similar fields
(such as OrganizationID) the same without having to highlight each similar
field.

Another thing Excel does that I don't know how to do in Access is print out
table definitions in simple tabular format. Tools/Analyze/Documenter "prints"
a "memo style" report that has each field's attributes as rows under the
field name. The report gets way too long (IMHO) very quickly.

NOW - there are several add-ins available from those great MVPs that do much
better - such as the one I'm using from Jeff Conrad available at

http://home.bendbroadband.com/conrad.../csdtools.html

*Much Better* report - to use for example in reviewing design with a client
or a development team. Or training, say, a support person.

But I was wondering if anyone knows of some software product in which you
can easily design a DB and then easily transfer the table - field definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

Thanks for your reply

John D



"John D" wrote in message
...
I think my DB design process for all but very simple databases requires
*significantly* more time to implement the design than necessary. Are
there
other methods and specific tools to "speed things up"? (This is not about
what the design is, rather how to do the design and get it implemented as
fast as possible.)

I think I'm an "intermediate" Access power user, whatever that might mean.
Fairly strong on database theory and how to use DBs for the analytical
work I
do (financial, organizational, etc). I can set DBs up fairly well, but I'm
not much of a programmer (trying to learn).

Here's how I do it now. I start on paper. When I begin to think I've "got
it", I do two main things: develop a Data Dictionary and an
Entity-Relationship Diagram. If I can identify existing or needed
proceedures
I try to document them as well.

I use Excel to develop the Data Dictionary. Each worksheet is (in effect)
a
data dictionary for each table. I name each worksheet's tab with the
table's
name.

Here's a wrinkle: unlike "normal" database formating, the "records" are
columns and the attributes are rows. That is, the first row has all the
"FieldName"s in the table, the second indicates which fields are "Key"s
(Primary or Foreign), and so on for Description, DataType, DataDomain,
Size,
Format, Lookup (that is, tied to a Lookup Table), Required, and Notes.

That puts the field names in the top row. When I think I've "got it", I
create a named range for each top row. I then open a new Access db and
import
each named range from each table/worksheet to create the tables with
fields.
But then I have to enter by hand all the characteristics for each field
(data
type, desc, size, format, default, required, etc.)

I find it's faster to change the evolving design of tables in Excel than
Access itself because I can see and edit multiple tables' dictionaries at
one
time.

I then use my Publisher-generated Entities-Relationships diagram to guide
me
in creating the Table Relationships in Access.

I recently posted a related question, and got two helpful responses.

__
I found that the time it took me to think out (paper/pencil) a design,
even
a preliminary structure, then define it better, then ... this process
slowed me down.

If by "efficient" you mean getting on with it, I found that I could create
a
table structure (entities, attributes, relationships) in Access fairly
quickly, then step back and refine it.

Adding in a layer of paper, or a layer of "db definition table" slowed me
down.

But that is JOPO - just one person's opinion...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

___


Jamie Collins and Roger Carlson gave me good advice about how to use VBA
and/or SQL to grab my field definitions out of Excel to create the DB, but
as
I say, I'm not very advanced at programming.
___

Suggestions are greatly appreciated. I'd much rather be canning my
tomatoes
right now (32 plants in the garden!) than retyping stuff I've already
typed.

Thanks - John D

well, i'm not an MVP, but i use a combination of pencil/paper and Access. i
think a lot of it is just accumulated experience, John. when i'm building a
database strictly for myself, my process analysis is an internal dialog, and
i usually write directly into table design view, modifying and restructuring
as i think through the process - until i have a normalized and correctly
related table structure. when i am analyzing somebody else's structure, i
take a lot of notes on paper that i use to build a clear, detailed *mental*
picture of the process and the entities involved; then i build the
tables/relationships. i tend to think with my eyes, though, so i often find
that building the tables raises additional questions that i must discuss
with my customer.

once i'm building tables, though, setting the field properties is mostly
automatic; i don't have to put a lot of thought into it. as you build more
and more databases, you'll get to the point where you know immediately, from
experience, what is the appropriate data type and field size for almost all
fields, when you need to make a field Required, or not, when to Index, or
not, etc. some things you'll probably set the same *all* the time - like
disallowing zero-length strings, removing the default zero value in number
fields, and so on - and then tweak those settings in the occasional field
that needs it.

hth


"John D" wrote in message
...


"David Cox" wrote:

I find it's faster to change the evolving design of tables in Excel
than
Access itself because I can see and edit multiple tables' dictionaries
at
one
time.

You can have multiple windows open in Access, and one of those can be
the
relationships window.

And one of those can be a datasheet view of a query that brings in just
the
table data that you want (selected wit checkboxes) and you can see and
edit
it just like you can in Excel.

And you can cut and paste fields from one table design window to another
if
you change your mind,

and you can cut and paste an entire line from a query to another line or
to
a new record...

Please explain what Excel can do in this application that Access can
not?



David

Let me make sure we're talking about the same thing - I'm not talking
about
after the database has been populated with data - I'm talking about
implementing a design. Unless I'm mistaken, I thought queries only access
and
manipulate data living as records in tables. I didn't think a query could
access and manipulate field specifications such as size, format, data
type,
etc. Am I wrong?

Is there a way to display and edit field specifications in a datasheet
format?

As to what Excel can do that Access doesn't as regards the design process
(at least what I know about Access at this point):

In Access the Table Design View has two areas. At the top you can see all
the fields, data types, and descriptions. But field size, format, input
mask,
caption, default value, validation rule, etc. are displayed at the bottom
only for a single field that is highlighted at the top. I don't know how
to
view and manipulate/edit all the attributes for a table in Access. It's
kind
of clumsy to have to repeatedly highlight similar fields in different
tables
so that you can see their attributes in the lower section of the view.

However, Excel displays rows and columns with each cell accessible and
edit-able. I can see all the design attributes for all fields in a table -
and by having multiple windows open with different table worksheets in
each,
I can quickly browse the tables to make sure I'm defining similar fields
(such as OrganizationID) the same without having to highlight each similar
field.

Another thing Excel does that I don't know how to do in Access is print
out
table definitions in simple tabular format. Tools/Analyze/Documenter
"prints"
a "memo style" report that has each field's attributes as rows under the
field name. The report gets way too long (IMHO) very quickly.

NOW - there are several add-ins available from those great MVPs that do
much
better - such as the one I'm using from Jeff Conrad available at

http://home.bendbroadband.com/conrad.../csdtools.html

*Much Better* report - to use for example in reviewing design with a
client
or a development team. Or training, say, a support person.

But I was wondering if anyone knows of some software product in which you
can easily design a DB and then easily transfer the table - field
definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

Thanks for your reply

John D



"John D" wrote in message
...
I think my DB design process for all but very simple databases requires
*significantly* more time to implement the design than necessary. Are
there
other methods and specific tools to "speed things up"? (This is not
about
what the design is, rather how to do the design and get it implemented
as
fast as possible.)

I think I'm an "intermediate" Access power user, whatever that might
mean.
Fairly strong on database theory and how to use DBs for the analytical
work I
do (financial, organizational, etc). I can set DBs up fairly well, but
I'm
not much of a programmer (trying to learn).

Here's how I do it now. I start on paper. When I begin to think I've
"got
it", I do two main things: develop a Data Dictionary and an
Entity-Relationship Diagram. If I can identify existing or needed
proceedures
I try to document them as well.

I use Excel to develop the Data Dictionary. Each worksheet is (in
effect)
a
data dictionary for each table. I name each worksheet's tab with the
table's
name.

Here's a wrinkle: unlike "normal" database formating, the "records"
are
columns and the attributes are rows. That is, the first row has all
the
"FieldName"s in the table, the second indicates which fields are
"Key"s
(Primary or Foreign), and so on for Description, DataType, DataDomain,
Size,
Format, Lookup (that is, tied to a Lookup Table), Required, and Notes.

That puts the field names in the top row. When I think I've "got it",
I
create a named range for each top row. I then open a new Access db and
import
each named range from each table/worksheet to create the tables with
fields.
But then I have to enter by hand all the characteristics for each
field
(data
type, desc, size, format, default, required, etc.)

I find it's faster to change the evolving design of tables in Excel
than
Access itself because I can see and edit multiple tables' dictionaries
at
one
time.

I then use my Publisher-generated Entities-Relationships diagram to
guide
me
in creating the Table Relationships in Access.

I recently posted a related question, and got two helpful responses.

__
I found that the time it took me to think out (paper/pencil) a design,
even
a preliminary structure, then define it better, then ... this process
slowed me down.

If by "efficient" you mean getting on with it, I found that I could
create
a
table structure (entities, attributes, relationships) in Access fairly
quickly, then step back and refine it.

Adding in a layer of paper, or a layer of "db definition table" slowed
me
down.

But that is JOPO - just one person's opinion...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

___


Jamie Collins and Roger Carlson gave me good advice about how to use
VBA
and/or SQL to grab my field definitions out of Excel to create the DB,
but
as
I say, I'm not very advanced at programming.
___

Suggestions are greatly appreciated. I'd much rather be canning my
tomatoes
right now (32 plants in the garden!) than retyping stuff I've already
typed.

Thanks - John D

=?Utf-8?B?Sm9obiBE?= wrote in
:

Here's how I do it now. I start on paper. When I begin to think I've
"got it", I do two main things: develop a Data Dictionary and an
Entity-Relationship Diagram. If I can identify existing or needed
proceedures I try to document them as well.

I use Excel to develop the Data Dictionary. Each worksheet is (in
effect) a data dictionary for each table. I name each worksheet's tab
with the table's name.

You have two (or three) choices:

If you can make a business case for the investment, then you can buy a
tool to do this -- Embarcadero, Visio, etc etc;

If you don't have any money to invest, then you can invest your time
instead by creating a tool yourself. It's not very hard; it's just a
question of building one or more "CREATE TABLE..." commands and executing
them.

If you are very lucky, you may find that someone who has built such a
tool will be kind enough to give you his or her work for free. I seem to
remember that Jeff did exactly that.

Jamie Collins and Roger Carlson gave me good advice about how to use
VBA and/or SQL to grab my field definitions out of Excel to create the
DB, but as I say, I'm not very advanced at programming.

What can I say -- this is a programming group, go learn programming!

B Wishes


Tim F

Hi John,

I think you'll find a wide range of viewpoints on this issue. I know some people who won't even go
near the power button on a computer before getting every specific detail of their table structure
down on paper. I know others who do all of their table design work within Access-refining as they go
along. Still others use a mesh of the two approaches. MVP John Viescas outlines in his book
Microsoft Office Access 2003 Inside Out his method for table design. He uses some Excel-made printed
sheets to do his design work on paper first. Once he has the basic structure down he begins to build
his tables, but then makes additional modifications within Access as he builds the relationships and
enters some sample data.

Personally I'm quite similar to MVP Jeff Boyce (discussed in your earlier thread) concerning this
topic. I tend to do most of my design work within Access itself. I usually initially create a table
for each entity with just one temporary field and throw it into the Relationships window. I tend to
visualize things much easier when I can see how their relationships interact within the Access
window versus sheets of paper laid out on the floor. That's just me. :-)

You touched upon the following subject:

But I was wondering if anyone knows of some software product in which you
can easily design a DB and then easily transfer the table - field definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

I personally have not seen any such tool, but that doesn't mean one does not exist. With enough
time, effort, and VBA knowledge I'm sure someone could create something, but I don't know how much
of a need there would be. I don't know if it would save a whole lot of time actually. Remember that
for every field you need to set certain field properties. In Access you need to do this on a field
by field basis. If you did this same work in say Excel you *still* have to set these individual
settings in Excel so this special VBA tool could set the appropriate field settings within Access.
Follow me? I just don't see the time advantage here.

Don't get me wrong, I'm not knocking your method of table design. If it works for you and it helps
you to generate a solid base, then it's great. I just think that there really is no way to avoid the
down and dirty task of going through field by field and setting specific properties. Computers can
only guess so much. A tool to help bridge the gap may help the process, but you'll still have to
carefully go over the blueprint plans.

Don't think of the extra time spent on table design as a waste. MVP John Vinson likes to use the
analogy of building the foundation of a house for this process. Spending the extra time on getting
the table design right (or as darn close to right as possible) on the first attempt **will** save
time on the rest of the application development. Trust me. A good table design makes the interface
design a lot easier.

Think of it this way--I have to choose between two contractors to design and build a very large new
house for my family. The first contractor will spend about 60% of the cost on building a rock solid
foundation. Extra time will be spent carefully analyzing the soil around the area to determine where
the best place on the property to build. Rainfall patterns from past years will be studied to see
how drainage should be handled. Extra time and expense will be spent drilling farther into the
bedrock for more stability. Extra rebar and special concrete will be poured in increments to further
add stability. All of this added expense will leave less money for the rest of the house. Naturally
the house will have to skimp a little bit on the extra "goodies." The house will be nicely decorated
and adequately furnished, but certainly not a mansion inside.

The second contractor, on the contrary, will spend 30% of the total cost on the foundation. Just a
basic run-of-the-mill foundation job with no extra reinforcements. The rest of the house will look
like a palace inside. Rich mahogany hardwood floors, stainless steel appliances, every room
pre-wired with CAT 6 cabling, air conditioning, 42" plasma TVs in every room, etc. You get the idea.

You're the contractor for the first house. In your presentation to me you do an absolute fantastic
job of outlining the costs of the project **up front** to me. You carefully detail why you believe
your process is the best. You also detail why the furnishings of the house will not be top of the
line, but you discuss the benefits of the extra time and expense spent on the solid foundation. You
discuss the changing seasons and future resale value of the house. Before closing your presentation
you offer up one last comment, "By the way, your property is in an earthquake prone region."

If you have done an excellent job of outlining the costs to your clients, explained why the extra
design time spent reaps rewards later on as my business changes, and explain that extra "goodies"
and features could be added on as needed in the future, who am I going to choose to solve my
business need?

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

"John D" wrote in message:
...

Let me make sure we're talking about the same thing - I'm not talking about
after the database has been populated with data - I'm talking about
implementing a design. Unless I'm mistaken, I thought queries only access and
manipulate data living as records in tables. I didn't think a query could
access and manipulate field specifications such as size, format, data type,
etc. Am I wrong?

Is there a way to display and edit field specifications in a datasheet
format?

As to what Excel can do that Access doesn't as regards the design process
(at least what I know about Access at this point):

In Access the Table Design View has two areas. At the top you can see all
the fields, data types, and descriptions. But field size, format, input mask,
caption, default value, validation rule, etc. are displayed at the bottom
only for a single field that is highlighted at the top. I don't know how to
view and manipulate/edit all the attributes for a table in Access. It's kind
of clumsy to have to repeatedly highlight similar fields in different tables
so that you can see their attributes in the lower section of the view.

However, Excel displays rows and columns with each cell accessible and
edit-able. I can see all the design attributes for all fields in a table -
and by having multiple windows open with different table worksheets in each,
I can quickly browse the tables to make sure I'm defining similar fields
(such as OrganizationID) the same without having to highlight each similar
field.

Another thing Excel does that I don't know how to do in Access is print out
table definitions in simple tabular format. Tools/Analyze/Documenter "prints"
a "memo style" report that has each field's attributes as rows under the
field name. The report gets way too long (IMHO) very quickly.

NOW - there are several add-ins available from those great MVPs that do much
better - such as the one I'm using from Jeff Conrad available at

http://home.bendbroadband.com/conrad.../csdtools.html

*Much Better* report - to use for example in reviewing design with a client
or a development team. Or training, say, a support person.

But I was wondering if anyone knows of some software product in which you
can easily design a DB and then easily transfer the table - field definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

or "You cannot build a skyscraper starting with a bungalow."

"Jeff Conrad" wrote in message
...
Hi John,

I think you'll find a wide range of viewpoints on this issue. I know some
people who won't even go near the power button on a computer before
getting every specific detail of their table structure down on paper. I
know others who do all of their table design work within Access-refining
as they go along. Still others use a mesh of the two approaches. MVP John
Viescas outlines in his book Microsoft Office Access 2003 Inside Out his
method for table design. He uses some Excel-made printed sheets to do his
design work on paper first. Once he has the basic structure down he begins
to build his tables, but then makes additional modifications within Access
as he builds the relationships and enters some sample data.

Personally I'm quite similar to MVP Jeff Boyce (discussed in your earlier
thread) concerning this topic. I tend to do most of my design work within
Access itself. I usually initially create a table for each entity with
just one temporary field and throw it into the Relationships window. I
tend to visualize things much easier when I can see how their
relationships interact within the Access window versus sheets of paper
laid out on the floor. That's just me. :-)

You touched upon the following subject:

But I was wondering if anyone knows of some software product in which you
can easily design a DB and then easily transfer the table - field
definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

I personally have not seen any such tool, but that doesn't mean one does
not exist. With enough time, effort, and VBA knowledge I'm sure someone
could create something, but I don't know how much of a need there would
be. I don't know if it would save a whole lot of time actually. Remember
that for every field you need to set certain field properties. In Access
you need to do this on a field by field basis. If you did this same work
in say Excel you *still* have to set these individual settings in Excel so
this special VBA tool could set the appropriate field settings within
Access. Follow me? I just don't see the time advantage here.

Don't get me wrong, I'm not knocking your method of table design. If it
works for you and it helps you to generate a solid base, then it's great.
I just think that there really is no way to avoid the down and dirty task
of going through field by field and setting specific properties. Computers
can only guess so much. A tool to help bridge the gap may help the
process, but you'll still have to carefully go over the blueprint plans.

Don't think of the extra time spent on table design as a waste. MVP John
Vinson likes to use the analogy of building the foundation of a house for
this process. Spending the extra time on getting the table design right
(or as darn close to right as possible) on the first attempt **will** save
time on the rest of the application development. Trust me. A good table
design makes the interface design a lot easier.

Think of it this way--I have to choose between two contractors to design
and build a very large new house for my family. The first contractor will
spend about 60% of the cost on building a rock solid foundation. Extra
time will be spent carefully analyzing the soil around the area to
determine where the best place on the property to build. Rainfall patterns
from past years will be studied to see how drainage should be handled.
Extra time and expense will be spent drilling farther into the bedrock for
more stability. Extra rebar and special concrete will be poured in
increments to further add stability. All of this added expense will leave
less money for the rest of the house. Naturally the house will have to
skimp a little bit on the extra "goodies." The house will be nicely
decorated and adequately furnished, but certainly not a mansion inside.

The second contractor, on the contrary, will spend 30% of the total cost
on the foundation. Just a basic run-of-the-mill foundation job with no
extra reinforcements. The rest of the house will look like a palace
inside. Rich mahogany hardwood floors, stainless steel appliances, every
room pre-wired with CAT 6 cabling, air conditioning, 42" plasma TVs in
every room, etc. You get the idea.

You're the contractor for the first house. In your presentation to me you
do an absolute fantastic job of outlining the costs of the project **up
front** to me. You carefully detail why you believe your process is the
best. You also detail why the furnishings of the house will not be top of
the line, but you discuss the benefits of the extra time and expense spent
on the solid foundation. You discuss the changing seasons and future
resale value of the house. Before closing your presentation you offer up
one last comment, "By the way, your property is in an earthquake prone
region."

If you have done an excellent job of outlining the costs to your clients,
explained why the extra design time spent reaps rewards later on as my
business changes, and explain that extra "goodies" and features could be
added on as needed in the future, who am I going to choose to solve my
business need?

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

"John D" wrote in message:
...

Let me make sure we're talking about the same thing - I'm not talking
about
after the database has been populated with data - I'm talking about
implementing a design. Unless I'm mistaken, I thought queries only access
and
manipulate data living as records in tables. I didn't think a query could
access and manipulate field specifications such as size, format, data
type,
etc. Am I wrong?

Is there a way to display and edit field specifications in a datasheet
format?

As to what Excel can do that Access doesn't as regards the design process
(at least what I know about Access at this point):

In Access the Table Design View has two areas. At the top you can see all
the fields, data types, and descriptions. But field size, format, input
mask,
caption, default value, validation rule, etc. are displayed at the bottom
only for a single field that is highlighted at the top. I don't know how
to
view and manipulate/edit all the attributes for a table in Access. It's
kind
of clumsy to have to repeatedly highlight similar fields in different
tables
so that you can see their attributes in the lower section of the view.

However, Excel displays rows and columns with each cell accessible and
edit-able. I can see all the design attributes for all fields in a
table -
and by having multiple windows open with different table worksheets in
each,
I can quickly browse the tables to make sure I'm defining similar fields
(such as OrganizationID) the same without having to highlight each
similar
field.

Another thing Excel does that I don't know how to do in Access is print
out
table definitions in simple tabular format. Tools/Analyze/Documenter
"prints"
a "memo style" report that has each field's attributes as rows under the
field name. The report gets way too long (IMHO) very quickly.

NOW - there are several add-ins available from those great MVPs that do
much
better - such as the one I'm using from Jeff Conrad available at

http://home.bendbroadband.com/conrad.../csdtools.html

*Much Better* report - to use for example in reviewing design with a
client
or a development team. Or training, say, a support person.

But I was wondering if anyone knows of some software product in which you
can easily design a DB and then easily transfer the table - field
definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

some folks down here in Anaheim Hills probably wished they'd read your
analogy before the big rains we had year before last in So Cal.


"Jeff Conrad" wrote in message
...
Hi John,

I think you'll find a wide range of viewpoints on this issue. I know some
people who won't even go
near the power button on a computer before getting every specific detail
of their table structure
down on paper. I know others who do all of their table design work within
Access-refining as they go
along. Still others use a mesh of the two approaches. MVP John Viescas
outlines in his book
Microsoft Office Access 2003 Inside Out his method for table design. He
uses some Excel-made printed
sheets to do his design work on paper first. Once he has the basic
structure down he begins to build
his tables, but then makes additional modifications within Access as he
builds the relationships and
enters some sample data.

Personally I'm quite similar to MVP Jeff Boyce (discussed in your earlier
thread) concerning this
topic. I tend to do most of my design work within Access itself. I usually
initially create a table
for each entity with just one temporary field and throw it into the
Relationships window. I tend to
visualize things much easier when I can see how their relationships
interact within the Access
window versus sheets of paper laid out on the floor. That's just me. :-)

You touched upon the following subject:

But I was wondering if anyone knows of some software product in which
you
can easily design a DB and then easily transfer the table - field
definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

I personally have not seen any such tool, but that doesn't mean one does
not exist. With enough
time, effort, and VBA knowledge I'm sure someone could create something,
but I don't know how much
of a need there would be. I don't know if it would save a whole lot of
time actually. Remember that
for every field you need to set certain field properties. In Access you
need to do this on a field
by field basis. If you did this same work in say Excel you *still* have to
set these individual
settings in Excel so this special VBA tool could set the appropriate field
settings within Access.
Follow me? I just don't see the time advantage here.

Don't get me wrong, I'm not knocking your method of table design. If it
works for you and it helps
you to generate a solid base, then it's great. I just think that there
really is no way to avoid the
down and dirty task of going through field by field and setting specific
properties. Computers can
only guess so much. A tool to help bridge the gap may help the process,
but you'll still have to
carefully go over the blueprint plans.

Don't think of the extra time spent on table design as a waste. MVP John
Vinson likes to use the
analogy of building the foundation of a house for this process. Spending
the extra time on getting
the table design right (or as darn close to right as possible) on the
first attempt **will** save
time on the rest of the application development. Trust me. A good table
design makes the interface
design a lot easier.

Think of it this way--I have to choose between two contractors to design
and build a very large new
house for my family. The first contractor will spend about 60% of the cost
on building a rock solid
foundation. Extra time will be spent carefully analyzing the soil around
the area to determine where
the best place on the property to build. Rainfall patterns from past years
will be studied to see
how drainage should be handled. Extra time and expense will be spent
drilling farther into the
bedrock for more stability. Extra rebar and special concrete will be
poured in increments to further
add stability. All of this added expense will leave less money for the
rest of the house. Naturally
the house will have to skimp a little bit on the extra "goodies." The
house will be nicely decorated
and adequately furnished, but certainly not a mansion inside.

The second contractor, on the contrary, will spend 30% of the total cost
on the foundation. Just a
basic run-of-the-mill foundation job with no extra reinforcements. The
rest of the house will look
like a palace inside. Rich mahogany hardwood floors, stainless steel
appliances, every room
pre-wired with CAT 6 cabling, air conditioning, 42" plasma TVs in every
room, etc. You get the idea.

You're the contractor for the first house. In your presentation to me you
do an absolute fantastic
job of outlining the costs of the project **up front** to me. You
carefully detail why you believe
your process is the best. You also detail why the furnishings of the house
will not be top of the
line, but you discuss the benefits of the extra time and expense spent on
the solid foundation. You
discuss the changing seasons and future resale value of the house. Before
closing your presentation
you offer up one last comment, "By the way, your property is in an
earthquake prone region."

If you have done an excellent job of outlining the costs to your clients,
explained why the extra
design time spent reaps rewards later on as my business changes, and
explain that extra "goodies"
and features could be added on as needed in the future, who am I going to
choose to solve my
business need?

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

"John D" wrote in message:
...

Let me make sure we're talking about the same thing - I'm not talking
about
after the database has been populated with data - I'm talking about
implementing a design. Unless I'm mistaken, I thought queries only
access and
manipulate data living as records in tables. I didn't think a query
could
access and manipulate field specifications such as size, format, data
type,
etc. Am I wrong?

Is there a way to display and edit field specifications in a datasheet
format?

As to what Excel can do that Access doesn't as regards the design
process
(at least what I know about Access at this point):

In Access the Table Design View has two areas. At the top you can see
all
the fields, data types, and descriptions. But field size, format, input
mask,
caption, default value, validation rule, etc. are displayed at the
bottom
only for a single field that is highlighted at the top. I don't know how
to
view and manipulate/edit all the attributes for a table in Access. It's
kind
of clumsy to have to repeatedly highlight similar fields in different
tables
so that you can see their attributes in the lower section of the view.

However, Excel displays rows and columns with each cell accessible and
edit-able. I can see all the design attributes for all fields in a
table -
and by having multiple windows open with different table worksheets in
each,
I can quickly browse the tables to make sure I'm defining similar fields
(such as OrganizationID) the same without having to highlight each
similar
field.

Another thing Excel does that I don't know how to do in Access is print
out
table definitions in simple tabular format. Tools/Analyze/Documenter
"prints"
a "memo style" report that has each field's attributes as rows under the
field name. The report gets way too long (IMHO) very quickly.

NOW - there are several add-ins available from those great MVPs that do
much
better - such as the one I'm using from Jeff Conrad available at

http://home.bendbroadband.com/conrad.../csdtools.html

*Much Better* report - to use for example in reviewing design with a
client
or a development team. Or training, say, a support person.

But I was wondering if anyone knows of some software product in which
you
can easily design a DB and then easily transfer the table - field
definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

Thanks folks for your comments.

I know a good local contractor who subscribes to the business imperative of
"better, faster, cheaper". That was the focus of my question - there must be
a "better, faster, cheaper" way of moving from design to development of an
Access DB than what I'm doing.

Incidentally, I absolutely buy the idea that it's important to take the time
to make sure the design is "right". For example, when you understand what's
happening in the Normalization process it's clear you need to do it. But in
context of my question that's part of the *CONTENT* of design, not the
*PROCESS* or way of doing it. It needs to be done, I just want to do it
"better, faster, cheaper".

Here's what I got from your responses:

1) There really isn't a "settled industry standard" about what tools and
process to use in designing Access databases; it largely depends on "what
works for you".

2) I combined a thought from both tina and David. I'm going to create a
"Boilerplate" Table of fields I'll use frequently already formated the way I
want, and then copy/paste from that boilerplate table into new tables I'm
building. Then I'll probably only have to change the names. Like, have the
"phone" fields formated as I will want, then copy it into tables and rename
to things like "WorkPhone" or "PersonPhone". That at least would save the
time of having to go through all the field attribute definitions.

3) The Visual Studio or Embarcadero stuff refered to by Tim is intriguing -
I'll look into it more.

Thanks again

John D

"John D" wrote in message:
...

2) I combined a thought from both tina and David. I'm going to create a
"Boilerplate" Table of fields I'll use frequently already formated the way I
want, and then copy/paste from that boilerplate table into new tables I'm
building. Then I'll probably only have to change the names. Like, have the
"phone" fields formated as I will want, then copy it into tables and rename
to things like "WorkPhone" or "PersonPhone". That at least would save the
time of having to go through all the field attribute definitions.

John,

One other thing to consider is a new feature with Access 2007 that follow closely with your
"Boilerplate" table of fields example. This new feature not only could save time on the design
process, but also the development process. In 2007 you can set up your own custom "blank" database
template that includes, for example, sample tables with field properties set as well as some sample
queries, forms, reports and even any code modules you wish to have. Once you set this up if you
click on new blank database in 2007 *every* new database would include all these elements! Pretty
slick, huh? Essentially your blank database already has a whole bunch of application objects already
created. You could even create several of these custom templates to use for various scenarios.

Something to think about.
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
Access 2007 Info: http://www.AccessJunkie.com