Multiple fields of same record in table updated

[bluegrassstateworker]

I have a fairly simple Access database that I am intentionally trying
to keep as flat as possible so that the primary table can be exported
to Excel. Some people <have> to have a spreadsheet.

What I would like to have is an autolookup in one other table that
contains multiple fields that I would like to pull into the primary
table. For example:

The secondary table contains the following fields:
1. Zip Code
2. State
3. Region

The primary table has corresponding fields. Normally, only one value
from the secondary table can be inserted into the defined field of the
primary table. Since it is a flat structure I would like essentially,
three fields in the primary table updated all at once.

I understand why separate tables would be better but my coworkers
glaze over after only a few seconds after mentioning data
normalization. Any ideas appreciated.

 

[Jeff Boyce]

How you wish to display/output data has nothing, I repeat NOTHING!!! to do
with how you get the best use of Access' relationally-oriented
features/functions (OK, slight overemphasis).

If the only reason you are "flattening" your Access tables is to allow
export of data to Excel, stop now!

First, figure out the entities and relationships you are dealing with. Then
create tables to handle these.

Now, time to export? Use a query! Queries give you a way to flatten your
relational data for displaying, for reporting, for exporting.

You want to have related records show up? Use a query! Join the tables on
their shared ID field(s).

It doesn't make much sense to learn how to work AROUND the features Access
provides when you can take advantage of them and make your work (and
Access') a lot simpler.

(perhaps a bit overemphasized, but I think you'll either pay now or pay
later!)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

Why should your coworkers care about the design? It sounds as if you are
entering data directly in tables. You should use forms, especially for
users other than the developer.

What is the connection between the two tables? I realize the secondary
table has the three fields you mentioned, but how does it relate to the
primary table? From which record in the secondary table would you have the
record in the primary table "pull" the records?

For exporting you can use a query, which you should be able to design so
that it contains the information you want, arranged as you choose. It all
starts with the database design. Details of your design are needed before
any kind of specific response is possible.

 

[Ken Sheridan]

You might like to take a look at the demo .mdb file from:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Its primarily designed to illustrate the use of correlated combo boxes while
maintaining a set of normalized tables, but as the logical model is similar
to yours, it also serves to show how an interface which can be used
intuitively by users is achievable without departing from the principles of
normalization. The demo uses the local administrative units of County,
District and Parish in my neck of the woods, but the model equally applies to
any set of entities in an hierarchical relationship.

The option to 'Open Form in Continuous Form View Using Multi-Column Combo
Box and Text Boxes' is probably the closest to what you are describing in
terms of user perception.

For export to Excel all that's necessary is to join the tables in a query
and export the query's result set.

Ken Sheridan
Stafford, England

 

[bluegrassstateworker]

How you wish to display/output data has nothing, I repeat NOTHING!!! to do
with how you get the best use ofAccess' relationally-oriented
features/functions (OK, slight overemphasis).

If the only reason you are "flattening" yourAccesstables is to allow
export of data to Excel, stop now!

First, figure out the entities and relationships you are dealing with.  Then
create tables to handle these.

Now, time to export?  Use a query!  Queries give you a way to flattenyour
relational data for displaying, for reporting, for exporting.

You want to have related records show up?  Use a query!  Join the tables on
their shared ID field(s).

It doesn't make much sense to learn how to work AROUND the featuresAccess
provides when you can take advantage of them and make your work (andAccess') a lot simpler.

(perhaps a bit overemphasized, but I think you'll either pay now or pay
later!)

Good luck!

Regards

Jeff Boyce
Microsoft Office/AccessMVP

I hear your emphasis and am not disagreeing. I work with a group of
people that use spreadsheets. They think in spreadsheets, relate any
data in terms of spreadsheets, and pivot tables are their reports. On
the down side, our IT group will not touch Access, regardless of how
simple the design - and forget the fact that a database makes more
sense. If it is not written in .NET they wont touch it.

Since I will not be a permanent fixture in this group, my goal was to
allow them to use Access but be able to use the one table itself.
Perhaps a button that will allow exporting the query to a file would
be a better way to give them a spreadsheet option.

 

[bluegrassstateworker]

Why should your coworkers care about the design?  It sounds as if you are
entering data directly in tables.  You should use forms, especially for
users other than the developer.

What is the connection between the two tables?  I realize the secondary
table has the three fields you mentioned, but how does it relate to the
primary table?  From which record in the secondary table would you havethe
record in the primary table "pull" the records?

For exporting you can use a query, which you should be able to design so
that it contains the information you want, arranged as you choose.  It all
starts with the database design.  Details of your design are needed before
any kind of specific response is possible.

I have designed far more complicated databases but it seems the
sometimes illogical insistence of the end users make it more
complicated. If the users could use a spreadsheet that allowed
multiple access (like a database) then they would use the
spreadsheet. I thought this would be quick because of its simplicity.

 

[bluegrassstateworker]

You might like to take a look at the demo .mdb file from:

http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&ts....

Its primarily designed to illustrate the use of correlated combo boxes while
maintaining a set of normalized tables, but as the logical model is similar
to yours, it also serves to show how an interface which can be used
intuitively by users is achievable without departing from the principles of
normalization.  The demo uses the local administrative units of County,
District and Parish in my neck of the woods, but the model equally applies to
any set of entities in an hierarchical relationship.

The option to 'Open Form in Continuous Form View Using Multi-Column Combo
Box and Text Boxes' is probably the closest to what you are describing in
terms of user perception.

For export to Excel all that's necessary is to join the tables in a query
and export the query's result set.

Ken Sheridan
Stafford, England

Thanks Ken, I will give it a try.

 

[BruceM]

One of my points was that the concept of normalization is for the developer.
All the users want, in my experience, is an interface with which they are
comfortable. Of course, some people are absolutely resistant to change, so
unless there is a directive from the top that they *will* adapt there may be
little you can do.
That being said, a form can be made to resemble a spreadsheet. The
questions then would remain about how the tables are related, what exactly
you mean by "pulling" the data from one form to another, and why you would
do that rather than linking to data in a related table. It is not
immediately apparent how a table containing the fields ZipCode, State, and
Region is related to a primary table that you have not defined other than
stating that it contains fields that correspond to the fields in the
secondary table.

Why should your coworkers care about the design? It sounds as if you are
entering data directly in tables. You should use forms, especially for
users other than the developer.

What is the connection between the two tables? I realize the secondary
table has the three fields you mentioned, but how does it relate to the
primary table? From which record in the secondary table would you have the
record in the primary table "pull" the records?

For exporting you can use a query, which you should be able to design so
that it contains the information you want, arranged as you choose. It all
starts with the database design. Details of your design are needed before
any kind of specific response is possible.

I have designed far more complicated databases but it seems the
sometimes illogical insistence of the end users make it more
complicated. If the users could use a spreadsheet that allowed
multiple access (like a database) then they would use the
spreadsheet. I thought this would be quick because of its simplicity.

 

[Jeff Boyce]

I urge you to build the tables/relationships the way that lets Access work
best, then add a button that exports the data from a query to and Excel
spreadsheet. That query will be how/where you "flatten" the data. The
folks who use the data never need to see how it's stored!

(sorry, I get on a rant every now and then...<g>)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

How you wish to display/output data has nothing, I repeat NOTHING!!! to do
with how you get the best use ofAccess' relationally-oriented
features/functions (OK, slight overemphasis).

If the only reason you are "flattening" yourAccesstables is to allow
export of data to Excel, stop now!

First, figure out the entities and relationships you are dealing with.
Then
create tables to handle these.

Now, time to export? Use a query! Queries give you a way to flatten your
relational data for displaying, for reporting, for exporting.

You want to have related records show up? Use a query! Join the tables on
their shared ID field(s).

It doesn't make much sense to learn how to work AROUND the featuresAccess
provides when you can take advantage of them and make your work
(andAccess') a lot simpler.

(perhaps a bit overemphasized, but I think you'll either pay now or pay
later!)

Good luck!

Regards

Jeff Boyce
Microsoft Office/AccessMVP

I hear your emphasis and am not disagreeing. I work with a group of
people that use spreadsheets. They think in spreadsheets, relate any
data in terms of spreadsheets, and pivot tables are their reports. On
the down side, our IT group will not touch Access, regardless of how
simple the design - and forget the fact that a database makes more
sense. If it is not written in .NET they wont touch it.

Since I will not be a permanent fixture in this group, my goal was to
allow them to use Access but be able to use the one table itself.
Perhaps a button that will allow exporting the query to a file would
be a better way to give them a spreadsheet option.